• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 493
  • Last Modified:

ORDER BY slows down the query dramatically

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?
0
dencom
Asked:
dencom
  • 6
  • 3
  • 3
  • +1
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
use a temp table or table variable to store the result of the query, and then return the data from that table with order by
set nocount on
declare @t TABLE ( col1 int, col2 varchar(10) ) --- define the columns as you need them 
insert into @t (col1, col2)
 select field1, field2 ---- here comes your current query, without order by
   from ...
  where ... 
  
select * from @t 
 order by col1  -- put the order by as you need it

Open in new window

0
 
SNilssonCommented:
The easy solution since its only return 5 rows might be a subquery


Select SubQ.* From (Your query) SubQ
Order By SubQ.Column

Open in new window

0
 
MikeTooleCommented:
I would guess that the Order By on the function column changes the way the query is optimised.
You can avoid that by nesting you original select inside another that handles only the Order By:

Select F1, F2, F3
From (Select F1, F2, F3  ...)
Order by F3
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
dencomAuthor Commented:
I already tried what angellll suggested. That works fine. Instead of using a table variable, I use a temp one. But both work fine.

The problem is the solution offered by SNilsson. I have tried that and it does not work. I don't know why.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>. I don't know why.
because sql server tries to be smarter than it should :)


with sql 2005+, you can try this alternative
with data as (Select F1, F2, F3  ...)
select *
 from data
Order by F3

Open in new window

0
 
SNilssonCommented:
Right SQL Server should really learn to execute the innermost code first, sorry if it did not increase performance.
Maybe you can check the execution plan of the query and see what stupied thing it tries to do when order by is used.
0
 
dencomAuthor Commented:
Angellll, I know that it will work too. Temp tables, Table variable, 'With" queries, are all logically same (as far as I understand). I would like to know why the original query bogs down, though. I know how to work around the problem, but I am not sure why the problem is manifesting itself. Will posting my execution plan help?
0
 
dencomAuthor Commented:
Please remove the ".txt" from the file. Thanks.
ExecPlan.sqlplan.txt
0
 
MikeTooleCommented:
>> angelIII
nice one, thanks :-)
Mike
0
 
dencomAuthor Commented:
Plan without the sort.
ExecPlan-WithoutOrderBy.sqlplan.txt
0
 
dencomAuthor Commented:
Anybody see anything in the query plans? The "Sort" operator only has 0% cost, so I cannot explain why the query slows down so much.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>The "Sort" operator only has 0% cost,
if the rest of the explain plan shows a different behavior, ie tries to return the rows according to the order by even before the order by (for example, by using a clustered index on that field instead of another index for the where clause), this can explain both.
I could not yet look at the explain plans yet, so cannot comment on the effective plans...
0
 
SNilssonCommented:
Have a look here, there are suggestions made to the same problem you have, like putting a non-clustered index on the table valued function.
http://www.eggheadcafe.com/software/aspnet/33177127/query-is-slow-because-of.aspx
0
 
dencomAuthor Commented:
Thanks guys. I might just ring up Microsoft, I have a bunch of MSDN calls available.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 6
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now