Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dynamic SQL vs. Static SQL Part 2, Code


Dynamic SQL vs. Static SQL Part 2, Code

Author
Message
Robert W Marda
Robert W Marda
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1100 Visits: 82
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rmarda/dynamicvsstatic2.asp



Robert W. Marda
SQL Programmer
Ipreo

flachance
flachance
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 1
Case 3 is what I was most interested in reading about since I faced a similar situation a while back. The solution is simply not a workable one in any cases that require the use of more then 2 variables. I racked by brain for a while before concluding that there was no other solution but to build dynamic SQL for my search problem. Fortunately, my database is accessed only by the web site and therefore I only had to grant SELECT right to the IUSR user.

The CASE in the ORDER BY is a neat trick that I didn't know about.

Good article, keep up the good work.



Antares686
Antares686
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: Moderators
Points: 8730 Visits: 780
Straight foward and giving in both formats to show the implimentation needs was a nice touch.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)



Robert W Marda
Robert W Marda
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1100 Visits: 82
I am glad that article helped both of you. I am still trying to convert many of our dynamic SQL to static SQL and some of these stored procedures have over 20 different variables that determine how the query is built. The conversion process takes a lot of time.

Robert Marda



Robert W. Marda
SQL Programmer
Ipreo

Andy Warren
Andy Warren
SSCertifiable
SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)

Group: Moderators
Points: 7978 Visits: 2709
Interesting article. Not sure I agree with you that static SQL is easier to maintain than dynamic. In my experience dynamic is usually called for mainly by search forms which require read only access - so if you put the user/login in the datareader role you can then pass over whatever you need in the query and its done - no changes required. With static you would HAVE to modify the proc.

Generating all the possible combinations can quickly become a burden. Steve and I have discussed building a tool that would generate the code, but even just assuming that all parameters are and'ed together if you have a lot of params it's huge and Im not sure would be very easy to maintain. Possibly it makes sense to convert the most common ones (or slow ones) to static, fall back to dynamic when that fails.

Beyond that, Im not sure it makes sense to build dynamic sql on the server, in the case of the search scenario anyway. This is easily done on the client and is definitely the way to go when you support all comparisions, not just equality plus logical and.

I'd like to see an assessment of the performance tradeoffs - be good to see just what each one costs.



Andy

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Robert W Marda
Robert W Marda
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1100 Visits: 82
If its a simple query, say only a few lines then you can easily argue the point that dynamic SQL could be simpler than static SQL to maintain. For example in Case 1 of this article the dynamic SQL looks like it is less code and therefore easier to maintain than the static SQL that does the same thing.

However, most dynamic SQL I have seen is long and not simple to read and can require extra time to debug and modify.

Robert Marda



Robert W. Marda
SQL Programmer
Ipreo

Andy Warren
Andy Warren
SSCertifiable
SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)

Group: Moderators
Points: 7978 Visits: 2709
Dont know that I would consider lines of code as my basis, rather the "elegance" of the solution. Guess it also depends on what you call easy to maintain - in my view means that I don't have to touch it. This isn't intended as criticism btw - just another side of the issue!


Andy

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Robert W Marda
Robert W Marda
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1100 Visits: 82
I'm not taking it as criticism. I agree, if you don't ever have to touch it again then yes its simpler. Sometimes I get so focused on one thing that I don't see others. For example I was thinking in terms of the environment in which I now work and most our major stored procedure get modified once every six months and some once every three months. We, of course, have some that don't get touched at all for a year or even longer.

Robert Marda



Robert W. Marda
SQL Programmer
Ipreo

jloesch
jloesch
SSC-Addicted
SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)

Group: General Forum Members
Points: 430 Visits: 7
I like topics like this. It gets me thinking. I would like to present a scenario where dynamic SQL is a better solution, as far as I can see. If you have a partitioned view with lots of tables, when you query the view you want to pull from as few tables as possible, so you want to include the partitioning field in your query as much as possible. If you specify the filter using a variable, then look at the Estimated Execution Plan, you will see that SQL Server goes through all the tables in the view; but if you specify it as a literal (such as '1/16/01' for a date), then SQL Server will only pull from the table(s) that are necessary. I have noticed a huge performance difference with this.

To put this in a stored procedure where the filter value is passed in as a parameter, the only way I know of to get the value specified as a literal is to use dynamic SQL.

My theory on why this is so, is that SQL Server determines which tables of a partitioned view to use in a query when it is initially parsed, at which time the filter values are not known if you use variables.

If there is a better solution to this, I'd be very interested in hearing about it.

James C Loesch


James C Loesch
Robert W Marda
Robert W Marda
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1100 Visits: 82
I'm glad to here you enjoyed the article.

Unfortunately, I don't have a better solution due to the fact that I am still working with SQL Server 7.0 where partitioned views don't exist.

Robert Marda



Robert W. Marda
SQL Programmer
Ipreo

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search