36 Chambers – The Legendary Journeys: Execution to the max!

March 1, 2014

T-SQL Anti-Patterns: Non-SARGable ORs In Joins

Filed under: Database Administration — Kevin Feasel @ 6:00 pm

Our last anti-pattern had us discussing unnecessary repetition of queries, but this time, I’m going to hit the opposite problem:  trying to merge two queries which should remain separate.

Let’s start with an example of a query.  This is a simplified version of a query which I ran into at a prior job:

SELECT
	ACCT.[Id].
	ACCT.[TypeId].
	ACCT.[EntityId].
	ACCT.[Name].
	ACCT.[Purpose],
	TF.Amount As TransferAmount
FROM
	dbo.Account ACCT
	INNER JOIN dbo.Transfer TF
		ON ACCT.Id = TF.AccountToId
		OR ACCT.Id = TF.AccountFromId;

There’s nothing objectionable in this query, per se…at least until you try to run it. The problem here is that the join from Account to Transfer happens on one of two keys: ID to AccountToID, or ID to AccountFromID. If we have indexes on neither AccountToID nor AccountFromID, we’re going to scan the Transfer table once for each account. So, our DBA looks at the scan and says, “Hey, let’s put an index on this.” Unfortunately, the indexes do nothing:

  • If you put an index on AccountToID, we still need to scan to get the rows with a matching AccountFromID.
  • If you put an index on AccountFromID, we still need to scan to get rows with a matching AccountToID.
  • If you put an index on both AccountToID and AccountFromID, that still doesn’t help:  rows matching on AccountToID typically won’t be the same as those matching AccountFromID (you don’t transfer money from one account to the same account, after all).

But let’s say you put separate indexes on AccountToID and AccountFromID.  In that case, one of the indexes might be useful, but SQL Server won’t be able to use both of them effectively in a single query.

The performance-improving correction I made was as follows:

SELECT
	ACCT.[Id].
	ACCT.[TypeId].
	ACCT.[EntityId].
	ACCT.[Name].
	ACCT.[Purpose],
	TF.Amount As TransferAmount
FROM
	dbo.Account ACCT
	INNER JOIN dbo.Transfer TF
		ON ACCT.Id = TF.AccountFromId

UNION ALL

SELECT
	ACCT.[Id].
	ACCT.[TypeId].
	ACCT.[EntityId].
	ACCT.[Name].
	ACCT.[Purpose],
	TF.Amount As TransferAmount
FROM
	dbo.Account ACCT
	INNER JOIN dbo.Transfer TF
		ON ACCT.Id = TF.AccountToId;

In our case, we had a check constraint making sure that AccountToID and AccountFromID were not the same on a single transfer, so I could use a UNION ALL. By separating this out into two queries, we can now take full advantage of the two separate indexes. This leads to two separate accesses of the Account table rather than one, but cuts down on I/O tremendously, especially as Account and Transfer get large.

So when is it OK to use OR in a join? In the work that I’ve done, I’ve seen three major cases:

  1. TableX x INNER JOIN TableY y ON (x.ColumnA = y.ColumnA OR x.ColumnB = y.ColumnB) — Two separate columns with two separate joins are OK.
  2. TableX x INNER JOIN TableY y ON (x.ColumnA = y.ColumnA OR x.ColumnA = 6) — Join on a match or when one table matches a constant.  That second part could be ColumnB as well.
  3. TableX x INNER JOIN TableY y ON (x.ColumnA = y.ColumnA OR z.ColumnA = y.ColumnA) — Join on another table match, as long as z was brought in on a join earlier in the query.  Watch out for accidental Cartesian products here.

In contrast, I would simplify our scenario as:  TableX x INNER JOIN TableY y ON (x.ColumnA = y.ColumnA OR x.ColumnB = y.ColumnA).  Whenever we have two separate columns matching to one column in the other table, we will want to look at an alternative form of joining these tables together.  Otherwise, we run the risk of terrible performance.

About these ads

1 Comment »

  1. […] anti-pattern, non-SARGable ORs in joins, was all about writing code which the SQL Server database engine can optimize.  Today’s […]

    Pingback by T-SQL Anti-Patterns: Linked Server Joins | 36 Chambers - The Legendary Journeys: Execution to the max! — March 2, 2014 @ 6:01 pm


RSS feed for comments on this post. TrackBack URI

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

The Silver is the New Black Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 94 other followers

%d bloggers like this: