March 16, 2010 at 2:16 pm
I have a LINQ generated TSQL statement. LINQ is very fond of generating derived tables E.g. SELECT * FROM (SELECT * FROM Orders WHERE state = 'CA') as x.
My query has derived tables nested 4 levels deep. It's not a poorly performing query except that I have an ORDER BY on the outermost select. This causes a 'sort' task to appear in the execution plan.
The table where the column that is being ordered only has 9 records in it so there's no use in indexing that. It's being joined in the query to a table with a million to create a derived table and this is what is being ordered before returning the top 5.
I don't think it's possible to index the derived table in memory. How do I get rid of this 'sort'?
Thanks,
dnash
March 16, 2010 at 2:30 pm
Is the sort a problem?
Is the specific query that you're looking at a problem?
You said it's not a poorly performing query, so why are you worried about it?
http://sqlinthewild.co.za/index.php/2010/03/11/the-root-of-all-evil/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 17, 2010 at 5:06 am
if possible, required ORDER can be set in physical table and then avoid "order by" clause in derived query
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 17, 2010 at 5:59 am
Bhuvnesh (3/17/2010)
if possible, required ORDER can be set in physical table and then avoid "order by" clause in derived query
It is not good advice to suggest that one should rely on anything except an explicit ORDER BY for ordering.
As Gail said, there's no real point in avoiding the sort anyway.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 17, 2010 at 6:20 am
What I meant to say when I said it's not a poorly performing query is that the sql generated is very efficient. The query itself still takes too long to run. It's about 10 seconds and responds to a navigation menu click. That's too slow. Ninety nine percent of the work is all on one 'sort' task.
I think I'm answering my own question in that there is no way to return a swatch of records ordered by a column without first sorting the whole mess. I don't think it can be fixed in the sql. Might require a schema change.
March 17, 2010 at 6:36 am
David Nash-367764 (3/17/2010)
What I meant to say when I said it's not a poorly performing query is that the sql generated is very efficient. The query itself still takes too long to run. It's about 10 seconds and responds to a navigation menu click. That's too slow. Ninety nine percent of the work is all on one 'sort' task.
So a lot of rows flow through the Sort? And the sort is there to enforce the order of the TOP (5) selection, yes?
I think I'm answering my own question in that there is no way to return a swatch of records ordered by a column without first sorting the whole mess. I don't think it can be fixed in the sql. Might require a schema change.
SQL Server can avoid the sort if it knows the rows are already sorted by the time they arrive at the TOP. Depending on the logic of the sort, you might be able to help SQL Server achieve that by selecting pre-join records in a guaranteed order (i.e. from an index). If all that sounds a bit hand-wavy, it's because it is. Sometimes it is possible, sometimes not. If you are able to post a query plan from a real execution, and some table/query details, that would probably help.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 17, 2010 at 6:46 am
David Nash-367764 (3/17/2010)
What I meant to say when I said it's not a poorly performing query is that the sql generated is very efficient. The query itself still takes too long to run. It's about 10 seconds and responds to a navigation menu click. That's too slow. Ninety nine percent of the work is all on one 'sort' task.
Ah, so it's not removing the sort for the sole sake of removing the sort.
Please post the actual query, table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 17, 2010 at 6:47 am
Bhuvnesh (3/17/2010)
if possible, required ORDER can be set in physical table and then avoid "order by" clause in derived query
Order of returned rows is NOT guaranteed unless ORDER BY is specified in the outer-most query.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 30, 2010 at 12:48 pm
You're returning "navigation data" with 9 million rows in it?
Is that for a menu of choices?
Because if I understood that correctly, the solution is probably "don't do that". 🙂
I'm guessing I just misunderstood...
March 31, 2010 at 7:35 am
Suggestion 1: stop using LINQ 😀
Failing that, make a stored procedure that executes a properly written (and performing) query to do the call that is causing problems and call THAT from LINQ.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply