Abstracting out a query to a stored procedure make

问题:

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'

[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?

回答1:

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

回答2:

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.

回答3:

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