Fast SQL query with * but extremely slow with specific columns?

Question

We have a query that we're running on SQL Server 2008 in the Management Studio. I can't give the exact query details, but let's say it's equivalent to this:

SELECT * 
FROM   MyView a (nolock) INNER JOIN MyTable b (nolock) 
           ON a.id = b.id AND a.thedate > b.thedate
WHERE  a.period_start < '1/1/2012' 
AND    a.invoice_status = 5 
AND    b.memo like '%something' 
AND    b.memo LIKE '%something else%'

This query runs relatively fast, returning 100 records in less than a second.

I then change the * in the select to explicitly list a few specific columns I want to see from each joined table. (Nothing fancy, just regular columns.) For example:

SELECT a.column1, a.column2, b.column1, b.column2 
FROM   MyView a (nolock) INNER JOIN MyTable b (nolock) 
           ON a.id = b.id AND a.thedate > b.thedate
WHERE  a.period_start < '1/1/2012' 
AND    a.invoice_status = 5 
AND    b.memo like '%something' 
AND    b.memo LIKE '%something else%'

Suddenly the query is taking many, many minutes to execute.

Lastly, I run the same query, but I add the * back in, to the last position of the select column list, and the query is back to the same fast speeds! What gives? I'm looking at the estimated execution plans, and I see the faster version mentions a "Hash Match" early on in the query that I don't see in the slower version.

SELECT a.column1, a.column2, b.column1, b.column2, * 
FROM   MyView a (nolock) INNER JOIN MyTable b (nolock) 
          ON a.id = b.id AND a.thedate > b.thedate
WHERE  a.period_start < '1/1/2012' 
AND    a.invoice_status = 5 
AND    b.memo like '%something' 
AND    b.memo LIKE '%something else%'

Is there a way to address this, or do we have to keep the asterisk in place?

Answer 1

You could try forcing the hash match, give this a go? :

SELECT a.column1, a.column2, b.column1, b.column2  
FROM   MyView a (nolock) 
INNER HASH JOIN MyTable b (nolock) ON a.id = b.id AND a.thedate > b.thedate 
WHERE  a.period_start < '1/1/2012'  
AND    a.invoice_status = 5  
AND    b.memo like '%something'  
AND    b.memo LIKE '%something else%' 

Answer 2

As to the question "Why" the answer will be that selecting all the columns prompts SQL Server to use a different query plan.

For example, if you select columns which do not have an index, SQL Server knows it is eventually going to have to read the table pages, so might conclude that doing a table scan would be faster, or might conclude that using a different index, then using a clustered index seek would be faster.

However if you only select columns which have an index, SQL Server will probably think it is faster scan the index and read the data from there.

Now, what with the query optimiser being too clever by half, it often gets this wrong. In your case I would guess that the table scan and/or clustered index seek with the hash join is faster but SQL thinks it is slower, and only does it because you are asking for all the columns.

The solution is either to force the plan, or possibly better, to create indexes on the date fields.

Also, you can sometimes improve the plan by forcing short-circuit order of the where clause. In your case:

/* Example how to turn an AND AND AND into a CASE. Note the NOT. */
case
when NOT a.period_start < '1/1/2012'   then 0
when NOT a.invoice_status = 5   then 0
when NOT b.memo like '%something'   then 0
when NOT b.memo LIKE '%something else%' then 0
else 1 end = 1

That would have the same logical effect as your where clause, but prevents SQL from using the period_start or invoice_status indexes, and also forces those to be evaluated before the memo condition.

/* Example how to turn an OR OR OR into a CASE */
case
when a.period_start < '1/1/2012'   then 1
when a.invoice_status = 5   then 1
when b.memo like '%something'   then 1
when b.memo LIKE '%something else%' then 1
else 0 end = 1