2
\$\begingroup\$

My co-worker claims that using a parameterized Like statement is equivalent to dynamic sql and won't have its execution plan cached for reuse. He says that using sp_executesql will allow the execution plan to be cached, increasing the performance of identical searches. However, in the context of this query, the argument doesn't seem to make sense.

Any way to prove one way or the other?

CREATE PROCEDURE [dbo].[SP_Get_Person]
        @SearchText varchar(50)
AS
BEGIN
        SET NOCOUNT ON;

        ------------------------------------------------------------------------------------
        Declare @SQLString nvarchar(500)
        Declare @ParmDefinition nvarchar(500)
        SET @ParmDefinition = N'@SearchText varchar(50)';

        SET @SQLString = N'Select * FROM PERSON Where LASTNAME Like ''%'' + @SearchText + ''%''';

        EXECUTE sp_executesql @SQLString, @ParmDefinition,
                              @SearchText = @SearchText;
        ------------------------------------------------------------------------------------

        --OR

        ------------------------------------------------------------------------------------
        Select * FROM PERSON Where LASTNAME Like '%' + @SearchText + '%'
        ------------------------------------------------------------------------------------
END
\$\endgroup\$
  • 1
    \$\begingroup\$ This looks like SQL Server, right? Which version? \$\endgroup\$ – Cheran Shunmugavel Jun 2 '12 at 6:12
  • \$\begingroup\$ @CheranS - Sql Server 2005 \$\endgroup\$ – Brandon Boone Jun 2 '12 at 13:29
  • 1
    \$\begingroup\$ Hopefully you will get a good answer, but what stops you from A/B testing it? Properly measuring performance is a very good skill to have. For instance stuq.nl/weblog/2009-01-28/… \$\endgroup\$ – Leonid Jun 3 '12 at 14:22
  • \$\begingroup\$ @Leonid - I took your advice and conducted an experiment. Please see my results below. Any additional comments you may have concerning my conclusions would be appreciated. Thanks! \$\endgroup\$ – Brandon Boone Jun 5 '12 at 15:07
  • \$\begingroup\$ I'm assuming the EXECUTE... one isn't vulnerable to SQL Injection, what about your ad-hoc query? (Sorry, I'm not up on how SQL Server handles that type of stuff). \$\endgroup\$ – Clockwork-Muse Jun 6 '12 at 18:07
2
\$\begingroup\$

According to this article:

SQL Server can avoid compilations of previously executed queries by using four mechanisms to make plan caching accessible in a wide set of situations.

  • Adhoc query caching
  • Autoparameterization
  • Prepared queries, using either sp_executesql or the prepare and execute method invoked through your API
  • Stored procedures or other compiled objects (triggers, TVFs, etc.)

In other words, both of your examples should fit SQL Server's criteria for query caching, since one is an ad-hoc query that will be textually the same each time it is run, and the other is a prepared query using sp_executesql.

In addition, it appears that either one would appear in a stored procedure, which itself should have a query execution plan cached. The sp_executesql approach may be slightly slower because it's doing more work to set up the query, but it's possible that SQL Server actually precompiles the call into something that resembles the second query.

As in all cases of optimization, the only way to be sure is to test both approaches against your expected data set.

The second (ad-hoc) query is much more readable, in my opinion. All else being equal, I'd go with that.

\$\endgroup\$
  • \$\begingroup\$ In case you're interested, I conducted an experiment based off the information you provided. I have posted it as a follow up answer to my own question. Thanks for your help. \$\endgroup\$ – Brandon Boone Jun 5 '12 at 15:04
4
\$\begingroup\$

Armed with the article and information provided by @StriplingWarrior, I conducted an experiment and am posting it as an answer to my own question. Please feel free to comment if I have erred in my conclusions.

Assumptions

SQL Server can avoid compilations of previously executed queries by using four mechanisms to make plan caching accessible in a wide set of situations.

  • Adhoc query caching
  • Autoparameterization
  • Prepared queries, using either sp_executesql or the prepare and execute method invoked through your API
  • Stored procedures or other compiled objects (triggers, TVFs, etc.)

SQL Server stores it’s caching plans in a metadata table called sys.dm_exec_cached_plans

Examining the cache

LEGEND:

  • Proc (Stored procedure) – “By default, the cached plan will be reused for all successive executions…. unlike the plans cached and reused with sp_executesql, you have an option with stored procedures and user-defined scalar functions to force recompilation when the object is executed.”
  • Prepared (Prepared statement) – ”The stored procedure sp_executesql is halfway between adhoc caching and stored procedures. Using sp_executesql requires that you identify the parameters and their datatypes, but doesn’t require all the persistent object management needed for stored procedures and other programmed objects.”
  • Adhoc (Adhoc query) – “…even when SQL Server caches your adhoc queries, you might not be able to depend on their reuse. When SQL Server caches the plan from an adhoc query, the cached plan will be used only if a subsequent batch matches exactly.”

DATA:

  1. Determine what caching mechanisms are used outside the context of a stored procedure

    Running the attached query NoStoredProcs.sql (see Resources section) yields the following results:

    Query Results 1

    You can see that the Query executed with sp_executesql has its query plan cached as a Prepared type while the inline statements were cached as Adhoc types.

  2. Determine what caching mechanisms are used within the context of a stored procedure

    Running the attached query WithStoredProcs.sql (see Resources section) yields the following results:

    Query Results 2

    SP_Get_Person1 – Proc & Prepared

    SP_Get_Person2 – Proc

    SP_Get_Person3 – Proc & Adhoc

CONCLUSION:

It would seem that since there was no additional Adhoc cache plan created for SP_Get_Person2 that its structure can stand on its own (within a Stored Procedure), in terms of database caching. This can be confirmed by the fact that an Adhoc cache plan was created for this query outside the context of the stored procedure, but not when placed inside the stored procedure.

Performance

LEGEND:

  • Client processing time - The cumulative amount of time that the client spent executing code while the query was executed.
  • Total execution time - The cumulative amount of time (in milliseconds) that the client spent processing while the query was executed, including the time that the client spent waiting for replies from the server as well as the time spent executing code.
  • Wait time on server replies - The cumulative amount of time (in milliseconds) that the client spent while it waited for the server to reply.

DATA:

SP_Get_Person1 Query Results 3

SP_Get_Person2 Query Results 4

SP_Get_Person3 Query Results 5

CONCLUSION:

From the results above, I can only conclude that any performance gains or losses between SP_Get_Person1 and SP_Get_Person2 are inconclusive/negligible since the average total execution time consistently differs in terms of microseconds. However, it is interesting to note that the average time for SP_Get_Person3 is significantly lower. I would strongly caution that this is not necessarily evidence of a reliable performance gain since every search would result in a new Adhoc query (SQL server did not autoparamatarize this query during my tests). Therefore it is inconclusive what the effect of a growing set of Adhoc queries would have on the database. Furthermore, we lose the safety of typed parameters when using this method.

Resources

NoStoredProcs.sql

use DemoDatabase

dbcc freeproccache;
GO
DECLARE @SearchText varchar(50)
SET @SearchText = 'Abatemarco'
Select * FROM PERSON Where LASTNAME Like '%' + @SearchText + '%'    

GO
DECLARE @SearchText varchar(50)
SET @SearchText = 'Abatemarco'
EXECUTE sp_executesql N'Select * FROM PERSON Where LASTNAME Like ''%'' + @SearchText + ''%''', N'@SearchText varchar(50)',
                      @SearchText = @SearchText;

GO
DECLARE @SearchText varchar(50)
SET @SearchText = 'Abatemarco'
EXECUTE ( 'Select * FROM PERSON Where LASTNAME Like ''%' + @SearchText + '%''')

GO
SELECT usecounts, cacheobjtype, objtype, [text]
FROM sys.dm_exec_cached_plans P
    CROSS APPLY sys.dm_exec_sql_text (plan_handle)
WHERE cacheobjtype = 'Compiled Plan'
    AND [text] NOT LIKE '%dm_exec_cached_plans%';

WithStoredProcs.sql

use DemoDatabase

dbcc freeproccache;
--DROP PROCEDURE [SP_Get_Person1]
--DROP PROCEDURE [SP_Get_Person2]
--DROP PROCEDURE [SP_Get_Person3]
GO

    CREATE PROCEDURE [dbo].[SP_Get_Person1]
            @SearchText varchar(50)
    AS
    BEGIN
            SET NOCOUNT ON;
            EXECUTE sp_executesql N'Select * FROM PERSON Where LASTNAME Like ''%'' + @SearchText + ''%''', N'@SearchText varchar(50)',
                                  @SearchText = @SearchText;
    END

GO

    CREATE PROCEDURE [dbo].[SP_Get_Person2]
            @SearchText varchar(50)
    AS
    BEGIN
            SET NOCOUNT ON;
            Select * FROM PERSON Where LASTNAME Like '%' + @SearchText + '%'
    END
GO

    CREATE PROCEDURE [dbo].[SP_Get_Person3]
            @SearchText varchar(50)
    AS
    BEGIN
            SET NOCOUNT ON;
            EXECUTE ( 'Select * FROM PERSON Where LASTNAME Like ''%' + @SearchText + '%''')
    END
GO

    --SET STATISTICS IO ON
    --SET STATISTICS TIME ON 
GO

    Execute [SP_Get_Person1] @SearchText = 'Abatemarco'

GO

    Execute [SP_Get_Person2] @SearchText = 'Abatemarco'

GO

    Execute [SP_Get_Person3] @SearchText = 'Abatemarco'

GO

    --SET STATISTICS IO OFF
    --SET STATISTICS TIME OFF 
GO
    SELECT usecounts, cacheobjtype, objtype, [text]
    FROM sys.dm_exec_cached_plans P
        CROSS APPLY sys.dm_exec_sql_text (plan_handle)
    WHERE cacheobjtype = 'Compiled Plan'
        AND [text] NOT LIKE '%dm_exec_cached_plans%';

Tools

Statistics were gathered using the Include Client Statistics button in SQL Server Management Studio. Only the last 10 query results are stored and averaged. The results can be cleared from the menu: Query -> Reset Client Statistics.

Client Statistics Button

Client Statistics Tab

\$\endgroup\$
  • 2
    \$\begingroup\$ Nicely done. What I find particularly interesting is that SP_Get_Person3 appeared to do better on every trial run. If its performance gain was due to caching the entire query, I would have expected it to take as long as the others on at least the first trial run. Did you clear the query cache before starting the test? I'd also be curious to see how the performance would compare if you used a different search string for each test. \$\endgroup\$ – StriplingWarrior Jun 5 '12 at 16:05
  • \$\begingroup\$ Very cool indeed. I do not have anything to add at this point other than a question - which tools did you use to get that cool summary and how? \$\endgroup\$ – Leonid Jun 5 '12 at 17:22
  • \$\begingroup\$ Thanks @Leonid - See my edit above. I added a section for the tools. \$\endgroup\$ – Brandon Boone Jun 5 '12 at 17:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.