Running Very Slow

Question

I Use Dapper for Get data from The Below Code

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


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

I Need It's Going Faster RUN , I USE C# AND REGULAR NOT CORE ENTITY FRAMWORK and catch the data with dapper and put it in gridcontrol , i don't khnow why it's so slow , i dont have a lot of data in my database i use chatgpt too but i can't find anything , chatgpt sayed maybe becuase for the having in your code i comment having too but still running verry slow

Answer 1

A few pointers:

Dynamic isn't as optimal as defining the objects/records returned.

Connection is an unmanaged resource so you're best off wrapping it in a using, eg.

using (var connection = _context.Database.Connection))
{
    connection.Open();

Your SQL is quite inefficient with the LEFT OUTER JOIN conditions. Instead of using scalar functions in the LEFT OUTER JOIN conditions, you can use Common Table Expressions (CTEs) to pre-calculate those values and then join with them

Try to avoid using aggregate functions in the HAVING clause, if possible. Instead, move the logic into the SELECT clause or use a subquery to filter the results before grouping.

WITH RequestData AS (
    SELECT 
        li.LocItemID,
        SUM(req.TotalReserveMivQty) AS TotalReserveMivQty,
        SUM(req.TotalDelMivQty) AS TotalDelMivQty,
        SUM(li.NISQty) AS NISQty,
        SUM(li.RejectQty) AS RejectQty,
        SUM(req_mrv.TotalReturnAcceptQty * -1) AS TotalReturnAcceptQty
    FROM 
        LocItems AS li
    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 
        li.LocItemID
),
FilteredItems AS (
    SELECT 
        Items.ItemId,
        Items.ScopeID,
        Items.UnitID,
        Items.PKID,
        Items.Tag,
        Items.Description,
        Items.HeatNo,
        Items.BatchNo,
        Items.Remark,
        Items.Hold,
        ISNULL(SUM(li.QtyInLoc), 0) - ISNULL(SUM(rd.TotalReserveMivQty), 0) - ISNULL(SUM(rd.TotalDelMivQty), 0) - ISNULL(SUM(li.NISQty), 0) - ISNULL(SUM(li.RejectQty), 0) + ISNULL(SUM(rd.TotalReturnAcceptQty), 0) AS Balance,
        ISNULL(SUM(li.QtyInLoc), 0) - ISNULL(SUM(rd.TotalDelMivQty), 0) - ISNULL(SUM(li.NISQty), 0) - ISNULL(SUM(li.RejectQty), 0) + ISNULL(SUM(rd.TotalReturnAcceptQty), 0) AS Inventory
    FROM 
        Items
    LEFT OUTER JOIN LocItems AS li ON Items.ItemId = li.ItemId
    LEFT OUTER JOIN RequestData AS rd ON li.LocItemID = rd.LocItemID
    GROUP BY 
        Items.ItemId,
        Items.ScopeID,
        Items.UnitID,
        Items.PKID,
        Items.Tag,
        Items.Description,
        Items.HeatNo,
        Items.BatchNo,
        Items.Remark,
        Items.Hold
    HAVING 
        ISNULL(SUM(li.QtyInLoc), 0) - ISNULL(SUM(rd.TotalReserveMivQty), 0) - ISNULL(SUM(rd.TotalDelMivQty), 0) - ISNULL(SUM(li.NISQty), 0) - ISNULL(SUM(li.RejectQty), 0) + ISNULL(SUM(rd.TotalReturnAcceptQty), 0) > 0
)
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,
    fi.Balance,
    fi.Inventory,
    d.DesciplineName AS Discipline,
    Scopes.ScopeName AS Scope,
    Items.HeatNo,
    Items.BatchNo,
    Items.Remark,
    Items.Hold
FROM 
    FilteredItems AS fi
INNER JOIN Items ON fi.ItemId = Items.ItemId
INNER JOIN Scopes ON Items.ScopeID = Scopes.ScopeID
INNER JOIN Units ON Items.UnitID = Units.UnitID
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;