Click here to Skip to main content
15,867,594 members
Please Sign up or sign in to vote.
4.50/5 (2 votes)
See more:
Hi all,
I'm having a little trouble with a view someone wrote in SQL Server 2005.
The query is used for reporting (Crystal Reports, yikes!) and is called pretty often (probably a few 100 times a day). The problem is the data takes 4 to 6 seconds to load.

The query is pretty straightforward:
SQL
SELECT 'SomeField' = dbo.MyTable.Field1,
       'SomeOtherField' = dbo.MyTable.Field2
       'A_Third_Field' = dbo.MyOtherTable.Field1,
       ...
FROM dbo.MyTable
LEFT JOIN dbo.MyOtherTable ON dbo.MyOtherTable.Field1 = dbo.MyTable.OtherTableField
...
WHERE Something

There's 12 JOINs and three WHERE conditions. There are some functions in the SELECT list, like ISNULL and a CASE.
All that doesn't matter though.

What matters is that when I run the query it takes SQL Server 4 to 6 seconds to give me a result.
Here comes the strange part; when I replace SELECT Field1 etc. with SELECT * the query returns results instantly!

We can't use SELECT * in our view because it returns columns with the same name. And, of course, we don't want to use SELECT *.
How is it that SELECT * is so much faster though?
And how can I make my 'regular' query just as fast (and why isn't it already? :-s)?

UPDATE:
Just now the original query ran instantly while the SELECT * doesn't run at all... The original query is now back to it's 6 seconds...

I understand SQL Server can sometimes take a little longer, but this isn't sometimes.

Basically this weird behaviour makes it pretty impossible for me to tune this query.

Any idea's?
Thanks.

ANOTHER UPDATE:
I just found out the SELECT * statement makes about 88000 LESS reads on one of the biggest tables in our database. Why would it do that?

YET ANOTHER UPDATE:
It seems when I put a non-persisted computed column in my select list everything is blazing fast... But only when I run the query as ad-hoc query. When I add the same field to the view nothing happens...
Posted
Updated 27-Mar-14 4:26am
v3
Comments
Kornfeld Eliyahu Peter 27-Mar-14 7:09am    
Did you tried to see what in the execution plan of that view, and to compare to the execution plan of the 'SELECT *'?
Sander Rossel 27-Mar-14 7:28am    
I've checked the estimated execution plan for the SELECT *, but there's nothing wrong with it. Lots of index seeks (afaik it doesn't get faster than that!), nested loops, merge joins, a few key lookups, a few sorts... More or less the same as the regular query.
The problem is that just now SELECT * ran perfect again!
I just rewrote the query, but starting with a different (more logical) FROM table. Guess what? SELECT * doesn't work at all and SELECT some field is just pretty slow again. The query plan shows the same as above with 66% of the cost in an index seek...

I really don't know what is going on anymore!
Kornfeld Eliyahu Peter 27-Mar-14 7:32am    
That 66% of index seek is something should alert your DBA!!! It seems one of your table need some re-indexing...
Anyway - I do know nothing about your joins, but 12 of them sound a lot...
Try this: start with simple select, than add joins one by one. You may be lucky and catch the problem...
Sander Rossel 27-Mar-14 7:42am    
We all do a bit of DBA'ing here and today I'm the DBA of choice ;-)
I've checked the index and there seems nothing wrong with it.
Kornfeld Eliyahu Peter 27-Mar-14 7:10am    
Can you check (with profiler) if SQL re-compiles your view on every access?

See more details in blog
select-vs-select-columns-sql-server.html[^]
 
Share this answer
 
Comments
Sander Rossel 27-Mar-14 8:14am    
Yes, pretty nice read. But it doesn't explain why SELECT *, in my case, sometimes is lightning fast (6 seconds faster than just some columns!) and sometimes just 'chokes'...
I'd never use SELECT * in an production environment.
[no name] 27-Mar-14 8:26am    
SQL Server by default copy the complete table to buffer pool irrespective of columns selection – So no matter whether you use * or few columns.
Not really a solution, but it did fix my problem.
The problem was in the JOINS. Making some JOINS INNER JOINS made the query lightning fast. Though it would be correct to make all of them INNER JOINS this once again makes the query slow... Having all LEFT JOINS (as it was) was, in some way, correct, but not necessary.
So I guess that's case closed.

Edit:
I have no idea how the computed column made everything fast in an ad-hoc query, but not in a view... I've given up on finding that out.
 
Share this answer
 
v3

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900