Enhancing StoredProc caching

Question

Post reply

Enhancing StoredProc caching

iccEC

SSC Rookie

Points: 30
More actions
December 19, 2005 at 3:03 pm

#111578

Hi all!

Consider this simple stored procedure:

CREATE PROCEDURE testProc
            (
@testTable      varchar (30)
            )
AS

            EXEC (‘SELECT TOP 1 FROM ‘ + @testTable)

RETURN 0

It’s been hinted in many articles that SQL Server will recompile the sp on each use because of the direct use of the variant @testTable, so SQL recompiles the sp as to create a new execution plan to better optimise its run.

So the basic suggestion in optimisation was to do this:

CREATE PROCEDURE testProc
            (
@testTable      varchar (30)
            )
AS

            DECLARE      @arg_testTable           varchar (30)
            SET                 @arg_testTable           = @testTable

            EXEC (‘SELECT TOP 1 FROM ‘ + @arg_testTable)

RETURN 0

Until now, everything looks good and makes sense to me. Theoretically, the sp gets cached since there is no unknown variant since the data isn’t being pushed in the sp directly.

Now, I am presently seeing this done at work and would like your comments on this next approach:

CREATE PROCEDURE testProc
            (
@testTable      varchar (30)
            )
AS

            DECLARE      @arg_testTable           varchar (30)
            SET                 @arg_testTable           = @testTable

            EXEC sp_executesql N’SELECT TOP 1 FROM ‘ + @arg_testTable

RETURN 0

To me, this approach doesn’t sound right. I can understand using sp_executesql to cache dynamic sql, but in this context, I don’t see this as helping performance.

First – The actual SELECT gets cached twice. Once from the sp_executesql, and twice because of the sp being cached as well. So basically, we’re using twice the memory size inside the cache for one same routine.

Second – The overhead of the sp_executesql sp being run in the first place when it really doesn’t need to, IMHO. I would be curious to find out if sp_executesql recompiles on each run as well.

Well, that's what I think atm.

Any thoughts on this would be greatly appreciated.
Eric
December 22, 2005 at 8:00 am

This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

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

Jo Pattyn SSC-Dedicated Points: 32347 More actions · Answer 1

In the 3th version it shouldn't recompile because it passed the parameters to another stored procedure.

In version 1 & 2 it will still need to recompile because

EXEC (‘SELECT TOP 1 FROM ‘ + @arg_testTable) changes with each run

sp_executesql will "act" like a stored procedure because it let sqlserver cache the final SELECT TOP 1 FROM MYTABLE

iccEC SSC Rookie Points: 30 More actions · Answer 2

Thanks for taking the time to reply Jo.

Were you able to personnaly try something of the sort on some past project?

I haven't thouroughly tested all this all this yet... I guess I'll need to do some further tests.

Thanks again for your time

Eric

Jo Pattyn SSC-Dedicated Points: 32347 More actions · Answer 3

You're welcome.

I haven't tested the differences as most of my stored procedures are quite static.

You can trace all recompilations with sql profiler or an indication using the performance monitor.