如何优化SQL Server存储过程批量导入20万+Excel产品数据

阿华AIGC实验室

2026-5-11

优化20万+产品数据导入SQL Server的存储过程

兄弟，你当前的存储过程是单条数据处理的模式，20万条数据就要调用20万次存储过程，光是和数据库的往返通信就占掉了绝大部分时间，这是最核心的性能瓶颈。下面我给你拆解几个关键优化点，尤其是批量处理的改造，能直接把性能拉上去：

1. 先修复条码查询的致命逻辑错误

原代码里的SET @CheckBarcode = (SELECT pp.ProductId FROM Products pp WHERE pp.Barcode LIKE '%@Barcode%');有两个大问题：

你把变量@Barcode当成字符串字面量写在LIKE里了，数据库会直接找包含@Barcode这个字符串的条码，而不是你传入的参数值！这完全是逻辑错误，应该改成精确匹配：pp.Barcode = @Barcode（条码本来就该是唯一的，模糊匹配毫无意义）。
就算变量写对了，模糊匹配也会触发全表扫描，20万次下来性能直接崩掉。

2. 替换低效的ID获取方式

你用MAX(pp.ProductId)获取新插入的ProductId，这种方式在并发场景下很容易拿到错误的ID（比如别人同时插入了数据），而且性能也远不如专门的标识值获取方法：

插入Products后，直接用SET @CheckBarcode = SCOPE_IDENTITY();，它能精准获取当前会话、当前作用域生成的自增ID，高效又安全。
Categories的新ID也一样，用SCOPE_IDENTITY()代替MAX(cc.Category_Id)。

3. 加索引！加索引！加索引！（重要的事说三遍）

没有索引的话，每次查询Barcode和Category_Name都会扫全表，20万次查询的开销简直不敢想：

给Products.Barcode加唯一索引：

CREATE UNIQUE NONCLUSTERED INDEX IX_Products_Barcode ON dbo.Products(Barcode);

给Categories.Category_Name加唯一索引：

CREATE UNIQUE NONCLUSTERED INDEX IX_Categories_CategoryName ON dbo.Categories(Category_Name);

4. 核心优化：改用批量处理（性能提升几十倍）

单条处理的模式是最大的性能杀手，咱们改成表值参数（TVP），一次性传入多条数据，把20万次调用变成几次调用，大幅减少数据库往返开销：

第一步：先创建表值参数类型

这个类型用来承载批量导入的数据结构：

CREATE TYPE dbo.ProductImportType AS TABLE
(
    Category_Id int,
    Category_Name varchar(3000),
    ProductName varchar(3000),
    Brand_Name varchar(2000),
    Barcode varchar(3000),
    Description varchar(4000),
    ProductInventory_UnitPrice decimal,
    ProductInventory_Status bit,
    Product_TotalPrice decimal,
    ProductInventory_SellingPrice decimal,
    ProductInventory_Qty int,
    Brand_Name_Urdu varchar(3000),
    Description_Urdu varchar(4000),
    ProductName_Urdu varchar(3500),
    Category_Name_Urdu varchar(2000)
);

第二步：重写批量处理的存储过程

这个存储过程一次性处理所有导入数据，避免单条调用的开销：

CREATE PROCEDURE [dbo].[BulkAddProductsWithCat_Sp]
    @ImportData dbo.ProductImportType READONLY
AS
BEGIN
    SET NOCOUNT ON; -- 减少网络传输的元数据，提升性能

    -- 1. 批量插入不存在的分类（先把所有需要新增的分类一次性插完）
    INSERT INTO Categories (Category_Name, Category_Name_Urdu, Status)
    SELECT DISTINCT i.Category_Name, i.Category_Name_Urdu, 1
    FROM @ImportData i
    LEFT JOIN Categories c ON c.Category_Name = i.Category_Name
    WHERE c.Category_Id IS NULL;

    -- 2. 批量插入不存在的产品，同时用OUTPUT子句获取新生成的ProductId
    DECLARE @NewProducts TABLE (Barcode varchar(3000), ProductId int);

    INSERT INTO Products (ProductName, Barcode, Description, Status, DateTime, Brand_Name, Category_Id, ProductImage, Brand_Name_Urdu, Description_Urdu, ProductName_Urdu)
    OUTPUT inserted.Barcode, inserted.ProductId INTO @NewProducts(Barcode, ProductId)
    SELECT 
        i.ProductName, i.Barcode, i.Description, 1, GETDATE(), i.Brand_Name, c.Category_Id, 'no Image',
        i.Brand_Name_Urdu, i.Description_Urdu, i.ProductName_Urdu
    FROM @ImportData i
    JOIN Categories c ON c.Category_Name = i.Category_Name
    LEFT JOIN Products p ON p.Barcode = i.Barcode
    WHERE p.ProductId IS NULL;

    -- 3. 批量插入库存数据：不管产品是新的还是已存在的，统一关联ProductId插入
    INSERT INTO ProductInventories (ProductId, ProductInventory_UnitPrice, ProductInventory_SellingPrice, ProductInventory_Qty, ProductInventory_Status, DateTime, Product_TotalPrice)
    SELECT
        COALESCE(np.ProductId, p.ProductId), -- 优先用新插入的ID，没有就用已存在的
        i.ProductInventory_UnitPrice,
        i.ProductInventory_SellingPrice,
        i.ProductInventory_Qty,
        1,
        GETDATE(),
        i.Product_TotalPrice
    FROM @ImportData i
    LEFT JOIN @NewProducts np ON np.Barcode = i.Barcode
    LEFT JOIN Products p ON p.Barcode = i.Barcode;
END;