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:
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
JOIN
s 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...