Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share

migration rejected from stackoverflow.com Aug 7 '13 at 10:30

This question came from our site for professional and enthusiast programmers. Votes, comments, and answers are locked due to the question being closed here, but it may be eligible for editing and reopening on the site where it originated.

closed as unclear what you're asking by Mark Storey-Smith, Jon Seigel, swasheck, StanleyJohns, dezso Aug 7 '13 at 10:30

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

    
Do you have SET EXPLAIN or some other way to get the query plans of the two queries. What's the difference between them? –  Jonathan Leffler May 2 '12 at 0:05
    
@Jonathan, I asked the same question, then deleted it when I saw the text just above the last query. :-) –  Aaron Bertrand May 2 '12 at 0:17
1  
@Mason, can you share more details, e.g. about the indexing on MyTable, how many rows from each side, how many rows returned by the query, what happens if you join against the necessary underlying table(s) instead of the view, the indexes on the table(s) referenced by the view, whether you really are okay with NOLOCK, and whether you have tried explicitly specifying a loop join (which I assume is the type of join used in the faster version of the query)? –  Aaron Bertrand May 2 '12 at 0:20
1  
@JonathanLeffler Oh I definitely agree it would be useful for us to see the actual query plans. But I asked the same question as you, which was what is the difference? Was just pointing out that he did explain what difference he sees, and I missed it the first time I read the question too. :-) –  Aaron Bertrand May 2 '12 at 0:23
1  
This is the twilight zone of slow query problems. I hope this gets solved and share the cause of the problem –  Michael Buen May 2 '12 at 11:14