May 15, 2012 at 11:39 am
Does anybody no sql command in ADO.NET connection support "order by " clause or if there is a work around for it. I am trying to avoid "Sort" component which is very expensive?
May 15, 2012 at 11:48 am
Are you trying to move the sorting of the result set from the server to the client? How big is the result set?
May 15, 2012 at 12:50 pm
There are lots of ways you can sort data in .NET. It really depends on what you are doing.
http://msdn.microsoft.com/en-us/library/13wb36xf.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 16, 2012 at 3:04 am
Are you using an Execute SQL Task?
You should be able to use the ORDER BY clause just in your SQL statement.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 16, 2012 at 3:10 am
MisLead (5/15/2012)
Does anybody no sql command in ADO.NET connection support "order by " clause or if there is a work around for it. I am trying to avoid "Sort" component which is very expensive?
why is your sql server sort expensive ?
are there indexes to support the query? - a lot of poeple reply to this "YES" without actually looking at a query plan - why not post the query plan for us and maybe the table structure or query
MVDBA
May 16, 2012 at 3:13 am
michael vessey (5/16/2012)
MisLead (5/15/2012)
Does anybody no sql command in ADO.NET connection support "order by " clause or if there is a work around for it. I am trying to avoid "Sort" component which is very expensive?why is your sql server sort expensive ?
are there indexes to support the query? - a lot of poeple reply to this "YES" without actually looking at a query plan - why not post the query plan for us and maybe the table structure or query
We're talking about an SSIS sort component, not an ORDER BY clause in SQL Server.
The OP is actually trying to use an ORDER BY clause to get rid of this SSIS component (which is indeed horrible for performance).
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 16, 2012 at 12:05 pm
Lynn Pettis (5/15/2012)
Are you trying to move the sorting of the result set from the server to the client? How big is the result set?
Lynn,
Actually I need to use the "MERGE" component to combine two result sets both coming from ADO.net source. If I could use the "Order By" clause in the statement, i wouldn't have to use the "SORT" component. If at the end of the day, if i have to "SORT" component values, does the number of sorting keys will impact performance right?
For instance, chosing just ID as sorting key will perform better than chosing ID and name , right?
May 16, 2012 at 12:41 pm
MisLead (5/16/2012)
Lynn Pettis (5/15/2012)
Are you trying to move the sorting of the result set from the server to the client? How big is the result set?Lynn,
Actually I need to use the "MERGE" component to combine two result sets both coming from ADO.net source. If I could use the "Order By" clause in the statement, i wouldn't have to use the "SORT" component. If at the end of the day, if i have to "SORT" component values, does the number of sorting keys will impact performance right?
For instance, chosing just ID as sorting key will perform better than chosing ID and name , right?
Why don't you use the ORDER BY in the source?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 16, 2012 at 12:46 pm
Koen Verbeeck (5/16/2012)
MisLead (5/16/2012)
Lynn Pettis (5/15/2012)
Are you trying to move the sorting of the result set from the server to the client? How big is the result set?Lynn,
Actually I need to use the "MERGE" component to combine two result sets both coming from ADO.net source. If I could use the "Order By" clause in the statement, i wouldn't have to use the "SORT" component. If at the end of the day, if i have to "SORT" component values, does the number of sorting keys will impact performance right?
For instance, chosing just ID as sorting key will perform better than chosing ID and name , right?
Why don't you use the ORDER BY in the source?
Koen is correct, you should be able to specify the ORDER BY in your original query, but that may not be enough. You will need to do some extra research because I ran into this issue before and it wasn't enough. Unfortunately I haven't worked with SSIS for a few years now and I can't remember exactly where I had to make the change, but I had to set a flag or property to indicate that the data was already sorted.
May 16, 2012 at 12:49 pm
Lynn is right. You need to open the advanced editor of the source component and set the IsSorted property to TRUE. Then you have to indicate which columns have been sorted and in what order.
Sort Data for the Merge and Merge Join Transformations
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 16, 2012 at 12:53 pm
Koen Verbeeck (5/16/2012)
Lynn is right. You need to open the advanced editor of the source component and set the IsSorted property to TRUE. Then you have to indicate which columns have been sorted and in what order.
Thank you Koen. That is just what I was talking about even if I didn't know remember what exactly needed to be done.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply