Dapper调用存储过程查询过慢问题排查与优化请求

DevOps 小满

2026-6-29

存储过程查询性能优化方案求助

使用非Core版本的Entity Framework，通过Dapper调用SQL Server存储过程获取数据并绑定到GridControl，但查询执行速度极慢。数据库数据量并不大，注释掉存储过程中的HAVING子句后问题依然存在，现有优化建议未解决，寻求可行的优化方案。

C#调用代码

public List<dynamic> Search_Material_Issue_Voucher()
{
    try
    {
        var connection = _context.Database.Connection;

        if (connection.State != ConnectionState.Open)
        {
            connection.Open();
        }

        string storedProcedure = "[dbo].[Search_Material_Issue_Voucher]";

        var searchitems = connection.Query<dynamic>(
            storedProcedure,
            commandType: CommandType.StoredProcedure).ToList();

        return searchitems;
    }
    catch (Exception ex)
    {
        Console.WriteLine($"Error: {ex.Message}");
        throw;
    }
}

SQL存储过程代码

USE [AWSdb]
GO
/****** Object:  StoredProcedure [dbo].[Search_Material_Issue_Voucher]    Script Date: 2/21/2024 7:37:29 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Search_Material_Issue_Voucher]
AS
BEGIN
SELECT 
    ROW_NUMBER() OVER (ORDER BY pl.PLName, p.PK, Items.ItemOfPk ASC) AS RowNumber,
    p.ArrivalDate,
    pl.Project,
    po.PoName AS Po,
    v.VendorName AS Vendor,
    Items.ItemId,
    pl.PLName,
    p.PK,
    Items.ItemOfPk,
    Items.Tag,
    Items.Description,
    Units.UnitName AS Unit,
    ISNULL(SUM(Items.Qty), 0) AS QtyPL,
    ISNULL(SUM(li.QtyInLoc), 0) - ISNULL(SUM(req.TotalReserveMivQty), 0) - ISNULL(SUM(req.TotalDelMivQty), 0) - ISNULL(SUM(li.NISQty), 0) - ISNULL(SUM(li.RejectQty), 0) + ISNULL(SUM(req_mrv.TotalReturnAcceptQty * -1), 0) AS Balance,
    ISNULL(SUM(li.QtyInLoc), 0) - ISNULL(SUM(req.TotalDelMivQty), 0) - ISNULL(SUM(li.NISQty), 0) - ISNULL(SUM(li.RejectQty), 0) + ISNULL(SUM(req_mrv.TotalReturnAcceptQty * -1), 0) AS Inventory,
    d.DesciplineName AS Discipline,
    Scopes.ScopeName AS Scope,
    Items.HeatNo,
    Items.BatchNo,
    Items.Remark,
    Items.Hold
FROM
    Items
INNER JOIN Scopes ON Items.ScopeID = Scopes.ScopeID
INNER JOIN Units ON Items.UnitID = Units.UnitID
LEFT OUTER JOIN LocItems AS li ON Items.ItemId = li.ItemId
LEFT OUTER JOIN dbo.ufn_Request_GetBy_LocItemID() AS req ON li.LocItemID = req.LocItemID
LEFT OUTER JOIN dbo.ufn_RequestMRvHmv_GetBy_LocItemID() AS req_mrv ON li.LocItemID = req_mrv.LocItemID
LEFT OUTER JOIN Packages AS p ON Items.PKID = p.PKID
LEFT OUTER JOIN PackingLists AS pl ON p.PLId = pl.PLId
LEFT OUTER JOIN Desciplines AS d ON pl.DesciplineId = d.DesciplineId
LEFT OUTER JOIN Poes AS po ON pl.PoId = po.PoId
LEFT OUTER JOIN Vendors AS v ON pl.VendorId = v.VendorID
GROUP BY 
    p.ArrivalDate,
    pl.Project,
    po.PoName,
    v.VendorName,
    Items.ItemId,
    pl.PLName,
    p.PK,
    Items.ItemOfPk,
    Items.Tag,
    Items.Description,
    Units.UnitName,
    Scopes.ScopeName,
    Items.HeatNo,
    Items.BatchNo,
    Items.Remark,
    Items.Hold,
    d.DesciplineName
HAVING 
    ISNULL(SUM(li.QtyInLoc), 0) - ISNULL(SUM(req.TotalReserveMivQty), 0) - ISNULL(SUM(req.TotalDelMivQty), 0) - ISNULL(SUM(li.NISQty), 0) - ISNULL(SUM(li.RejectQty), 0) + ISNULL(SUM(req_mrv.TotalReturnAcceptQty * -1), 0) > 0

  -- Ensure index exists on relevant columns:
    -- CREATE INDEX IX_Items_ItemId ON Items (ItemId);
    -- CREATE INDEX IX_LocItems_ItemId ON LocItems (ItemId);
    -- CREATE INDEX IX_Packages_PKID ON Packages (PKID);
    -- CREATE INDEX IX_PackingLists_PLId ON PackingLists (PLId);
    -- CREATE INDEX IX_Scopes_ScopeID ON Scopes (ScopeID);
    -- CREATE INDEX IX_Units_UnitID ON Units (UnitID);
    -- CREATE INDEX IX_Poes_PoId ON Poes (PoId);
    -- CREATE INDEX IX_Vendors_VendorID ON Vendors (VendorID);
    -- CREATE INDEX IX_Desciplines_DesciplineId ON Desciplines (DesciplineId);
    -- CREATE INDEX IX_Req_LocItemID ON Requests (LocItemID);
    -- CREATE INDEX IX_ReqMRvHmv_LocItemID ON RequestMRvHmv (LocItemID);

END

优化方向建议

1. 表值函数性能优化

检查ufn_Request_GetBy_LocItemID()和ufn_RequestMRvHmv_GetBy_LocItemID()的类型：如果是多语句表值函数，替换为内联表值函数，后者性能远高于前者。
直接将函数内部逻辑合并到主查询中，避免函数调用带来的额外开销；若必须保留函数，确保函数内部查询有合适的索引支持。

2. 完善索引策略

为Items表创建覆盖索引，减少回表查询：

CREATE NONCLUSTERED INDEX IX_Items_Covering 
ON Items (ItemId, PKID, ScopeID, UnitID) 
INCLUDE (ItemOfPk, Tag, Description, HeatNo, BatchNo, Remark, Hold, Qty);

为LocItems表创建复合索引，覆盖连接和聚合字段：

CREATE NONCLUSTERED INDEX IX_LocItems_ItemId_LocItemID 
ON LocItems (ItemId, LocItemID) 
INCLUDE (QtyInLoc, NISQty, RejectQty);

确保Requests、RequestMRvHmv表有针对LocItemID的索引，并包含需要聚合的字段（如TotalReserveMivQty、TotalDelMivQty等）。

3. 重构查询逻辑，减少重复计算

通过CTE提前计算聚合结果，避免在SELECT和HAVING中重复执行相同的聚合表达式，同时过滤掉不符合条件的数据后再关联其他表，减少数据量：

ALTER PROCEDURE [dbo].[Search_Material_Issue_Voucher]
AS
BEGIN
    WITH ItemAggregates AS (
        SELECT 
            Items.ItemId,
            Items.PKID,
            Items.ScopeID,
            Items.UnitID,
            Items.ItemOfPk,
            Items.Tag,
            Items.Description,
            Items.HeatNo,
            Items.BatchNo,
            Items.Remark,
            Items.Hold,
            Items.Qty,
            ISNULL(SUM(li.QtyInLoc), 0) AS SumQtyInLoc,
            ISNULL(SUM(req.TotalReserveMivQty), 0) AS SumReserve,
            ISNULL(SUM(req.TotalDelMivQty), 0) AS SumDel,
            ISNULL(SUM(li.NISQty), 0) AS SumNIS,
            ISNULL(SUM(li.RejectQty), 0) AS SumReject,
            ISNULL(SUM(req_mrv.TotalReturnAcceptQty * -1), 0) AS SumReturn
        FROM Items
        LEFT OUTER JOIN LocItems AS li ON Items.ItemId = li.ItemId
        LEFT OUTER JOIN dbo.ufn_Request_GetBy_LocItemID() AS req ON li.LocItemID = req.LocItemID
        LEFT OUTER JOIN dbo.ufn_RequestMRvHmv_GetBy_LocItemID() AS req_mrv ON li.LocItemID = req_mrv.LocItemID
        GROUP BY 
            Items.ItemId, Items.PKID, Items.ScopeID, Items.UnitID,
            Items.ItemOfPk, Items.Tag, Items.Description, Items.HeatNo,
            Items.BatchNo, Items.Remark, Items.Hold, Items.Qty
    )
    SELECT 
        ROW_NUMBER() OVER (ORDER BY pl.PLName, p.PK, ia.ItemOfPk ASC) AS RowNumber,
        p.ArrivalDate,
        pl.Project,
        po.PoName AS Po,
        v.VendorName AS Vendor,
        ia.ItemId,
        pl.PLName,
        p.PK,
        ia.ItemOfPk,
        ia.Tag,
        ia.Description,
        Units.UnitName AS Unit,
        ISNULL(ia.Qty, 0) AS QtyPL,
        ia.SumQtyInLoc - ia.SumReserve - ia.SumDel - ia.SumNIS - ia.SumReject + ia.SumReturn AS Balance,
        ia.SumQtyInLoc - ia.SumDel - ia.SumNIS - ia.SumReject + ia.SumReturn AS Inventory,
        d.DesciplineName AS Discipline,
        Scopes.ScopeName AS Scope,
        ia.HeatNo,
        ia.BatchNo,
        ia.Remark,
        ia.Hold
    FROM ItemAggregates ia
    INNER JOIN Scopes ON ia.ScopeID = Scopes.ScopeID
    INNER JOIN Units ON ia.UnitID = Units.UnitID
    LEFT OUTER JOIN Packages AS p ON ia.PKID = p.PKID
    LEFT OUTER JOIN PackingLists AS pl ON p.PLId = pl.PLId
    LEFT OUTER JOIN Desciplines AS d ON pl.DesciplineId = d.DesciplineId
    LEFT OUTER JOIN Poes AS po ON pl.PoId = po.PoId
    LEFT OUTER JOIN Vendors AS v ON pl.VendorId = v.VendorID
    WHERE 
        ia.SumQtyInLoc - ia.SumReserve - ia.SumDel - ia.SumNIS - ia.SumReject + ia.SumReturn > 0
END

4. 分析执行计划定位瓶颈

在SQL Server Management Studio中执行存储过程，查看实际执行计划：

查找耗时占比高的节点（如表扫描、键查找），针对性添加索引；
若出现排序警告（如Sort节点显示内存溢出到磁盘），优化排序字段的索引，或调整服务器内存配置。

5. Dapper调用优化

替换dynamic为强类型实体，减少反射开销：

public class MaterialIssueVoucher
{
    public int RowNumber { get; set; }
    public DateTime? ArrivalDate { get; set; }
    public string Project { get; set; }
    public string Po { get; set; }
    public string Vendor { get; set; }
    public int ItemId { get; set; }
    public string PLName { get; set; }
    public string PK { get; set; }
    public string ItemOfPk { get; set; }
    public string Tag { get; set; }
    public string Description { get; set; }
    public string Unit { get; set; }
    public decimal QtyPL { get; set; }
    public decimal Balance { get; set; }
    public decimal Inventory { get; set; }
    public string Discipline { get; set; }
    public string Scope { get; set; }
    public string HeatNo { get; set; }
    public string BatchNo { get; set; }
    public string Remark { get; set; }
    public bool Hold { get; set; }
}

public List<MaterialIssueVoucher> Search_Material_Issue_Voucher()
{
    try
    {
        using (var connection = _context.Database.Connection)
        {
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }

            return connection.Query<MaterialIssueVoucher>(
                "[dbo].[Search_Material_Issue_Voucher]",
                commandType: CommandType.StoredProcedure).ToList();
        }
    }
    catch (Exception ex)
    {
        Console.WriteLine($"Error: {ex.Message}");
        throw;
    }
}

使用using语句管理数据库连接，确保连接及时释放。

内容的提问来源于stack exchange，提问作者hamid azhdari

Dapper调用存储过程查询过慢问题排查与优化请求

C#调用代码 #

SQL存储过程代码 #

优化方向建议 #

1. 表值函数性能优化 #

2. 完善索引策略 #

3. 重构查询逻辑，减少重复计算 #

4. 分析执行计划定位瓶颈 #

5. Dapper调用优化 #