Post reply

ORDER BY affecting query against my view

  • July 13, 2011 at 11:34 am

    #389965

    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

    #1352330

    Have you look at the query execution plan to see what is killing it?

    :cool:

    "There are no problems! Only solutions that have yet to be discovered!"

  • July 13, 2011 at 12:00 pm

    #1352340

    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

    #1352354

    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.

    :cool:

    "There are no problems! Only solutions that have yet to be discovered!"

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply