CASE statement versus dynamic Query

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Performance Tuning » CASE statement versus dynamic Query

17 posts, Page 1 of 2

1 2 »»

CASE statement versus dynamic Query

Rate Topic

Display Mode

Topic Options

Author

Message

Ramesh Lende

Posted Monday, February 25, 2008 12:31 PM

Valued 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

Jeff Moden

Posted Monday, February 25, 2008 12:48 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 33,112, Visits: 27,038

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #459887

Jack Corbett

Posted Monday, February 25, 2008 1:21 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 10,613, Visits: 11,959

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

Jeff Moden

Posted Monday, February 25, 2008 1:33 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 33,112, Visits: 27,038

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

Post #459934

Jack Corbett

Posted Monday, February 25, 2008 1:41 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 10,613, Visits: 11,959

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.

Post #459939

Matt Miller (#4)

Posted Monday, February 25, 2008 1:44 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 9:08 PM
Points: 7,002, Visits: 14,000

(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

Jack Corbett

Posted Monday, February 25, 2008 1:58 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 10,613, Visits: 11,959

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?

Post #459949

Ramesh Lende

Posted Monday, February 25, 2008 2:47 PM

Valued 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

Jeff Moden

Posted Monday, February 25, 2008 2:51 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 33,112, Visits: 27,038

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

Post #459982

Jack Corbett

Posted Monday, February 25, 2008 3:02 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 10,613, Visits: 11,959

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 w00t

Post #459989

« Prev Topic | Next Topic »

17 posts, Page 1 of 2

1 2 »»

Permissions