T-SQL Tuesday #037 – SARGability – Not Just For WHERE Clauses

LogoT-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
About these ads
This entry was posted in SQLServerPedia Syndication, T-SQL Tuesday and tagged , , . Bookmark the permalink.

2 Responses to T-SQL Tuesday #037 – SARGability – Not Just For WHERE Clauses

  1. Pingback: T-SQL Tuesday #37 - Roundup - sqlity.net | sqlity.net

  2. Pingback: T-SQL Tuesday #37 - Roundup - sqlity.staging.wpengine.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s