October 15, 2010 at 10:08 am
I am trying to find a solution for changing sort order on a complex query without having to re-write the entire query. I have tried a view and then just sort the view but that was too slow.
Stored procedures are much faster but the only way I can change sort order is to write dynamic SQL which can then not be pre compiled.
The basic underlying issue is that I have complex data that I now need to analyze a variety of ways and I am not a SQL person. Is my best option to explore analysis services and MDX? The sort issue seems very basic and hopefully others have a simple solution.
October 15, 2010 at 11:35 am
Can you provide more details? Maybe post the actual painful query, show the query plan as a view, ordered one way, and as just a query?
SSAS and MDX doesnt solve the problem of ordering data as much as it solves the problem of grouping data. If your ordering makes a query slow, chances are you are dealing with an index issue. Unfortunately we wont be able to point you in the right direction without some details.
October 15, 2010 at 11:41 am
First, a query is a query is a query. SQL Server will store execution plans for queries that use views just as readily as it will store the ones used for procs. The idea that "procs are precompiled and run faster" is more of an urban legend than a reality.
Second, having an Order By clause in a view requires a Top component, and if it's Top 100 Percent, SQL Server will ignore it in the query. It won't actually cause the output data to be in a specific order. You have to specify that in the query that calls the view, not in the view itself.
Third, ordering issues can be (often are) issues with index structure. That's correct.
Fourth, without more details on the table(s) and the select statement, I can't get more specific about a solution for you.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 15, 2010 at 2:17 pm
Thanks so much! This solved my problem so I thought I'd post it:
ORDER BY
CASE WHEN @SortVar='name' AND @order = 'ASC' THEN s.LastName+s.FirstName END ASC,
CASE WHEN @SortVar='name' AND @order = 'DESC' THEN s.LastName+s.FirstName END DESC,
CASE WHEN @SortVar='date' AND @order = 'ASC' THEN a.DateAppointment END ASC,
CASE WHEN @SortVar='date' AND @order = 'DESC' THEN a.DateAppointment END DESC,
CASE WHEN @SortVar='dateupdated' AND @order = 'ASC' THEN
October 15, 2010 at 2:34 pm
smknox (10/15/2010)
Thanks so much! This solved my problem so I thought I'd post it:ORDER BY
CASE WHEN @SortVar='name' AND @order = 'ASC' THEN s.LastName+s.FirstName END ASC,
CASE WHEN @SortVar='name' AND @order = 'DESC' THEN s.LastName+s.FirstName END DESC,
CASE WHEN @SortVar='date' AND @order = 'ASC' THEN a.DateAppointment END ASC,
CASE WHEN @SortVar='date' AND @order = 'DESC' THEN a.DateAppointment END DESC,
CASE WHEN @SortVar='dateupdated' AND @order = 'ASC' THEN
:crazy: I... never knew you could do that. That new in '08 or have I just been really missing the boat the last few years?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 18, 2010 at 11:13 am
I did that in SQL 2000, so it's not new.
It's a performance killer, but it's not a new one.
This kind of thing is one of the valid reasons to use dynamic SQL.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 18, 2010 at 1:39 pm
It's a performance killer only because it prevents the optimizer from identifying an index that could eliminate the sort. If the result set is small, the impact of the sort is less important. However, using parameterized dynamic SQL is a definite option here.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 18, 2010 at 2:58 pm
True. A sort operation on a small dataset is trivial. Can you guarantee the dataset will always be small? Scalability is one of the keys to good code.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply