Hi,
I have created a MS SQL store procedure for displaying store item balances. That procedure takes nearly 2 minutes for display records. I want to optimized that procedure for reduce the time.
ALTER PROCEDURE [dbo].[StoreItemsBalance_Stors_ID1]
@Stors_ID int = 23,
@Data1 date ='2015-01-01',
@Data2 date = '2022-01-31',
@Category_ID int = 0,
@AssetTypeID int = 0,
@ItemTypeID int = 0,
@TransID int = 0, --1 no trasacation, 2 trasacation
@SearchText nvarchar(20) = ''
AS
BEGIN
SET NOCOUNT ON;
DROP TABLE IF EXISTS #TempTableStoreBal
DECLARE @CategoryID1 NVARCHAR(2) = (select case when @Category_ID = 0 then '' else FORMAT(@Category_ID, 'd2') end)
DECLARE @AssetTypeID1 NVARCHAR(1) = (select case when @AssetTypeID = 0 then '' else FORMAT(@AssetTypeID, 'd1') end)
DECLARE @ItemTypeID1 NVARCHAR(1) = (select case when @ItemTypeID = 0 then '' else FORMAT(@ItemTypeID, 'd1') end)
DECLARE @TransID1 NVARCHAR(1) = (select case when @TransID = 0 then '' else FORMAT(@TransID, 'd1') end);
CREATE TABLE #TempTableStoreBal (Stors_ID int, Date1 date, TypeID int, Item_ID int, Qty decimal(18,4), Amount decimal(18,4));
WITH CTE AS
(
--DTN--
SELECT A.Stors_ID as 'Stors_ID',
A.Date1 as 'Date1',
A.TypeID as 'TypeID',
B.Item_ID as 'Item_ID',
isnull((B.QTY * -1),0) as 'Qty',
isnull((B.Amount * -1),0) as 'Amount'
FROM tbl_DTN_Items B
JOIN tbl_DTN A ON B.DTN_ID = A.DTN_ID
WHERE A.Stors_ID = @Stors_ID AND B.Active = 1
UNION ALL
--DGRN--
SELECT A.Stors_ID as 'Stors_ID',
A.Date1 as 'Date1',
A.TypeID as 'TypeID',
B.Item_ID as 'Item_ID',
isnull((B.QTY * 1),0) as 'Qty',
isnull((B.Amount * 1),0) as 'Amount'
FROM tbl_DGRN_Items B
JOIN tbl_DGRN A ON B.DGRN_ID = A.DGRN_ID
WHERE A.Stors_ID = @Stors_ID AND B.Active = 1
UNION ALL
--GRN--
SELECT A.Stors_ID as 'Stors_ID',
A.Date1 as 'Date1',
A.TypeID as 'TypeID',
B.Item_ID as 'Item_ID',
isnull((B.QTY * 1),0) as 'Qty',
isnull(((B.Amount + B.NbtAmount) * 1),0) as 'Amount'
FROM tbl_GRN_Items B
JOIN tbl_GRN A ON B.GRN_ID = A.GRN_ID
WHERE A.Stors_ID = @Stors_ID AND B.Active = 1
UNION ALL
--SIN--
SELECT A.Stors_ID as 'Stors_ID',
A.Date1 as 'Date1',
A.TypeID as 'TypeID',
B.Item_ID as 'Item_ID',
isnull((B.QTY * -1),0) as 'Qty',
isnull((B.Amount * -1),0) as 'Amount'
FROM tbl_SR_Items B
JOIN tbl_SR A ON B.SR_ID = A.SR_ID
WHERE A.Stors_ID = @Stors_ID AND B.Active = 1
UNION ALL
--SIRN--
SELECT A.Stors_ID as 'Stors_ID',
A.Date1 as 'Date1',
A.TypeID as 'TypeID',
B.Item_ID as 'Item_ID',
isnull((B.QTY * 1),0) as 'Qty',
isnull((B.Amount * 1),0) as 'Amount'
FROM SRRN_Items B
JOIN SRRN_Header A ON B.SrrnID = A.SrrnID
WHERE A.Stors_ID = @Stors_ID AND B.Active = 1
UNION ALL
--RN--
SELECT A.Stors_ID as 'Stors_ID',
A.Date1 as 'Date1',
A.TypeID as 'TypeID',
B.Item_ID as 'Item_ID',
isnull((B.QTY * -1),0) as 'Qty',
isnull(((B.Amount + B.NbtAmount) * -1),0) as 'Amount'
FROM RN_Items B
JOIN RN_Header A ON B.RnID = A.RnID
WHERE A.Stors_ID = @Stors_ID AND B.Active = 1
UNION ALL
--SCN--
SELECT A.Stors_ID as 'Stors_ID',
A.Date1 as 'Date1',
A.TypeID as 'TypeID',
B.Item_ID as 'Item_ID',
isnull((B.QTY * -1),0) as 'Qty',
isnull((B.Amount * -1),0) as 'Amount'
FROM tbl_SCN_Items B
JOIN tbl_SCN A ON B.SCN_ID = A.SCN_ID
WHERE A.Stors_ID = @Stors_ID AND B.Active = 1
UNION ALL
--RSCN--
SELECT A.Stors_ID as 'Stors_ID',
A.Date1 as 'Date1',
A.TypeID as 'TypeID',
B.Item_ID as 'Item_ID',
isnull((B.QTY * 1),0) as 'Qty',
isnull((B.Amount * 1),0) as 'Amount'
FROM tbl_RSCN_Items B
JOIN tbl_RSCN A ON B.RSCN_ID = A.RSCN_ID
WHERE A.Stors_ID = @Stors_ID AND B.Active = 1
UNION ALL
--MIR--
SELECT A.Stors_ID as 'Stors_ID',
A.Date1 as 'Date1',
A.TypeID as 'TypeID',
B.Item_ID as 'Item_ID',
isnull((B.QTY * 1),0) as 'Qty',
isnull((B.Amount * 1),0) as 'Amount'
FROM MIR_Items B
JOIN MIR_Header A ON B.MirID = A.MirID
WHERE A.Stors_ID = @Stors_ID AND B.Active = 1
UNION ALL
--SVN--
SELECT A.Stors_ID as 'Stors_ID',
A.Date1 as 'Date1',
A.TypeID as 'TypeID',
B.Item_ID as 'Item_ID',
isnull(B.QTY,0) as 'Qty',
isnull(B.Amount,0) as 'Amount'
FROM tbl_SVN_Items B
JOIN tbl_SVN A ON B.SVN_ID = A.SVN_ID
WHERE A.Stors_ID = @Stors_ID AND B.Active = 1
UNION ALL
--RIR--
SELECT A.Stors_ID as 'Stors_ID',
A.Date1 as 'Date1',
A.TypeID as 'TypeID',
B.Item_ID as 'Item_ID',
isnull(B.QTY,0) as 'Qty',
isnull(B.Amount,0) as 'Amount'
FROM RIR_Items B
JOIN RIR_Header A ON B.RirID = A.RirID
WHERE A.Stors_ID = @Stors_ID AND B.Active = 1
UNION ALL
--GPN--
SELECT A.Stors_ID as 'Stors_ID',
A.Date1 as 'Date1',
A.TypeID as 'TypeID',
B.Item_ID as 'Item_ID',
isnull((B.QTY * -1),0) as 'Qty',
isnull((B.Amount * -1),0) as 'Amount'
FROM GPN_Items B
JOIN GPN_Header A ON B.GpnHeaderID = A.GpnHeaderID
WHERE A.Stors_ID = @Stors_ID AND B.Active = 1
UNION ALL
--GPRN--
SELECT A.Stors_ID as 'Stors_ID',
A.Date1 as 'Date1',
A.TypeID as 'TypeID',
B.Item_ID as 'Item_ID',
isnull((B.QTY * 1),0) as 'Qty',
isnull((B.Amount * 1),0) as 'Amount'
FROM GPRN_Items B
JOIN GPRN_Header A ON B.GprnHeaderID = A.GprnHeaderID
WHERE A.Stors_ID = @Stors_ID AND B.Active = 1
)
--ACTION--
INSERT INTO #TempTableStoreBal (Stors_ID, Date1, TypeID, Item_ID, Qty, Amount)
SELECT Stors_ID, Date1, TypeID, Item_ID, Qty, Amount FROM CTE C1 ORDER BY C1.Stors_ID ASC, C1.TypeID ASC, C1.Item_ID ASC
--- action---
--SELECT * FROM #TempTableStoreBal
SELECT I.Item_ID as 'ItemID',
I.Item_Code as 'ItemCode',
I.Item_Description as 'ItemDescription',
U.Unit_Code as 'UnitCode',
I.Unit_ID as 'Unit_ID',
C.Category_Description as 'CategoryName',
I.Category_ID as 'CategoryID',
I.AssetTypeID as 'AssetTypeID',
Iat.AssetType as 'AssetType',
I.ItemTypeID as 'ItemTypeID',
It.ItemTypeName as 'ItemTypeName',
CONVERT(DOUBLE PRECISION,C1.Qty) as 'QtyBf',
C1.Amount as 'AmountBf',
CONVERT(DOUBLE PRECISION,C2.Qty) as 'Qty2',
C2.Amount as 'Amount2',
CONVERT(DOUBLE PRECISION,Cdgrn.Qty) as 'QtyCdgrn',
Cdgrn.Amount as 'AmountCdgrn',
CONVERT(DOUBLE PRECISION,Cgrn.Qty) as 'QtyCgrn',
Cgrn.Amount as 'AmountCgrn',
CONVERT(DOUBLE PRECISION,Cdtn.Qty) as 'QtyCdtn',
Cdtn.Amount as 'AmountCdtn',
CONVERT(DOUBLE PRECISION,Csr.Qty) as 'QtyCsr',
Csr.Amount as 'AmountCsr',
CONVERT(DOUBLE PRECISION,Csrrn.Qty) as 'QtyCsrrn',
Csrrn.Amount as 'AmountCsrrn',
CONVERT(DOUBLE PRECISION,Crn.Qty) as 'QtyCrn',
Crn.Amount as 'AmountCrn',
CONVERT(DOUBLE PRECISION,Cscn.Qty) as 'QtyCscn',
Cscn.Amount as 'AmountCscn',
CONVERT(DOUBLE PRECISION,Crscn.Qty) as 'QtyCrscn',
Crscn.Amount as 'AmountCrscn',
CONVERT(DOUBLE PRECISION,Cmir.Qty) as 'QtyCmir',
Cmir.Amount as 'AmountCmir',
CONVERT(DOUBLE PRECISION,Csvn.Qty) as 'QtyCsvn',
Csvn.Amount as 'AmountCsvn',
CONVERT(DOUBLE PRECISION,Cgpn.Qty) as 'QtyCgpn',
Cgpn.Amount as 'AmountCgpn',
CONVERT(DOUBLE PRECISION,Cgprn.Qty) as 'QtyCgprn',
Cgprn.Amount as 'AmountCgprn',
CONVERT(DOUBLE PRECISION,(isnull(C1.Qty,0) + isnull(C2.Qty,0))) as 'QtyCf',
(isnull(C1.Amount,0) + isnull(C2.Amount,0)) as 'AmountCf',
CONVERT(DOUBLE PRECISION,C3.Qty) as 'QtyBalance',
C3.Amount as 'AmountBalance',
CONVERT(DOUBLE PRECISION,C4.Qty) as 'QtyRIR',
C4.Amount as 'AmountRIR',
CASE
WHEN C2.Qty is null THEN 1
WHEN C2.Qty is not null THEN 2
END as 'TransID',
convert(varchar, SvnDate.SvnDate1, 107) as 'LastSvnDate',
case I.Active_Status
when 0 then '<span style="color:red">✖</span>'
when 1 then '<span style="color:blue">✔</span>'
end as 'Active1' ,
FORMAT(@Stors_ID, 'd4') + FORMAT(I.Item_ID, 'd8') as 'NewID'
FROM Items I
JOIN Units U ON I.Unit_ID = U.Unit_ID
JOIN Category C ON I.Category_ID = C.Category_ID
JOIN Item_Asset_Type Iat ON I.AssetTypeID = Iat.AssetTypeID
JOIN ItemType It ON I.ItemTypeID = It.ItemTypeID
--All
JOIN ( SELECT Item_ID as 'Item_ID',
SUM(Qty) as 'Qty',
SUM(Amount) as 'Amount'
FROM #TempTableStoreBal
WHERE TypeID <> 11 --AND Date1 > @Data2
GROUP BY Item_ID) C3 ON I.Item_ID = C3.Item_ID
--within the period
LEFT JOIN ( SELECT Item_ID as 'Item_ID',
SUM(Qty) as 'Qty',
SUM(Amount) as 'Amount'
FROM #TempTableStoreBal
WHERE TypeID <> 11 AND Date1 BETWEEN @Data1 AND @Data2
GROUP BY Item_ID) C2 ON I.Item_ID = C2.Item_ID
--DGRN
LEFT JOIN ( SELECT Item_ID as 'Item_ID',
SUM(Qty) as 'Qty',
SUM(Amount) as 'Amount'
FROM #TempTableStoreBal
WHERE TypeID = 5 AND Date1 BETWEEN @Data1 AND @Data2
GROUP BY Item_ID) Cdgrn ON I.Item_ID = Cdgrn.Item_ID
--GRN
LEFT JOIN ( SELECT Item_ID as 'Item_ID',
SUM(Qty) as 'Qty',
SUM(Amount) as 'Amount'
FROM #TempTableStoreBal
WHERE TypeID = 3 AND Date1 BETWEEN @Data1 AND @Data2
GROUP BY Item_ID) Cgrn ON I.Item_ID = Cgrn.Item_ID
--DTN
LEFT JOIN ( SELECT Item_ID as 'Item_ID',
SUM(Qty) as 'Qty',
SUM(Amount) as 'Amount'
FROM #TempTableStoreBal
WHERE TypeID = 4 AND Date1 BETWEEN @Data1 AND @Data2
GROUP BY Item_ID) Cdtn ON I.Item_ID = Cdtn.Item_ID
--SR
LEFT JOIN ( SELECT Item_ID as 'Item_ID',
SUM(Qty) as 'Qty',
SUM(Amount) as 'Amount'
FROM #TempTableStoreBal
WHERE TypeID = 7 AND Date1 BETWEEN @Data1 AND @Data2
GROUP BY Item_ID) Csr ON I.Item_ID = Csr.Item_ID
--SRRN
LEFT JOIN ( SELECT Item_ID as 'Item_ID',
SUM(Qty) as 'Qty',
SUM(Amount) as 'Amount'
FROM #TempTableStoreBal
WHERE TypeID = 8 AND Date1 BETWEEN @Data1 AND @Data2
GROUP BY Item_ID) Csrrn ON I.Item_ID = Csrrn.Item_ID
--RN
LEFT JOIN ( SELECT Item_ID as 'Item_ID',
SUM(Qty) as 'Qty',
SUM(Amount) as 'Amount'
FROM #TempTableStoreBal
WHERE TypeID = 6 AND Date1 BETWEEN @Data1 AND @Data2
GROUP BY Item_ID) Crn ON I.Item_ID = Crn.Item_ID
--SCN
LEFT JOIN ( SELECT Item_ID as 'Item_ID',
SUM(Qty) as 'Qty',
SUM(Amount) as 'Amount'
FROM #TempTableStoreBal
WHERE TypeID = 12 AND Date1 BETWEEN @Data1 AND @Data2
GROUP BY Item_ID) Cscn ON I.Item_ID = Cscn.Item_ID
--RSCN
LEFT JOIN ( SELECT Item_ID as 'Item_ID',
SUM(Qty) as 'Qty',
SUM(Amount) as 'Amount'
FROM #TempTableStoreBal
WHERE TypeID = 13 AND Date1 BETWEEN @Data1 AND @Data2
GROUP BY Item_ID) Crscn ON I.Item_ID = Crscn.Item_ID
--MIR
LEFT JOIN ( SELECT Item_ID as 'Item_ID',
SUM(Qty) as 'Qty',
SUM(Amount) as 'Amount'
FROM #TempTableStoreBal
WHERE TypeID = 10 AND Date1 BETWEEN @Data1 AND @Data2
GROUP BY Item_ID) Cmir ON I.Item_ID = Cmir.Item_ID
--SVN
LEFT JOIN ( SELECT Item_ID as 'Item_ID',
SUM(Qty) as 'Qty',
SUM(Amount) as 'Amount'
FROM #TempTableStoreBal
WHERE TypeID = 9 AND Date1 BETWEEN @Data1 AND @Data2
GROUP BY Item_ID) Csvn ON I.Item_ID = Csvn.Item_ID
--GPN
LEFT JOIN ( SELECT Item_ID as 'Item_ID',
SUM(Qty) as 'Qty',
SUM(Amount) as 'Amount'
FROM #TempTableStoreBal
WHERE TypeID = 15 AND Date1 BETWEEN @Data1 AND @Data2
GROUP BY Item_ID) Cgpn ON I.Item_ID = Cgpn.Item_ID
--GPRN
LEFT JOIN ( SELECT Item_ID as 'Item_ID',
SUM(Qty) as 'Qty',
SUM(Amount) as 'Amount'
FROM #TempTableStoreBal
WHERE TypeID = 16 AND Date1 BETWEEN @Data1 AND @Data2
GROUP BY Item_ID) Cgprn ON I.Item_ID = Cgprn.Item_ID
--Old
LEFT JOIN ( SELECT Item_ID as 'Item_ID',
SUM(Qty) as 'Qty',
SUM(Amount) as 'Amount'
FROM #TempTableStoreBal
WHERE TypeID <> 11 AND Date1 < @Data1
GROUP BY Item_ID) C1 ON I.Item_ID = C1.Item_ID
--RIR
LEFT JOIN ( SELECT Item_ID as 'Item_ID',
SUM(Qty) as 'Qty',
SUM(Amount) as 'Amount'
FROM #TempTableStoreBal
WHERE TypeID = 11 --AND Date1 > @Data2
GROUP BY Item_ID) C4 ON I.Item_ID = C4.Item_ID
--SVN last date
LEFT JOIN ( SELECT Piv.Item_ID as 'Item_ID',
Piv.C_1 as 'SvnDate1'
FROM ( SELECT Svni.Item_ID as 'Item_ID',
Svn.Date1 as 'SvnDate',
'C_' + cast(row_number() over(partition by Svni.Item_ID order by Svni.Item_ID ASC, Svn.Date1 DESC) as varchar(10)) as columnsequence
FROM [SATHUTA-INVDB].[dbo].[tbl_SVN_Items] Svni
JOIN [SATHUTA-INVDB].[dbo].[tbl_SVN] Svn ON Svni.SVN_ID = Svn.SVN_ID
where Svni.Active = 1 and Svni.UnitRate is not null and Svn.Stors_ID = @Stors_ID)Temp
pivot( max(SvnDate)
for ColumnSequence in (C_1, C_2, C_3)) Piv) SvnDate ON I.Item_ID = SvnDate.Item_ID
------
WHERE FORMAT(I.Category_ID, 'd2') like '%' + @CategoryID1 + '%'
AND FORMAT(I.AssetTypeID, 'd1') like '%' + @AssetTypeID1 + '%'
AND FORMAT(I.ItemTypeID, 'd1') like '%' + @ItemTypeID1 + '%'
AND FORMAT(CASE WHEN C2.Qty is null THEN 1 WHEN C2.Qty is not null THEN 2 END, 'd1') like '%' + @TransID1 + '%'
AND (I.Item_Description LIKE '%' + @SearchText + '%' OR I.Item_Code LIKE '%' + @SearchText + '%')
ORDER BY C.Category_Description ASC, I.Item_Description ASC, I.Item_Code ASC
END
Please advise me for optimized.
samsmuthu
