将查询抽象为存储过程会使其运行非常缓慢 [英] 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'

[id] [int] IDENTITY(1,1) NOT NULL
[data] [varchar](512) NULL

此块尝试向数据"插入一个值.如果该值是唯一的，则将其插入.在所有情况下都返回相关数据 id

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); 

当我在原始存储过程中包含这段代码时，它运行良好.然而，为了整洁，我和 DRY，我想我会把它抽象成一个子过程，因为在其他一些 SP 中调用了相同的块

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

然后我这样称呼这个抽象的 SP

I then call this abstracted SP like so

EXEC [q_Data_TryInsert] @data, @data_id OUTPUT

即使代码相同，抽象的 SP 也会使整个过程减慢几个数量级.

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

为什么会这样?

推荐答案

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.

这篇关于将查询抽象为存储过程会使其运行非常缓慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！