Inserts through Stored Procedure 6 time slower than direct insert We have recently created a stored procedure in order to test the insert speed between a direct query into SQL Server without parametrization (new plan for each different insert) and insert through parametrized stored procedure.

The users are complaining that insert through stored procedure is 6 times slower... How is this possible?

The stored procedure looks like this:

CREATE PROCEDURE [dbo].[spInsertAnalysis] 
    @Class varchar(255),
    @Method varchar(100),
    @Name varchar(100),
    @Origin varchar(20),
    @Ultimate varchar(255),
    @Value varchar(100)
AS
BEGIN
    DECLARE @SQLString NVARCHAR(MAX)

    SET @SQLString = 'INSERT INTO [Analysis] ([Class], [Method], [Name], [Origin], [Ultimate], [Value] ) VALUES (''' +
        @Class + ''', ''' +
        @Method + ''', ''' +
        @Name + ''', ''' +
        @Origin + ''', ''' +
        @Ultimate + ''', ' +
        @Value + ') '

    EXEC (@SQLString)
END


I see that SQL has generated 17 different query plans for this insert and I found that the cause is the numeric value is different for each query plan (The rest is the same):

(@0 varchar(8000),@1 varchar(8000),@2 varchar(8000),@3 varchar(8000),@4 varchar(8000),@5 numeric(15,9))
(@0 varchar(8000),@1 varchar(8000),@2 varchar(8000),@3 varchar(8000),@4 varchar(8000),@5 numeric(14,7))
(@0 varchar(8000),@1 varchar(8000),@2 varchar(8000),@3 varchar(8000),@4 varchar(8000),@5 numeric(14,5))


But anyway I don't know why it is getting 6 times slower than making direct inserts...

Any help will be appreciated
Make your code simple. Do not create dynamic query for simple one table insertion. for example:

create proc [dbo].usp_Add&lt;Your table&gt; List of Parameters--, datatype similar to table columns
AS 
BEGIN
SET NOCOUNT ON
INSERT INTO &lt;MY TABLE&gt; (LIST OF COLUMNS) SELECT @Parameter1, @Parameter2
--OR
INSERT INTO &lt;MY TABLE&gt; (LIST OF COLUMNS) values(@Parameter1, @Parameter2)

END