0

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

  • Hi Jimmy, might be parameter sniffing. blogs.msdn.com/b/turgays/archive/2013/09/10/… easy way to edit this is by reinitializing your parameters which you pass f.e. @Class to the BEGIN part of your proc to f.e. Declare "@ClassInProc varchar(255) = @Class" – Stijn Wynants Mar 1 '16 at 12:17
  • 4
    Are some of the columns numeric? If so, why not declare your parameters with proper data types instead of passing strings? And why are you using dynamic SQL? You should read up on SQL injection and strong typing. – Aaron Bertrand Mar 1 '16 at 13:32
  • Thanks guys I will make the changes and see how it works. Thanks! – J1mmy Mar 1 '16 at 13:35
1

Make your code simple. Do not create dynamic query for simple one table insertion. for example:

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

END
0

I had a similar issue. As Stijn Wynants mentioned, usually it has to do with parameter sniffing. To avoid Parameter sniffing, copy the parameters to local variables and use the local variables in your dynamic SQL.

  • Thanks for your help, I will test and see if solves my problems. – J1mmy Mar 2 '16 at 10:20
0

The solution you're looking for is probably parameterized SQL, so that you aren't generating so many query plans.

When you parameterize it, set both the parameters of the SQL, and the parameters of the stored procedure, to exactly the same as the data types of the target table. Make the app pass in only good values; this may save some implicit conversions.

The solution you MUST USE to prevent SQL Injection is parameterized SQL. Never concatenate values passed in. Never concatenate values in the tables themselves, either, or you can get second order SQL injection.

Three good references: OWASP's Avoiding SQL Injection

Aaron Bertrand's own Bad Habits to Kick : Using EXEC() instead of sp_executesql

SQLServerCentral's SQL Injection and sp_executesql

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.