SQL Server 2008 - Cache Dynamic SQL

Question

Hi All,

I have Created a Procedure for one of our .Net Devs where they can pass in values that will then build and output a result set. Part of the params they pass in will determine which table gets called. My question is how can I ensure that the dynamic sql statement I am building will get cached for faster execution? None of the cache examples I read discussed dynamic table names (might have just overlooked), just dynamic params within the query.

I am using sp_executesql and passing in params, but I am not sure how I use that method for deriving the table name without appending the Parameter to the select statement?

Here is a simple example of how I am currently building the string to generate my result set.

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DynamicSQL]') AND type in (N'U'))
DROP TABLE [dbo].[DynamicSQL]
GO

CREATE TABLE [dbo].[DynamicSQL](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [TestName] [varchar](100) NULL
) ON [PRIMARY]

GO

Insert Into DynamicSQL
Values('Name1');
Insert Into DynamicSQL
Values('Name2');
Insert Into DynamicSQL
Values('Name3');
Insert Into DynamicSQL
Values('Name4');

GO

DECLARE @TableName VARCHAR(50),
        @SQL       NVARCHAR(500)

SELECT @TableName = 'DynamicSQL',
       @SQL = 'Select *
            From ' + @TableName;

execute sp_executesql @SQL;

Any info is greatly appreciated.

--S

Answer 1

I would strongly recommend against this sort of "do it" procedure. It is far better to have your .NET developers work in tandem with a DBA to create proper SQL statements or at the very least use an ORM rather than try to handle database calls in the manner you have suggested. If you are going to force your developers to use stored procedures, then write out the stored procedures. It will make maintenance and performance analysis substantially easier than having one stored proc to rule them all.

how can I ensure that the dynamic sql statement I am building will get cached for faster execution?

SQL Server should cache the execution plan of two identical queries. Thus, if your dynamic SQL generates two identical queries, SQL Server will cache the execution plan. What it will not do is cache two queries that ought to use the same execution plan but are slightly different. For example, if your dynamic SQL generates Select ... From Table Where Col = 1 and Select ... From Table Where Col = 2, SQL Server will calculate two execution plans even if the two values have the same cardinality. This is one place where parametrized queries or statically written stored procedures are better.

I am using sp_executesql and passing in params, but I am not sure how I use that method for deriving the table name without appending the Parameter to the select statement?

If you are going to use sp_executesql, you have to build the entire SQL statement including the table as a string. You cannot pass the table name as a parameter.

Answer 2

you can't cache dynamic table names, a different table is a different plan....as simple as that