Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

CASE statement versus dynamic Query Expand / Collapse
Author
Message
Posted Monday, February 25, 2008 12:31 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 03, 2011 9:11 AM
Points: 64, Visits: 104
I have a table with 100 columns. I want to sort on each column. My Front-end decides which column is to be
used for sorting. There are more than 500,000 records in the table.

My stored procedure has a input which decides which column to use for sorting. Now i have two options.

1. Create SQL Query dynamically based on the input parameter.
2. Create SQL Query using CASE statement.

Is there any third way? Also, which one would really work better? CASE statement would have SP's plan created. But,
if i use CASE statement, maintenance would be difficult.

Thanks,
Ramesh.



Post #459880
Posted Monday, February 25, 2008 12:48 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:00 PM
Points: 35,967, Visits: 30,258
Personally, I'd opt for the Dynamic SQL. It's going to be easy to maintain and it's going to be just as fast or faster than anything else because there's probably no chance of reusing an execution plan here, anyway.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #459887
Posted Monday, February 25, 2008 1:21 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 10,910, Visits: 12,547
I find this to be an interesting question since I have read 2 sides to the sorting question. One side says sorting should be done in the app and the other says in SQL. In your case with a dynamic sort, why not do it in the app? Then you can get a reusable plan and the sorting is done in memory on the client. I am definitely interested in hearing other opinions on this.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #459923
Posted Monday, February 25, 2008 1:33 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:00 PM
Points: 35,967, Visits: 30,258
I have to agree with Jack on this one. My premise was based on only whether or not to use CASE or Dynamic SQL. The real key is that sorting is very expensive... send the unsorted data to the client and let the client do some of the work by sorting it.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #459934
Posted Monday, February 25, 2008 1:41 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 10,910, Visits: 12,547
Its always good when Jeff agrees with you on something. If you cannot have the sorting done in the app then dynamic SQL is the way to go. I like to use sp_executesql when I do have to use dynamic SQL.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #459939
Posted Monday, February 25, 2008 1:44 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:12 PM
Points: 7,084, Visits: 14,685
(Not disagreeing with any ofthe above posts - just modulating the answer)

Just keep in mind that sorting at the client should be restricted to SMALL datasets, especially if the sorting is used in combination with a Top x predicate. Anything over a certain size will likely result in a LARGE amount of "unneeded data" being sent to the client, in turn resulting in a large effort on its side, resulting only in a large amount of the transmitted data being tossed by the client.


----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #459941
Posted Monday, February 25, 2008 1:58 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 10,910, Visits: 12,547
Matt has a good point as well. I was assuming (yes I know what assume stands for) that all the columns were being returned and that there was a where clause being applied as well so that only the rows needed were returned. If you are also dynamically determining the select list based on the sort column selected then you should use dynamic sql and I would even say that the query should be built in the app and passed to sql server, I rarely say that! Still using the command object and taking appropriate measures to protect against sql injection.

This also leads to the questions, what are the business reasons for needing to be able to sort on one of 100 columns and do you need to return all 100 columns?




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #459949
Posted Monday, February 25, 2008 2:47 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 03, 2011 9:11 AM
Points: 64, Visits: 104
I thought some more information regarding the application would help you all guys. It is a status flow based .NET Web Application. We are using Grid View to display the data in the web page. It is a business requirement to see all the fields (close to 100) in the file (which we load into the system) in the grid and with the ability to sort based on all the fields (end users use it for reviewing the data). Web application was designed as an Object Oriented and it was decided in the initial phases of the design to use Custom Collection and Data Reader instead of the Data Set. We had performance issue with loading all the records into the grid, tested with 30,000 records running on P4 and 1GB RAM. The application may perform well if tested in the Server but we did not want to take chance since it will have 500 concurrent users at a time. It was decided to go for the SQL Server based sorting instead of a Web Server based sorting techniques.
Post #459980
Posted Monday, February 25, 2008 2:51 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:00 PM
Points: 35,967, Visits: 30,258
Oh, I agree... the suggestion of sorting at the client means that you're sorting only the desired result set and no extra/unwanted data. Don't wanna "pop the pipe". :D

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #459982
Posted Monday, February 25, 2008 3:02 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 10,910, Visits: 12,547
Yeah, sorting that many records on the client, especially since you are using a data reader and putting in a custom collection would stink. Now you need to be really smart in what you use for your clustered index



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #459989
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse