Rob Farley : lobsterpot, sql

T-SQL in Chicago – the LobsterPot teams with DataEducation

Rob Farley — Mon, 26 Mar 2012 13:18:00 GMT

In May, I’ll be in the US. I have board meetings for PASS at the SQLRally event in Dallas, and then I’m going to be spending a bit of time in Chicago.

The big news is that while I’m in Chicago (May 14-16), I’m going to teach my “Advanced T-SQL Querying and Reporting: Building Effectiveness” course. This is a course that I’ve been teaching since the 2005 days, and have modified over time for 2008 and 2012. It’s very much my most popular course, and I love teaching it. Let me tell you why.

For years, I wrote queries and thought I was good at it. I was a developer. I’d written a lot of C (and other, more fun languages like Prolog and Lisp) at university, and then got into the ‘real world’ and coded in VB, PL/SQL, and so on through to C#, and saw SQL (whichever database system it was) as just a way of getting the data back. I could write a query to return just about whatever data I wanted, and that was good. I was better at it than the people around me, and that helped. (It didn’t help my progression into management, then it just became a frustration, but for the most part, it was good to know that I was good at this particular thing.)

But then I discovered the other side of querying – the execution plan. I started to learn about the translation from what I’d written into the plan, and this impacted my query-writing significantly. I look back at the queries I wrote before I understood this, and shudder. I wrote queries that were correct, but often a long way from effective. I’d done query tuning, but had largely done it without considering the plan, just inferring what indexes would help.

This is not a performance-tuning course. It’s focused on the T-SQL that you read and write. But performance is a significant and recurring theme. Effective T-SQL has to be about performance – it’s the biggest way that a query becomes effective. There are other aspects too though – such as using constructs better. For example – I can write code that modifies data nicely, but if I haven’t learned about the MERGE statement and the way that it can impact things, I’m missing a few tricks.

If you’re going to do this course, a good place to be is the situation I was in a few years before I wrote this course. You’re probably comfortable with writing T-SQL queries. You know how to make a SELECT statement do what you need it to, but feel there has to be a better way. You can write JOINs easily, and understand how to use LEFT JOIN to make sure you don’t filter out rows from the first table, but you’re coding blind.

The first module I cover is on Query Execution. Take a look at the Course Outline at Data Education’s website. The first part of the first module is on the components of a SELECT statement (where I make you think harder about GROUP BY than you probably have before), but then we jump straight into Execution Plans. Some stuff on indexes is in there too, as is simplification and SARGability. Some of this is stuff that you may have heard me present on at conferences, but here you have me for three days straight. I’m sure you can imagine that we revisit these topics throughout the rest of the course as well, and you’d be right. In the second and third modules we look at a bunch of other aspects, including some of the T-SQL constructs that lots of people don’t know, and various other things that can help your T-SQL be, well, more effective.

I’ve had quite a lot of people do this course and be itching to get back to work even on the first day. That’s not a comment about the jokes I tell, but because people want to look at the queries they run.

LobsterPot Solutions is thrilled to be partnering with Data Education to bring this training to Chicago. Visit their website to register for the course.

@rob_farley

Be the surgeon

Rob Farley — Tue, 13 Mar 2012 00:13:00 GMT

It’s a phrase I use often, especially when teaching, and I wish I had realised the concept years earlier. (And of course, fits with this month’s T-SQL Tuesday topic, hosted by Argenis Fernandez)

When I’m sick enough to go to the doctor, I see a GP. I used to typically see the same guy, but he’s moved on now. However, when he has been able to roughly identify the area of the problem, I get referred to a specialist, sometimes a surgeon.

Being a surgeon requires a refined set of skills. It’s why they often don’t like to be called “Doctor”, and prefer the traditional “Mister” (the history is that the doctor used to make the diagnosis, and then hand the patient over to the person who didn’t have a doctorate, but rather was an expert cutter, typically from a background in butchering). But if you ask the surgeon about the pain you have in your leg sometimes, you’ll get told to ask your GP. It’s not that your surgeon isn’t interested – they just don’t know the answer.

IT is the same now.

That wasn’t something that I really understood when I got out of university. I knew there was a lot to know about IT – I’d just done an honours degree in it. But I also knew that I’d done well in just about all my subjects, and felt like I had a handle on everything. I got into developing, and still felt that having a good level of understanding about every aspect of IT was a good thing.

This got me through for the first six or seven years of my career.

But then I started to realise that I couldn’t compete.

I’d moved into management, and was spending my days running projects, rather than writing code. The kids were getting older. I’d had a bad back injury (ask anyone with chronic pain how it affects your ability to concentrate, retain information, etc). But most of all, IT was getting larger.

I knew kids without lives who knew more than I did. And I felt like I could easily identify people who were better than me in whatever area I could think of. Except writing queries (this was before I discovered technical communities, and people like Paul White and Dave Ballantyne). And so I figured I’d specialise.

I wish I’d done it years earlier.

Now, I can tell you plenty of people who are better than me at any area you can pick. But there are also more people who might consider listing me in some of their lists too. If I’d stayed the GP, I’d be stuck in management, and finding that there were better managers than me too.

If you’re reading this, SQL could well be your thing. But it might not be either. Your thing might not even be in IT. Find out, and then see if you can be a world-beater at it.

But it gets even better, because you can find other people to complement the things that you’re not so good at.

My company, LobsterPot Solutions, has six people in it at the moment. I’ve hand-picked those six people, along with the one who quit. The great thing about it is that I’ve been able to pick people who don’t necessarily specialise in the same way as me. I don’t write their T-SQL for them – generally they’re good enough at that themselves. But I’m on-hand if needed. Consider Roger Noble, for example. He’s doing stuff in HTML5 and jQuery that I could never dream of doing to create an amazing HTML5 version of PivotViewer. Or Ashley Sewell, a guy who does project management far better than I do. I could go on. My team is brilliant, and I love them to bits. We’re all surgeons, and when we work together, I like to think we’re pretty good!

@rob_farley

LobsterPot submissions for SQLPASS

Rob Farley — Fri, 06 May 2011 04:05:32 GMT

My guys are great! When PASS started accepting abstract submissions for their Summit (in October this year), some of the LobsterPot employees immediately started looking into ideas for talks they could do. We rate communication as one of our key values at LobsterPot, and all my staff are keen presenters.

Roger Noble was at the PASS Summit with me last year, and has since spoken at both the Adelaide SQL Server User Group and Adelaide SharePoint User Group. Considering the work he’s done in data visualisation with PivotViewer over the past year, he was keen to be able to submit a session on that. This technology is seriously cool stuff – quite a few of our clients have been very interested in it and are now using PivotViewer to get at their data in new ways. You can see examples of Roger’s work at http://pivot.lobsterpot.com.au

Get even more from PivotViewer (Roger Noble)

With the release of the Silverlight PivotViewer control from Microsoft in June 2010 we saw the beginning of a new direction for data visualisation and interactivity, allowing data to be browsed and filtered in ways that highlighted information that could have easily been missed. This session will show you how to take the PivotViewer control and enhance it even further to provide even more ways to display your data, including placing information on maps, and showing extra information in the PivotViewer tiles according to the zoom level. From sourcing data from PowerPivot and SharePoint 2010, using Visual Studio 2010 to add new functionality and improvements in future versions this session will show the range of ways that PivotViewer can effectively be used in your organisation.

Like most of the team, Ashley Sewell has been doing a bunch of work with clients implementing cubes and reports. The talk he’s put in reflects a very common emotion that he gets from clients when they first start talking about Business Intelligence. They want to know that they’re not just getting their data in a different format, but that they’re going to be able to reach into the data themselves and realise that ‘Analysis’ aspect of SSAS. Ashley used to be tertiary lecturer, and understands the importance of giving presentations that are useful as well as at an appropriate technical level. This talk will be excellent, and I really hope it gets picked.

So you've got a Cube. What's Next? (Ashley Sewell)

Did you ever get to the end of an Analysis Services session thinking "Cubes sound great but what can I show the analysts and execs back at work to woo them?". If your answer is yes then this session is for you. You will be taken through some of the Business Intelligence reporting and dashboarding available using a combination of PerformancePoint Services 2010 and Reporting Services 2008 R2 with particular emphasis on combining the best of each offering to maximise the impact of your dashboards. You can expect to leave this session with a deep enough understanding (and a list of gotchas) to enable you to create your own dashboards and data visualizations that bring the data within your cube to life on the web.

As expected, I’ve put a few submissions – a pre-conference seminar and two regular sessions.

The pre-con is an enhanced version of the one I did at SQLBits 7. In that, I go through a bunch of T-SQL queries that could have been fixed using T-SQL that most people aren’t aware of. For example – many people would shy away from something like ORDER BY MAX(OrderDate) DESC, but if you understand what’s going on there, when it’s good and when it’s bad, then it can be just fine. This pre-con got excellent feedback at SQLBits, and I think it will please PASS Summit delegates as well.

Fixing Queries With Advanced T-SQL Constructs (Rob Farley)

Have you inherited queries that are not your own, and are finding that performance isn’t so great? Removing cursors in favour of set-based queries is useful, but even set-based queries can perform poorly. Understanding the impact that various constructs can have on a query plan could be key to resolving many of these issues. In this seminar, irrepressible SQL MVP Rob Farley will take a look at some real-life queries and take the audience through examples of constructs that can have significant effects on tuning. This will include complex nested joins, join simplification, procedural functions, SARGability v residuality with predicates, better execution plan reading, start-up parameters, force hints, complex sorting, ORs, effective Dynamic SQL, GROUP BY v DISTINCT, unique indexes, temporary tables, APPLY considerations, and more. You'll discover profoundness in things you thought you knew, and you'll even see when a covering index that returns a single row can be a bad thing. This will be a day spent in Management Studio, not PowerPoint. If you want to know how to persuade the Query Optimizer to do a better job of running your query, this day is for you. The examples will apply to a variety of versions, with most of it being useful even in a SQL 2005 environment.

Another talk that I’ve done in the past is one called “Understanding SARGability (to make your queries run faster)”. In fact, last year this talk was a ‘stand-by’ for the PASS Summit. I also gave it at SQLBits 7, with Brent Ozar (@BrentO) and Buck Woody (@BuckWoody) heckling me from the back. Brent tweeted “Okay, wow, @robfarley is a seriously good presenter”, and although he got my Twitter handle wrong (it’s @rob_farley), I was very flattered. It’s one of my second-favourite tweets still.

Over recent times, I’ve found that people really don’t seem to understand the significance of having predicates fall into the category of “Residual”. I wrote about it recently, and was quite interested to see some of the reactions that people had when they talked about it with me. Jack Li wrote a post last week about a query which took longer than expected because a Hash Match was putting a lot of data into a single bucket. This is a common problem that gets missed, because of the impact of having the selective predicate treated as residual. I’m going to write more posts on that in the coming weeks, and a lot of that will be covered in one of the talks I’ve submitted this year. Residualiciousness isn’t a real word, but I figure that shouldn’t stop me.

Joins, SARGability and the Evils of Residualiciousness (Rob Farley)

You wouldn't believe how often people just aren't using their indexes effectively, whether it be searching for data, or joining tables. Quite often, this comes down to predicates becoming residual. Yes, residuality is a problem, and once a predicate has become residualicious, you may as well be scanning instead of seeking. There’s so much more to SARGability than people think, and people can often miss out on significant performance benefits by not appreciating this – particularly with new and improved query hints becoming available in SQL 2008 R2 SP1 and Denali. SARGable means Search ARGument Able and relates to the ability to search through an index for a value. Unfortunately many database professionals don’t really understand it – especially in regard to joins – leading to queries which don’t run as well as they should. In this talk, you'll learn how to tell whether a predicate is being used correctly, and to evaluate what's really going on in your Seek or Join. You'll even learn to use new features in SQL 2008 R2 SP1 and Denali to affect the residuality of your predicates. This is a talk involving lots of demos, showing plenty of queries and execution plans.

The other talk I’ve submitted was inspired by a conversation with my friend Jamie Thomson (@jamiet), who unfortunately won’t be at the PASS Summit this year (he does have an excellent reason though). I happened to mention something which I considered an important consideration about queries used for SSIS, and he told me I had to write a blog post about it. I eventually did, and it got me thinking about a bunch of things that SSIS Tuning Talks (like those that Jamie gives) which are very relevant to tuning T-SQL, but yet almost never get mentioned by standard talks. Some of them get covered in the pre-con seminar too, and I’m sure I’ll have to get blog posts written on some of these things over coming months.

Tuning T-SQL Using Lessons Learned From SSIS (Rob Farley)

We see presentations telling us how to tune T-SQL, looking at things like how a covering index can help avoid an expensive lookup, and the importance of set-based thinking. But there can be a lot more to finding bottlenecks in an execution plan, and there are significant parallels with the kind of concerns we have when tuning SSIS Data Flows. This session will look at some of the things that SSIS gurus explain when in looking at how to make SSIS run faster, and draw strong parallels to things that many query tuners don't realise. If only they paid attention to the SSIS world! There will be a lot of examples in this session, explaining what's happening in query plans and the ways that you can persuade your queries to run more like your SSIS packages, and vice-versa.

Looking at the long list of abstract submissions this year (and right now there’s about three hours for more to come in), I think it’s going to be a great event. With buzz around Denali and a stronger community than ever, I think it’s going to be huge.

There will be handful of LobsterPot employees there, and I hope you will be too!

@rob_farley

A SQL story in 11 words or less

Rob Farley — Tue, 05 Apr 2011 01:29:41 GMT

Success Quietly Looms.

Not quite 11 words, I know. I’ll put more at the end. Definitely SQL related though, right? (And it’s even an acrostic!) Tom LaRock started this, and Stacia Misner tagged me. Monday is over now though (heck, it’s well into Tuesday here), so I’m not going to tag anyone else.

I’m not talking about our success (even though I’m in the process of hiring my sixth employee), I’m talking about the impact of having a system that you’ve implemented well, and finding that people actually want to use it. It’s the hope of every project. You don’t want to implement something only to find that people use the old system.

And yet when you implement a system which becomes successful, there are unexpected problems.

Look at Twitter. A few guys sit around and think how useful it would be to send SMS messages to a central location where everyone can read it. It becomes a worldwide phenomenon, and has very serious (and very public) problems scaling. Brilliant idea, but success quietly loomed.

Scalability needs to be a forethought, not a reaction. If you do your job well, you will have underestimated the required workload. It’s a fact of life. Luckily, SQL easily lets you plan things nicely. Systems can be tuned when small, allowing for growth that doesn’t cripple.

The principle also applies on a personal level. If you do well at things, you’ll be given more, and need to work out how to scale. Right now my business is growing, and that’s a good thing. I’ve had to hire extra people to allow for the growth, and I’m trying to find time to get into CRM Online and various other systems. I’m needing to adjust to the challenges of having a company of six (nearly seven) people, having to lean on God more than ever, and (like Aaron) making sure I prioritise the things that are important. I’m not leaving the community – I see that as an important part of running my business – but I do need to make sure that my family doesn’t suffer too much simply because there is more that needs doing than ever before.

As for the other eight words in my short story (and acrostic):

Did Anybody Think About Backups And Saving Everything?

Visualising data a different way with Pivot collections

Rob Farley — Wed, 29 Dec 2010 02:50:00 GMT

Roger’s been doing a great job extending PivotViewer recently, and you can find the list of LobsterPot pivots at http://pivot.lobsterpot.com.au

Many months back, the TED Talk that Gary Flake did about Pivot caught my imagination, and I did some research into it. At the time, most of what we did with Pivot was geared towards what we could do for clients, including making Pivot collections based on students at a school, and using it to browse PDF invoices by their various properties. We had actual commercial work based on Pivot collections back then, and it was all kinds of fun. Later, we made some collections for events that were happening, and even got featured in the TechEd Australia keynote.

But I’m getting ahead of myself... let me explain the concept.

A Pivot collection is an XML file (with .cxml extension) which lists Items, each linking to an image that’s stored in a Deep Zoom format (this means that it contains tiles like Bing Maps, so that the browser can request only the ones of interest according to the zoom level). This collection can be shown in a Silverlight application that uses the PivotViewer control, or in the Pivot Browser that’s available from getpivot.com. Filtering and sorting the items according to their facets (attributes, such as size, age, category, etc), the PivotViewer rearranges the way that these are shown in a very dynamic way. To quote Gary Flake, this lets us “see patterns which are otherwise hidden”.

This browsing mechanism is very suited to a number of different methods, because it’s just that – browsing. It’s not searching, it’s more akin to window-shopping than doing an internet search.

When we decided to put something together for the conferences such as TechEd Australia 2010 and the PASS Summit 2010, we did some screen-scraping to provide a different view of data that was already available online. Nick Hodge and Michael Kordahi from Microsoft liked the idea a lot, and after a bit of tweaking, we produced one that Michael used in the TechEd Australia keynote to show the variety of talks on offer.

It’s interesting to see a pattern in this data: The Office track has the most sessions, but if the Interactive Sessions and Instructor-Led Labs are removed, it drops down to only the sixth most popular track, with Cloud Computing taking over.

This is something which just isn’t obvious when you look an ordinary search tool. You get a much better feel for the data when moving around it like this.

The more observant amongst you will have noticed some difference in the collection that Michael is demonstrating in the picture above with the screenshots I’ve shown. That’s because it’s been extended some more.

At the SQLBits conference in the UK this year, I had some interesting discussions with the guys from Xpert360, particularly Phil Carter, who I’d met in 2009 at an earlier SQLBits conference. They had got around to producing a Pivot collection based on the SQLBits data, which we had been planning to do but ran out of time. We discussed some of ways that Pivot could be used, including the ways that my old friend Howard Dierking had extended it for the MSDN Magazine. I’m not suggesting I influenced Xpert360 at all, but they certainly inspired us with some of their posts on the matter

So with LobsterPot guys David Gardiner and Roger Noble both having dabbled in Pivot collections (and Dave doing some for clients), I set Roger to work on extending it some more. He’s used various events and so on to be able to make an environment that allows us to do quick deployment of new collections, as well as showing the data in a grid view which behaves as if it were simply a third view of the data (the other two being the array of images and the ‘histogram’ view).

I see PivotViewer as being a significant step in data visualisation – so much so that I feature it when I deliver talks on Spatial Data Visualisation methods. Any time when there is information that can be conveyed through an image, you have to ask yourself how best to show that image, and whether that image is the focal point. For Spatial data, the image is most often a map, and the map becomes the central mode for navigation. I show Pivot with postcode areas, since I can browse the postcodes based on their data, and many of the images are recognisable (to locals of South Australia).

Naturally, the images could link through to the map itself, and so on, but generally people think of Spatial data in terms of navigating a map, which doesn’t always gel with the information you’re trying to extract. Roger’s even looking into ways to hook PivotViewer into the Bing Maps API, in a similar way to the Deep Earth project, displaying different levels of map detail according to how ‘zoomed in’ the images are.

Some of the work that Dave did with one of the schools was generating the Deep Zoom tiles “on the fly”, based on images stored in a database, and Roger has produced a collection which uses images from flickr, that lets you move from one search term to another. Pulling the images down from flickr.com isn’t particularly ideal from a performance aspect, and flickr doesn’t store images in a small-enough format to really lend itself to this use, but you might agree that it’s an interesting concept which compares nicely to using Maps.

I’m looking forward to future versions of the PivotViewer control, and hope they provide many more events that can be used, and even more hooks into it.

Naturally, LobsterPot could help provide your business with a PivotViewer experience, but you can probably do a lot of it yourself too. There’s a thorough guide at getpivot.com, which is how we got into it. For some examples of what we’ve done, have a look at http://pivot.lobsterpot.com.au. I’d like to see PivotViewer really catch on a data visualisation tool.

Why bother with database professionals?

Rob Farley — Tue, 02 Nov 2010 05:49:32 GMT

Considering that I run a database consultancy , I should be fairly biased on this question, and suggest that you can't possibly cope without database professionals – except that I don’t really believe it all that much. I do believe it, and I’ll demonstrate...(read more)

The first Gold Competency in Australia!

Rob Farley — Sun, 31 Oct 2010 23:03:54 GMT

At LobsterPot , there are a few things that we definitely value above other things. Integrity is top of the list – it has to be. In fact, it is the quality I refuse to compromise on with my staff. When I identify someone I want to approach for a job,...(read more)