T-SQL Tuesday is a monthly SQL Server blogging event. One of us sets a topic for the month’s event, and we all have a week in which to write a blog post that fits that topic. This month’s event is hosted by Sebastian Meine (blog|@SQLity on Twitter), and he’s inviting us to join in a month of JOINs – see A Join A Day – Introduction for more information.
I’m arriving a bit late to this particular party, but I do recall having written something in the past about Non-ANSI JOINs and SQL 2012 which you ought to know if you don’t already.
However, no repeats are (officially) allowed, so here’s another little nugget.
SARGability – not just for WHERE clauses
You may have come across the concept of a something being SARGable (yes, I know, shouldn’t use Wikipedia as a primary source…), ie that it can be found using an INDEX. A function is considered SARGable if it still allows for use of an index.
Most of the discussion on SARGability focuses on the use in a WHERE clause. However, the same rules apply to JOIN criteria. Last year, for example, I found a query that had a JOIN something like:
... FROM foo INNER JOIN otherDB.dbo.bar ON RTRIM(LTRIM(foo.a)) = RTRIM(LTRIM(bar.a))...
This procedure was causing issues. I knew that the foo.a field was basically a space-padded copy of otherDB.dbo.bar.a, so I re-wrote the JOIN to:
... FROM foo INNER JOIN otherDB.dbo.bar
ON RIGHT(' ' + foo.a, 10) = bar.a...
This caused a massive performance improvement – execution time for this query was reduced by 95%, and we never saw another timeout issue relating to that particular query. It’s still not SARGable, but at least this way the number of functions is reduced, and the data being pulled from OtherDB is also reduced – hence the performance boost.
Summary
Two key points:
- Know your data – at least, know the patterns of your data so you can spot this kind of thing
- SARGability isn’t just for WHERE clauses
Pingback: T-SQL Tuesday #37 - Roundup - sqlity.net | sqlity.net