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»»

Dynamic SQL vs. Static SQL Part 2, Code Expand / Collapse
Author
Message
Posted Sunday, March 3, 2002 12:00 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 9:05 AM
Points: 976, Visits: 59
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
Posted Sunday, March 3, 2002 9:36 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Monday, March 4, 2002 4:46 AM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: Tuesday, April 22, 2014 1:22 PM
Points: 8,369, Visits: 734
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
Posted Monday, March 4, 2002 5:23 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 9:05 AM
Points: 976, Visits: 59
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
Posted Monday, March 4, 2002 7:30 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Today @ 7:55 AM
Points: 6,779, Visits: 1,858
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
Post #29177
Posted Monday, March 4, 2002 7:42 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 9:05 AM
Points: 976, Visits: 59
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
Posted Monday, March 4, 2002 8:16 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Today @ 7:55 AM
Points: 6,779, Visits: 1,858
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
Post #29179
Posted Monday, March 4, 2002 8:38 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 9:05 AM
Points: 976, Visits: 59
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
Posted Tuesday, March 5, 2002 10:41 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-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
Posted Tuesday, March 5, 2002 11:44 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 9:05 AM
Points: 976, Visits: 59
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 »

Add to briefcase 12»»

Permissions Expand / Collapse