Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2003
    Posts
    16

    Unanswered: SProc Performance

    Am I right in assuming that a stored procedure that contained SQL with bound parameters would be faster than using dynamic sql in a string using Execute?

    SELECT @field1, @field2 FROM @tbl1
    -versus-
    EXECUTE 'Select field1, field2 FROM table1'

    If this is true is it possible to have a variable number of parameters like a dynamic fieldlist? Also if there's a good topic in BOL I can look up that might explain these different approaches let me know. THX!

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245

    Re: SProc Performance

    You are correct in your assumption that an SP performs better than dynamic SQL (an ad hoc query). I can think of ways to make an SP return different recordsets based on parameters that are submitted, but I'm not certain that I can think of a way to make an SP return a completely dynamic set of columns based on a submitted field list.

    Regards,

    hmscott

  3. #3
    Join Date
    Oct 2003
    Posts
    16

    Re: SProc Performance

    What if the fieldlist was in a table by queryType, would that help? and the type were passed in as a parameter...

    usp_GetSomeData(queryTypeID int)

    tblQueryFields:
    queryTypeID int,
    queryField varchar(100)

    ...if that can't be made to work I guess the way I'm leaning is to have our configuration tool rebuild a view when the fieldlist changes. But that would require more development on the tool

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    There are many drawbacks to using dynamic SQL, but the performance hit in using dynamic SQL vs bound SQL is not large. The hit comse from the fact that dynamic SQL is not precompiled, but the SQL Server optimizer caches query plans and will reuse them if it finds a similar statement, even if the statement is not identical.

    There are occasions when dynamic SQL runs significantly faster than direct SQL that is loaded up with conditional WHERE clauses because the cost of executing the complex query is greater than that cost of recompiling the simpler one.

    Be aware that there are other drawbacks to dynamic SQL, and it is best to avoid it if possible. (Right, Brett?)

    blindman

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Security is a main concern as well...

    This is a good read...

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=30754
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Feb 2002
    Posts
    2,232
    Check out any article on sql injection - if you have never heard of this, you need to read about it.

    Also, if you must use dynamic sql, use sp_executesql - not exec.

  7. #7
    Join Date
    Oct 2003
    Posts
    16
    Thanks, will do... Great article, though, especially the performance angle...

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    sp_executesql can be used instead of stored procedures to execute a Transact-SQL statement a number of times when the change in parameter values to the statement is the only variation. Because the Transact-SQL statement itself remains constant and only the parameter values change, the Microsoft® SQL Server™ query optimizer is likely to reuse the execution plan it generates for the first execution.
    Never enter a contract with MS...always an escape clause

    Note If object names in the statement string are not fully qualified, the execution plan is not reused.
    ....a minor point, one that I didn't know...

    And why not use EXEC?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Feb 2002
    Posts
    2,232
    Because for sp_executesql, the execution plans are put in the procedure cache and are available for reuse.

    Fully qualifying your objects is always a performance boost (cAsE matters as well), including stored procedures - and a good practice.

    "Likely to reuse" - is true for any query/stored procedure.

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You don't ever restore a database on the same instance with a different name?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •