Dynamic SQL vs. Static SQL Part 2, Code

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Robert Marda » Dynamic SQL vs. Static SQL Part 2, Code

16 posts, Page 1 of 2

1 2 »»

Dynamic SQL vs. Static SQL Part 2, Code

Rate Topic

Display Mode

Topic Options

Author

Message

Robert W Marda

Posted Sunday, March 03, 2002 12:00 AM

SSC Eights!

Group: General Forum Members
Last Login: Friday, February 15, 2013 10:33 AM
Points: 976, Visits: 48

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

Post #2859

flachance

Posted Sunday, March 03, 2002 9:36 PM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 28, 2004 9:20 PM
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.

Post #29174

Antares686

Posted Monday, March 04, 2002 4:46 AM

SSCrazy Eights

Group: Moderators
Last Login: Tuesday, April 09, 2013 12:53 PM
Points: 8,357, Visits: 684

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)

Post #29175

Robert W Marda

Posted Monday, March 04, 2002 5:23 AM

SSC Eights!

Group: General Forum Members
Last Login: Friday, February 15, 2013 10:33 AM
Points: 976, Visits: 48

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

Post #29176

Andy Warren

Posted Monday, March 04, 2002 7:30 AM

SSCertifiable

Group: Moderators
Last Login: Thursday, May 09, 2013 12:38 PM
Points: 6,462, Visits: 1,384

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
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #29177

Robert W Marda

Posted Monday, March 04, 2002 7:42 AM

SSC Eights!

Group: General Forum Members
Last Login: Friday, February 15, 2013 10:33 AM
Points: 976, Visits: 48

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

Post #29178

Andy Warren

Posted Monday, March 04, 2002 8:16 AM

SSCertifiable

Group: Moderators
Last Login: Thursday, May 09, 2013 12:38 PM
Points: 6,462, Visits: 1,384

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
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #29179

Robert W Marda

Posted Monday, March 04, 2002 8:38 AM

SSC Eights!

Group: General Forum Members
Last Login: Friday, February 15, 2013 10:33 AM
Points: 976, Visits: 48

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

Post #29180

jloesch

Posted Tuesday, March 05, 2002 10:41 AM

SSC-Addicted

Group: General Forum Members
Last Login: Monday, April 18, 2011 5:08 PM
Points: 410, 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

Post #29181

Robert W Marda

Posted Tuesday, March 05, 2002 11:44 AM

SSC Eights!

Group: General Forum Members
Last Login: Friday, February 15, 2013 10:33 AM
Points: 976, Visits: 48

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

Post #29182

« Prev Topic | Next Topic »

16 posts, Page 1 of 2

1 2 »»

Permissions