July 13, 2011 at 11:34 am
I have a simple report query that goes directly against a view. The underlying tables in the view contain a large number of records but using appropriate filters the report query returns a small number of records within 10-15 seconds. However, when I add an ORDER BY to the report query it takes about 2 hours to run.
For example:
"select field1, field2, field3 from v_RptView"; returns ~6 rows in 10-15 seconds.
"select field1, field2, field3 from v_RptView order by field3"; returns same 6 rows but takes two hours to complete and a lot of CPU.
If ORDER BY is last operation to be processed why does it look like SQL is trying to sort the underlying tables first? I can't figure out what's going on and would appreciate any suggestions.
Thanks
July 13, 2011 at 11:43 am
Have you look at the query execution plan to see what is killing it?
July 13, 2011 at 12:00 pm
I'm reporting the response times from the report server log; never actually had the patience to get actual plan yet. Estimated plan is same for both with or without ORDER BY.
July 13, 2011 at 12:20 pm
The reason I ask is their is the possibility there is no index for the order by thus causing it to scan the whole table.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply