c# - 仅当记录不存在时才通过存储过程从 TVP 列表中插入项目 - 性能低下

Question

我需要从 .NET 4.5 应用调用存储过程。作为 ORM，我使用 Dapper。

存储过程接受 TVP 参数，仅当具有 car_id 的汽车不存在时，才将汽车从列表插入到表 Cars。

表格:

CREATE TABLE USERS
(
    CAR_ID int IDENTITY PRIMARY KEY,
    CAR_NAME varchar(255),
)

TVP:

CREATE TYPE dbo.CarType
AS TABLE
(
    CAR_ID int null,
    CAR_NAME varchar(800) not null,
);

存储过程:

CREATE PROCEDURE dbo.InsertCars
    @Cars AS CarType READONLY
AS
    BEGIN
        DECLARE @CountOfNew int

        INSERT INTO CARS (CAR_ID, CAR_NAME)
        SELECT C.CAR_ID, C.CAR_NAME
        FROM @CARS C
        WHERE NOT EXISTS(SELECT 1 FROM CARS WHERE CAR_ID = C.CAR_ID)

        SET @CountOfNew = @@ROWCOUNT
        RETURN @CountOfNew      
    END

问题是存储过程的性能。如果表中存在超过 30 000 辆汽车，存储过程调用需要 20 秒。

我调试我的应用程序，通过 SQL Server Management Studio 进行测试，我认为瓶颈是存储过程。

有关如何调整此存储过程的任何建议。

我使用 SQL Server 2012。

谢谢

Answer 1

运行 SQL Server Profiler 并包含 Showplan Statistics XML Profile 事件。对大型案例(>= 30,000 辆汽车)运行查询并查看结果。

根据您的 SQL Server 版本，您可以尝试 MERGE(如果在 SQL 2008 或更高版本上)，或者 LEFT JOIN WHERE Car_ID IS NULL，如下所示:

INSERT INTO CARS (CAR_ID, CARNAME)
SELECT C.CAR_ID, C.CARNAME
FROM @CARS C
LEFT JOIN Cars ON Cars.CAR_ID = C.CAR_ID
WHERE Cars.Car_ID IS NULL -- the car does not already exist

此外，您可以尝试声明 CarType 的主键为 CAR_ID。

(我假设 TVP CarType 键是 CAR_ID 而不是您所写的 CARID。此外，您将此表声明为 USERS 而不是 CARS。)