Dynamic SQL vs. Static SQL Part 2, Code

  • Robert W Marda

    SSChampion

    Points: 13413

    Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rmarda/dynamicvsstatic2.asp

    Robert W. Marda
    Billing and OSS Specialist - SQL Programmer
    MCL Systems

  • flachance

    Valued Member

    Points: 50

    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

    SSC Guru

    Points: 125444

    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

    SSChampion

    Points: 13413

    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
    Billing and OSS Specialist - SQL Programmer
    MCL Systems

  • Andy Warren

    SSC Guru

    Points: 119694

    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

  • Robert W Marda

    SSChampion

    Points: 13413

    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
    Billing and OSS Specialist - SQL Programmer
    MCL Systems

  • Andy Warren

    SSC Guru

    Points: 119694

    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

  • Robert W Marda

    SSChampion

    Points: 13413

    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
    Billing and OSS Specialist - SQL Programmer
    MCL Systems

  • jloesch

    Ten Centuries

    Points: 1004

    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

    SSChampion

    Points: 13413

    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
    Billing and OSS Specialist - SQL Programmer
    MCL Systems

  • RyanRandall

    SSChampion

    Points: 13623

    Good article. For case 1 you could use a function in sql 2000 to simplify the static sql to...

    select * from authors WHERE au_lname IN (SELECT * FROM list_to_table('white,green'))

    where the function is...

    create function list_to_table (@list varchar(8000))

    returns @t table (list_item varchar(100))

    as

    begin

    DECLARE @len int, @CurPos int, @PrevPos int

    SET @len = LEN(@list) + 1

    SET @CurPos = 1

    SET @PrevPos = @CurPos

    WHILE @CurPos < @len + 1

    BEGIN

    IF SUBSTRING(@list + ',', @CurPos, 1) = ','

    BEGIN

    INSERT INTO @t (list_item)

    SELECT SUBSTRING(@list, @PrevPos, @CurPos - @PrevPos)

    SET @PrevPos = @CurPos + 1

    END

    SET @CurPos = @CurPos + 1

    END

    return

    end

    Just a thought.

    Ryan.

    Ryan Randall

    Solutions are easy. Understanding the problem, now, that's the hard part.

  • Robert W Marda

    SSChampion

    Points: 13413

    Thanks!

    Robert Marda

    Robert W. Marda
    Billing and OSS Specialist - SQL Programmer
    MCL Systems

  • jcool

    SSC Eights!

    Points: 863

    In the article you mentioned a 8000 chars limit of @variable for dynamic SQL. You can extend this to several variables like this:

    EXEC (@var1 + @var2 + @var3)

    (see BooksOnline)

    Otherwise, great article.

    Edited by - jcool on 03/18/2002 04:55:54 AM

  • psychonaut

    Grasshopper

    Points: 13

    I found the articles and discussion interesting.

    I have built a web based content management system where the page content is stored in XML representation in one field.

    I did this because it is easy to change the data structure as needed, and each row can have a different XML structure if necessary.

    The down sides to this are:

    • that the content for each page must be less than 8k (not really a problem as greater than 8k would really be too much!)
    • XML markup is mingled with content and thus performing a search may pick up matches in the markup.

    What I would prefer to do is to have each structure stored in its own table, with markup elements one per field.

    However, when reading the data out for display this would require something like:

    select @table from pages where pageid = @pageid

    select * from @table where pageid = @pageid

    As far as I can tell, the solution advocated in these articles would go something like:

    select @table from pages where pageid = @pageid

    if @table = 'structure1'

    begin

    select * from structure1 where pageid = @pageid

    end

    if @table = 'structure2'

    begin

    select * from structure2 where pageid = @pageid

    end

    etc.

    This is certainly harder to maintain than the dynamic SQL alternative.

    Is there a better way, or is dynamic SQL a 'satisfactory' approach?

    BTW I already have to use dynamic SQL for our distributed queries on to Oracle!

  • Robert W Marda

    SSChampion

    Points: 13413

    For performance and security the static SQL is likely the best choice. For development time the dynamic SQL would take less time. After that, the maintenance time required is almost none for both unless changes are required. However, some changes could take the same amount of time to implement as a simple find and replace could do it in both cases. For changes that can't be put in place by find and replace then there is a chance that the dynamic SQL could be easier to maintain.

    There are other factors to consider in the maintenance. The more complex your dynamic SQL the longer it takes to test and debug. This is because you have to run through every possible choice to ensure that the SQL is built properly. With static SQL you simply build it and SQL Server can check it for you and point you to bugs easily.

    Also the more complex the dynamic SQL is the harder it can become to read.

    I don't think you should never use dynamic SQL, I do believe you should limit its use as much as possible. There are some cases when static SQL simply won't work.

    Robert Marda

    Robert W. Marda
    Billing and OSS Specialist - SQL Programmer
    MCL Systems

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply