Abstracting out a query to a stored procedure makes it run very slowly

I have a very long stored procedure with a number of blocks of logic in it that insert to different tables. Here’s one such block

I have the following table with a unique constraint on ‘data’

  • Removing duplicate rows (based on values from multiple columns) from SQL table
  • SQL Inline or Scalar Function?
  • sql query get all data from Jan to current month even if there are no records
  • Simplest solution for high availability of SQL server 2008?
  • How do I drop table variables in SQL-Server? Should I even do this?
  • SQL Server Profiler?
  • [id] [int] IDENTITY(1,1) NOT NULL
    [data] [varchar](512) NULL
    

    This block attempts to insert a value to ‘data’. if that value is unique, it is inserted. In all cases the relevant data id is returned

    BEGIN TRY 
        INSERT INTO Data SELECT @data; 
    END TRY 
    BEGIN CATCH 
    END CATCH   
    SET @data_id = (SELECT id FROM Data WHERE data = @data); 
    

    When I include this block of code in my original stored procedure, it runs fine. However, for the sake of neatness I and DRY, I thought I’d abstract it out to a sub-procedure, as the same block is called in a few other SPs

    ALTER PROCEDURE [dbo].[q_Data_TryInsert]
    
       @data nvarchar(512),
       @id INT OUTPUT
    
    AS
    BEGIN
    
        BEGIN TRY 
            INSERT INTO Data SELECT @data; 
        END TRY 
        BEGIN CATCH 
        END CATCH   
        SET @id = (SELECT id FROM Data WHERE data = @data);     
    
    END
    

    I then call this abstracted SP like so

    EXEC [q_Data_TryInsert] @data, @data_id OUTPUT
    

    The abstracted SP slows down the whole process my several orders of magnitude, even though the code is the same.

    Why is this happening?

  • What is the best way to fetch records batch-wise from SQL Server
  • How to connect ASP.NET project with SQL Server database table in Visual Studio (C#)
  • SQL returning custom values based on query results
  • Convert textbox text to integer
  • SQL Server Reporting Services very slow after migration
  • Different output when executing statement directly and from stored procedure?
  • 3 Solutions collect form web for “Abstracting out a query to a stored procedure makes it run very slowly”

    Test for data, saving @id. Insert @data if needed. Update @id if needed.

    BEGIN TRANSACTION
      DECLARE @output TABLE (id int)
    
      SELECT @id = id FROM #Data WHERE data = @data
    
      INSERT Data (data)
      OUTPUT inserted.[id] INTO @output
      SELECT @data
      WHERE @id IS NULL
    
      SELECT TOP 1 @id = id FROM @output
    COMMIT TRANSACTION
    
    INSERT INTO [PKvalue] ([value])
    select 'Data6' as [value] 
     where not exists (select top 1 ID from [PKvalue] where [value] = 'Data6');
    select top 1 ID from [PKvalue] where [value] = 'Data6';
    
    INSERT INTO data (data)
    select @dtata as [data] 
     where not exists (select top 1 ID from [data] where [data] = @data);
    select top 1 ID from [data] where [data] = '@data;
    

    Don’t even need a transaction. That insert is a transaction. Even if another insert happened before the select you would still get the right answer. Only a delete or update could break the select. A transaction has overhead.

    please change

    INSERT INTO Data SELECT @data; 
    

    to

    INSERT INTO Data (data)
     VALUES (@data)
    

    And change

    SET @data_id = (SELECT id FROM Data WHERE data = @data); 
    

    to

    SET @data_id = IDENT_CURRENT('Data')
    

    EDIT:
    to get what you need the store procedure needs to be reworked in this way

    ALTER PROCEDURE [dbo].[q_Data_TryInsert]
    
       @data nvarchar(512),
       @id INT OUTPUT
    
    AS
    BEGIN
      IF NOT EXISTS(SELECT id FROM Data WHERE data = @data)
       BEGIN
        INSERT INTO Data (data) Values (@data) 
        SET @data_id = IDENT_CURRENT('Data')    
       END
      ELSE
        SET @id = (SELECT id FROM Data WHERE data = @data);     
    
    END 
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.