Post reply

Drawbacks of EXEC 'SQL STATEMENT'

  • August 28, 2008 at 12:05 am

    #191677

    Hello,

    Simple question, what are the drawbacks of using EXEC to execute a character string? I'm looking more in the lines of performance? If there are performance hits, what's a good alternative? I realize this question will yield a lot of "it depends". So, if you could give me a bad use of exec (with character strings) and probably suggest a better approach, that would be great.

    Thanks for everyone's contribution...

  • August 28, 2008 at 1:40 am

    #864170

    One of the drawbacks is in terms of security. Using dynamic SQL requires that the user has rights to the base tables and opens the system up to possible SQL injection attacks. Generally it's recommended that users have only execute rights on procedures and no rights to the base tables

    The second is related to the procedure cache. SQL does cache the execution plans of adhoc code just as it does stored procedures, but the algorithm that checks for reuse of plans is very limited in what it considers the same query. For example, the following 2 queries, if submitted as adhoc code would be considered different. Result of that is that there are two plans in the cache where there only really needs to be one.

    SELECT * FROM SomeTable WHERE SomeColumn = 25

    SELECT * FROM SomeTable WHERE SomeColumn = 25

    Similarly, two queries with different values in the where will usually be considered different queries. The solution to that is to use sp_executesql, rather than EXEC and to parameterise the queries.

    So instead of

    DECLARE @sSQL VARCHAR(200)

    SET @sSQL = 'SELECT * FROM SomeTable WHERE SomeColumn = 25'

    EXEC (@sSQL)

    Rather

    DECLARE @sSQL NVARCHAR(200)

    SET @sSQL = 'SELECT * FROM SomeTable WHERE SomeColumn = @ParamValue'

    EXEC sp_executesql @sSQL, '@ParamValue int', @ParamValue=25

    The problem with having multiple entries in the proc cache is that they tend to displace expensive plans for procedures that you want to be reused. Systems with lots of dynamic SQL tend to have lots of compiles/sec and a higher than necessary CPU load.

    Lastly, if the server has lots of memory (16GB+), lots of adhoc SQL and lots of different users may result in a larger than desired TokenAndPermUserStore. See here for some details - http://sqlinthewild.co.za/index.php/2007/10/21/memory-and-sql-2005-sp2/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 2 (of 2 total)

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