SQL Server Performance

Dynamic SQL with Order By clause Parameter

Discussion in 'Performance Tuning for DBAs' started by PAMUR, Aug 10, 2006.

  1. PAMUR New Member

    Could you please tell me if a dynamic sql, created with different order by clause passed as parameter, be efficient interms of execution plan reuse?

    Set @SqlString = 'SELECT * '
    + ' FROM Employee '
    + ' Where i_search_id = @SearchId '
    + ' and LastName Like @LastName '
    + @OrdClause

    EXEC sp_executesql @SqlString,
    N'@SearchId int, @LastName char(4)',
    @SearchId, @LastName

    Thank you.


    Usha Rani
  2. ranjitjain New Member

    have run profiler and tested the execution of sql strings.
    If you are running dynamic sql with sp_executesql sp then sql server can reuse the plans
  3. Adriaan New Member

    The ORDER BY is always applied after the results have been retrieved. So compared to the same query without ORDER BY, the basic query will take the same time and execution plan, and after that SQL does the ordering. SQL should be smart enough to re-use the execution plan, one would think.
  4. Roji. P. Thomas New Member

  5. mmarovic Active Member

    quote:Originally posted by Adriaan

    The ORDER BY is always applied after the results have been retrieved. So compared to the same query without ORDER BY, the basic query will take the same time and execution plan, and after that SQL does the ordering. SQL should be smart enough to re-use the execution plan, one would think.
    You don't have to use dynamic sql just because of different ordering. You can use case operator to achieve the goal. However, if you are going to order by column from the index used to retrieve data, then it could be better to use dynamic sql.

    Adriaan: Execution plan won't be reused. Ordering is part of execution plan, so it is not possible to re-use the same. Besides, query optimizer compares the complete query text to find if plan already exists in the cache.
  6. FrankKalis Moderator

    To illustrate Mirko's point, consider this:


    USE PUBS
    GO
    IF OBJECT_ID('test_me') IS NOT NULL
    DROP PROCEDURE test_me
    GO
    CREATE PROCEDURE test_me
    @ORDER_CRITERIA INT,
    @ORDER_DIRECTION INT
    AS

    IF @ORDER_DIRECTION = 1
    BEGIN
    SELECT
    *
    FROM
    authors
    ORDER BY
    CASE @ORDER_CRITERIA
    WHEN 1 THEN au_lname
    WHEN 2 THEN au_fname
    END
    DESC
    END
    ELSE
    SELECT
    au_lname
    , au_fname
    FROM
    authors
    ORDER BY
    CASE @ORDER_CRITERIA
    WHEN 1 THEN au_lname
    WHEN 2 THEN au_fname
    END
    GO
    EXEC test_me 2,2
    DROP PROCEDURE test_me


    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  7. Roji. P. Thomas New Member

    quote:Originally posted by FrankKalis

    To illustrate Mirko's point, consider this:
    Here is a shorter version.


    ALTER PROCEDURE test_me
    @ORDER_CRITERIA INT,
    @ORDER_DIRECTION INT
    AS
    BEGIN
    SELECT
    au_lname , au_fname
    FROM authors
    ORDER BY
    CASE WHEN @ORDER_CRITERIA = 1 AND @ORDER_DIRECTION = 1 THEN au_lname
    WHEN @ORDER_CRITERIA =2 AND @ORDER_DIRECTION = 1 THEN au_fname END DESC,
    CASE WHEN @ORDER_CRITERIA= 1 AND @ORDER_DIRECTION = 2 THEN au_lname
    WHEN @ORDER_CRITERIA =2 AND @ORDER_DIRECTION = 2 THEN au_fname END ASC
    END
    GO

    Roji. P. Thomas
    Microsoft SQL Server MVP
    http://toponewithties.blogspot.com
  8. PAMUR New Member

    Thank you. Actually there is a small confusion:
    Mmarovic says that the execution plan will not be reused where as Adriaan says it would. Please clarify.

    Thanks you Frank for the clear example and thanks Thomas.

    But any way I do want to know if the plan will be reused if we pass Order by fields as parameter to Dynamic SQL.





    Usha Rani
  9. FrankKalis Moderator

    An easy way would be to wrap it into a stored procedure, run Profiler with monitoring SP:CacheHit and SP:CacheInsert and see what happens when this is called with different clauses. You should observe what Mirko already mentioned. Execution plans won't be reused.

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  10. Adriaan New Member

    Usha Rani,<br /><br />Whenever mmarovic corrects me, he is usually right.[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  11. PAMUR New Member

    Thank You Frank, Adriaan, Mmarovic and Thomas.<br /><br /><br /><br /><br /><br /><br />Usha Rani[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  12. mmarovic Active Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br /><br />Usha Rani,<br /><br />Whenever mmarovic corrects me, he is usually right.[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Usually yes. I respect Adriaan's knowledge and opinions, so I think twice before posting dissagreement. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  13. Adriaan New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by mmarovic</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br /><br />Usha Rani,<br /><br />Whenever mmarovic corrects me, he is usually right.[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Usually yes. I respect Adriaan's knowledge and opinions, so I think twice before posting dissagreement. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">No you don't.[<img src='/community/emoticons/emotion-5.gif' alt=';)' />]
  14. PAMUR New Member

    Ha ha ha. But any way, strange! I have notised that the dynamic Sql on repeated execution is taking 673 nano seconds, where as The sp using the Case in order clause is taking 766. Hmm rest of the parameters are same.

    I wonder why?

    I am going to run Profiler with monitoring SP:CacheHit and SP:CacheInsert as Frank said.

    Usha Rani
  15. PAMUR New Member

    Hmmmmm! [<img src='/community/emoticons/emotion-6.gif' alt=':(' />]<br /><br />Usha Rani
  16. Roji. P. Thomas New Member

    quote:Originally posted by PAMUR
    I have notised that the dynamic Sql on repeated execution is taking 673 nano seconds, where as The sp using the Case in order clause is taking 766
    How did you measure that ?

    Roji. P. Thomas
    Microsoft SQL Server MVP
    http://toponewithties.blogspot.com
  17. mmarovic Active Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Roji. P. Thomas</i><br />How did you measure that ?<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />I guess you are asking about <b>nanoseconds</b> [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  18. Roji. P. Thomas New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by mmarovic</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Roji. P. Thomas</i><br />How did you measure that ?<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />I guess you are asking about <b>nanoseconds</b> [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Yes. I am yet to figure out how to measure anything less than 13 <b>milliseconds</b>, using SQL Servers native tools.<br /><br />Roji. P. Thomas<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br />
  19. PAMUR New Member

    OOps! Sorry I made a mistake. Thank you for pointing out.

    Usha Rani
  20. mmarovic Active Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by mmarovic</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br /><br />Usha Rani,<br /><br />Whenever mmarovic corrects me, he is usually right.[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Usually yes. I respect Adriaan's knowledge and opinions, so I think twice before posting dissagreement. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">No you don't.[<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Thinking...<br />Thinking...<br /><br />Adriaan, I am afraid I dissagree with you. You know about dissagreements I posted, but you don't know about dissagements I haven't posted.<br /><br />[<img src='/community/emoticons/emotion-5.gif' alt=';)' />]
  21. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by mmarovic</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by mmarovic</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br /><br />Usha Rani,<br /><br />Whenever mmarovic corrects me, he is usually right.[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Usually yes. I respect Adriaan's knowledge and opinions, so I think twice before posting dissagreement. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">No you don't.[<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Thinking...<br />Thinking...<br /><br />Adriaan, I am afraid I dissagree with you. You know about dissagreements I posted, but you don't know about dissagements I haven't posted.<br /><br />[<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />[<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  22. Adriaan New Member

    Ah, but you haven't seen the terrific results I get using my enhanced confabulator module!
  23. PAMUR New Member

    Ah, and what is that confabulator module!......<br />[?][<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Usha Rani
  24. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by PAMUR</i><br /><br />Ah, and what is that confabulator module!......<br /><br />Usha Rani<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Hehe, I didn'T want to ask that question, but I'm curious too. Sounds something like a flux compensator. [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  25. PAMUR New Member

    If you don't mind Frank! I don't want to sound ignorant but what is flux compensator? He he hehe

    Usha Rani
  26. FrankKalis Moderator

    Google on this. It's a "thing" from the movie "Back to the future". [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  27. PAMUR New Member

    Well looks like he uses a Flux Compensator for his confabulator module too......<br /><br />[<img src='/community/emoticons/emotion-2.gif' alt=':D' />]
  28. Adriaan New Member

    I never leave home without it.
  29. FrankKalis Moderator

    quote:Originally posted by Adriaan

    I never leave home without it.
    and what is it now???

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  30. PAMUR New Member

    The Confabulator!

    Usha Rani
  31. Adriaan New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br /><br />I never leave home without it.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />and what is it now???<hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">Pretty much the same as before.[<img src='/community/emoticons/emotion-3.gif' alt=':eek:' />)]
  32. FrankKalis Moderator

    yadayadayada....<br />That was almost my invitation for such a comment.[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  33. PAMUR New Member

    Hmm.... What is it? Some private conversation! Will any of you throw some light on it please.
    ok carry on....

    Usha Rani
  34. FrankKalis Moderator

    You're correct. We should continue that in "The lighter side...". [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  35. Adriaan New Member

    Not private - just a little joke amongst ourselves.
  36. PAMUR New Member

    Thank you.

    Usha Rani

Share This Page