SQL Server Performance

ORDER BY slows down the query dramatically

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by smcgrath12, Apr 16, 2009.

  1. smcgrath12 New Member

    I have a complex query that return 5 rows. The complex query is a join of 3 tables and a table valued function. When I run the query without any order by, the query return in 2 seconds. When I run the query with a order by which references one of the columns from the table valued function, the select slows down to almost 3 minutes. If I run the query with a order by (but on any other column except those from the table valued function), the query runs fast as well. It seems liike the query bogs down when I do a order by on any column from the table valued function. Is there a bug in the sql server regards to this or am I missing something here? I tried putting the results of the join in a temp table and running a order by on the temp table (order by on a column from the table valued function) and that runs very fast. Any ideas?
  2. cs_raman New Member

    what are the dml statements in table valued function ? perhaps a select from this function and an order by on this alone can be tried to see if this an issue
  3. Adriaan New Member

    ORDER BY indeed slows down a query.
    Why a table valued function? Do you have the option to insert the same rows into a local table variable, then include that in your query instead of the UDF?
  4. smcgrath12 New Member

    I can get around the problem by using temp table, table variable or "with" clause. I am at a loss to explain why a 5 row resultset will take such a long time to sort. I even tried something like this:
    SELECT TT.*
    FROM
    (
    -----
    ----
    ) TT
    ORDER BY TT.x, TT.y
    Here, x and y are the columns from UDT. The total rows returned by the UDT are only around 200.
  5. Adriaan New Member

    The 200 rows from the UDT are critical, especially since they are probably not indexed. No indexes, no joy.

Share This Page