通过大量插入提高存储proc的速度[Azure-SQL]

Question

运行S0。@@version = Microsoft SQL Azure (RTM) - 12.0.2000.8 Jul 21 2017 14:10:34 Copyright (C) 2017 Microsoft Corporation. All rights reserved.存储的proc使用3个表。一个与价格和特征有关的主要对象。

我有大约10.000件主要物品，每件10个特色和每3000英镑的价格。因此，这将导致rows表有3000万行。

我有一个C#控制台应用程序，它在每个mainobject上运行我存储的proc，并且通过使用类型和表值来加速价格和特性存储。我有一个字典在我的应用程序中的主要目标代码和id，所以我可以填写id的项目，不需要插入。这样我就可以保留主键，因为它们不会有太大变化。一开始我有3个存储过程。每个表一个，但不是3个存储的proc执行，而是移到一个sp中的all。下面是我的SQL代码：

ALTER PROCEDURE [dbo].[OneSpToRuleThemAll]
(
    @code nvarchar(max),
    @name nvarchar(max),
    @lat float,
    @lng float,
    @country int,
    @operator int,
    @id int output,
    @dtFeatures as FeatureType READONLY,
    @dtPrices as PriceType READONLY
)
AS
BEGIN
    SET NOCOUNT ON
-- begin stored proc 1:
    IF (@id > 0)
    begin
        UPDATE MainObject SET 
            [Name] = @name,
            Latitude = @lat,
            Longitude = @lng,
            CountryId = @country,
            OperatorId = @operator,
        WHERE code = @code;
    end
    else
    begin
        INSERT INTO [dbo].[MainObject]
           ([Code]
           ,[Name]
           ,[Latitude]
           ,[Longitude]
           ,[CountryId]
           ,[OperatorId])
     VALUES
           (@code, @name, @lat, @lng, @country, @operator);
            select @id = SCOPE_IDENTITY();
    end
-- begin stored proc 2:
    delete from features where MainObjectId = @id;
        INSERT INTO [dbo].[Features]
           ([ExternalId]
           ,[Mandatory]
           ,MainObjectId])
     select [ExternalId]
           ,[Mandatory]
           ,MainObjectId] from @dtFeatures;
-- begin stored proc 3:
    delete from prices where MainObjectid = @id;
    INSERT INTO [Prices]
        ([From]
        ,[To]
        ,[PriceInclDiscount]
        ,[PriceExclDiscount]
        ,[MainObjectId])
    select [From]
        ,[To]
        ,[PriceInclDiscount]
        ,[PriceExclDiscount]
        ,[MainObjectId] from @dtPrices;

END

正如@S4v1n建议的那样，我不得不发布执行计划。我使用https://stackoverflow.com/a/4146573/169714这段代码获取完整的SQL命令，并将其粘贴到SSMS中，并为其中一个主要对象获得了这个查询计划：

下面是第二个存储的proc的执行计划：

Answer 1

您肯定应该将其划分为多个较小的过程。只使用一个过程就会导致大量的锁定和阻塞，并且通常会降低性能。

至于优化：

第一条语句是基于提供的@Code更新数据的update。除非创建了聚集索引，否则应该在code列上创建非聚集索引，这样就可以防止整个表扫描以找到代码。

删除可以应用相同的逻辑。delete from prices where MainObjectid = @id;在MainObjectID上具有索引，通常在任何Foreign列上都会为您提供良好的性能好处(无论您是删除、加入表，还是更新父表中的记录)。

当您插入一些数据并根据该插入的ID删除数据时，您可以创建一个过程，该过程将执行代码的第一部分(如果满足条件，则更新或插入)，然后返回该ID的值(如何使用输出参数创建对象，您可以将其存储在c#应用程序中的某个int var中)。一旦得到一个值，就可以执行过程的第二部分，包括删除和一些值插入。不过，在同一事务中插入和删除数据时，它不会给您提供最佳解决方案--这意味着它很可能升级为对象锁，使您无法(取决于事务级别)对这些表进行其他选择查询，但它将总体上缩短更新/插入初始语句的%时间。

总之，请记住，从性能和并发的角度来看，较小和更快的事务比更大和更复杂的事务更有效。