My stored procedure taking so much time to excection

Question

My stored procedure taking so much time to excection

Kothapalli Srinivasarao 131

I was shared my stored procedure, pls help to improve my sp performnace.

/****** Object: StoredProcedure [dbo].[stg_Purchase_Register_vs_GSTR2B_R8] Script Date: 12/14/2022 11:34:31 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create PROC [dbo].[stg_Purchase_Register_vs_GSTR2B_R8] AS
BEGIN TRY
SET NOCOUNT ON
/****** Object: StoredProcedure [dbo].[stg_Purchase_Register_vs_GSTR2B_R8] Script Date: 7/6/2022 3:46:30 PM ******/

	/* Temp Tables Creation Start */  
 IF OBJECT_ID('tempdb..#Temp') IS NOT NULL  
	BEGIN  
		DROP TABLE #Temp  
	END  

	IF OBJECT_ID('tempdb..#company') IS NOT NULL  
	BEGIN  
		DROP TABLE #company  
	END  


	IF OBJECT_ID('tempdb..#Purchase_Register_GSTR2B') IS NOT NULL  
	BEGIN  
		DROP TABLE #Purchase_Register_GSTR2B  
	END  

			IF OBJECT_ID('tempdb..#DiffPR_GSTNPR') IS NOT NULL  
	BEGIN  
		DROP TABLE #DiffPR_GSTNPR  
	END  

			IF OBJECT_ID('tempdb..#DiffPR_GSTN') IS NOT NULL  
	BEGIN  
		DROP TABLE #DiffPR_GSTN  
	END  

	/* Temp Tables Creation End */  
  
CREATE TABLE #purchase_register_gstr2b  
  (  
	 companyid         INT,  
	 gstin             VARCHAR(50),  
	 Gstregid          INT,  
	 cfs               VARCHAR(5),  
	 financialperiod   CHAR(10),  
	 [document count]  INT,  
	 recoresultstatus  VARCHAR(250),  
	 [taxable amount]  DECIMAL(18, 2),  
	 [taxable amount%] DECIMAL(5, 2),  
	 igst              DECIMAL(18, 2),  
	 [igst%]           DECIMAL(5, 2),  
	 cgst              DECIMAL(18, 2),  
	 [cgst%]           DECIMAL(5, 2),  
	 sgst              DECIMAL(18, 2),  
	 [sgst%]           DECIMAL(5, 2),  
	 cess              DECIMAL(18, 2),  
	 [cess%]           DECIMAL(5, 2),  
	 total             DECIMAL(18, 2),  
	 [total%]          DECIMAL(5, 2)  
  )  

CREATE TABLE #temp  
  (  
	 cfs              CHAR(5),  
	 recoresultstatus VARCHAR(250),  
	 financialperiod  VARCHAR(10),  
	 inum             VARCHAR(50),  
	 transactiontype  VARCHAR(250),  
	 txval            DECIMAL(18, 2),  
	 iamt             DECIMAL(18, 2),  
	 samt             DECIMAL(18, 2),  
	 camt             DECIMAL(18, 2),  
	 csamt            DECIMAL(18, 2),  
	 gstin            VARCHAR(30),  
	 Gstregid          INT,  
	 companyid        VARCHAR(50)  
  )  

INSERT INTO #temp  
			(cfs,  
			 recoresultstatus,  
			 financialperiod,  
			 inum,  
			 transactiontype,  
			 txval,  
			 iamt,  
			 samt,  
			 camt,  
			 csamt,  
			 gstin,  
			 Gstregid,  
			 companyid)  
SELECT Isnull(A.cfs,'N/A') cfs,  
		CASE  
		When A.recoresultstatus = 'Missing in GSTN' then 'Missing in GSTN'  
	   End As recoresultstatus,  
	   Replace(A.financialperiod, '-', '') Financialperiod,  
	   A.inum,  
	   A.transactiontype,  
	   Isnull(B.txval,0) txval,  
	   Isnull(B.igstamount,0) igstamount,  
	   Isnull(B.sgstamount,0) sgstamount,  
	   Isnull(B.cgstamount,0) cgstamount,  
	   Isnull(B.cessamount,0) cessamount,  
	   C.gstin,  
	   C.Gstregid,  
	   D.companyid  
FROM   dwh.gstr2cpinvoice A with(nolock)  
		   JOIN dwh.gstr2cpinvoiceitem B with(nolock)  
		 ON B.cpinvoiceid = A.cpinvoiceid  
	   INNER JOIN dwh.gstreg C with(nolock)  
			   ON A.gstregid = C.gstregid  
	   INNER JOIN dwh.company D with(nolock)  
			   ON C.companyid = D.companyid  
WHERE  A.recoresultstatus = 'Missing in GSTN'  
	   AND Isnull(A.isdeleted, 0) != 1  

INSERT INTO #temp  
			(cfs,  
			 recoresultstatus,  
			 financialperiod,  
			 inum,  
			 transactiontype,  
			 txval,  
			 iamt,  
			 samt,  
			 camt,  
			 csamt,  
			 gstin,  
			 Gstregid,  
			 companyid)  
SELECT Isnull(A.cfs,'N/A') cfs,  
	   case   
		   When A.RecoResultStatus ='Missing in PR' then 'Missing in PR'  
		   END as RecoResultStatus,  
	   Replace(A.return_period, '-', '') AS FinancialPeriod,  
	   Case   
		when documenttype in ('B2B','B2BA')      then cast(INUM	   as varchar)  
		when documenttype in ('CDN','CDNA','CDNR','CDNRA,')    then cast(nt_num  as varchar)  
		when documenttype in ('IMPG','IMPGSEZ')  then cast(BeNum   as varchar)  
		when documenttype in ('ISD','ISDA')	     then cast(DocNum  as varchar)  
		Else 'N/A'  
		end as [Inum],  
	   A.transactiontype,  
	   CASE  
		 WHEN A.documenttype IN( 'CDN', 'CDNA', 'CDNR', 'CDNRA',  
								 'B2B', 'B2BA' ) THEN Isnull(  
		 (SELECT isnull(Sum(txval),0)  
		  FROM   dwh.getcpinvoiceitem2b with(nolock)  
		  WHERE  cpinvoiceid = A.cpinvoiceid), 0)  
		 ELSE A.txval  
	   END                               AS TXVAL,  
	   CASE  
		 WHEN A.documenttype IN( 'CDN', 'CDNA', 'CDNR', 'CDNRA',  
								 'B2B', 'B2BA' ) THEN Isnull(  
		 (SELECT isnull(Sum(iamt),0)  
		  FROM   dwh.getcpinvoiceitem2b with(nolock)  
		  WHERE  cpinvoiceid = A.cpinvoiceid), 0)  
		 ELSE A.iamt  
	   END                               AS IAMT,  
	   CASE  
		 WHEN A.documenttype IN( 'CDN', 'CDNA', 'CDNR', 'CDNRA',  
								 'B2B', 'B2BA' ) THEN Isnull(  
		 (SELECT isnull(Sum(samt),0)  
		  FROM   dwh.getcpinvoiceitem2b with(nolock)  
		  WHERE  cpinvoiceid = A.cpinvoiceid), 0)  
		 ELSE A.samt  
	   END                               AS SAMT,  
	   CASE  
		 WHEN A.documenttype IN( 'CDN', 'CDNA', 'CDNR', 'CDNRA',  
								 'B2B', 'B2BA' ) THEN Isnull(  
		 (SELECT isnull(Sum(camt),0)  
		  FROM   dwh.getcpinvoiceitem2b with(nolock)  
		  WHERE  cpinvoiceid = A.cpinvoiceid), 0)  
		 ELSE A.camt  
	   END                               AS CAMT,  
	   CASE  
		 WHEN A.documenttype IN( 'CDN', 'CDNA', 'CDNR', 'CDNRA',  
								 'B2B', 'B2BA' ) THEN Isnull(  
		 (SELECT isnull(Sum(csamt),0)  
		  FROM   dwh.getcpinvoiceitem2b with(nolock)  
		  WHERE  cpinvoiceid = A.cpinvoiceid), 0)  
		 ELSE A.csamt  
	   END                               AS CSAMT,  
	   B.gstin,  
	   B.Gstregid,  
	   C.companyid  
FROM   dwh.getcpinvoice2b A with(nolock)  
	   INNER JOIN dwh.gstreg B with(nolock)  
			   ON A.gstregid = B.gstregid  
	   INNER JOIN dwh.company C with(nolock)  
			   ON B.companyid = C.companyid  
WHERE  Isnull(recoresultstatus, '') != ''  
	   AND Isnull(isdeleted, 0) != 1  
	   AND recoresultstatus = 'Missing in PR'   

INSERT INTO #temp  
			(cfs,  
			 recoresultstatus,  
			 financialperiod,  
			 inum,  
			 transactiontype,  
			 txval,  
			 iamt,  
			 samt,  
			 camt,  
			 csamt,  
			 gstin,  
			 Gstregid,  
			 companyid)  
SELECT Isnull(A.cfs,'N/A') cfs,  
	   case   
		when A.RecoResultStatus='Remote Match with Tolerance' then 'Remote Match with Tolerance'  
		when A.RecoResultStatus='Match' then 'Matched'  
		when A.RecoResultStatus='Mismatch' then 'Mismatch'  
		when A.RecoResultStatus='Match with Tolerance' then 'Matched with Tolerance'  
		when A.RecoResultStatus='Cross FY Match' then 'Cross FY Match'  
		when A.RecoResultStatus='Cross FY Probable Match' then 'Cross FY Probable Match'  
		when A.RecoResultStatus='Probable Match' then 'Probable Match'  
		when A.RecoResultStatus='Probable Mismatch' then 'Probable Mismatch'  
		when A.RecoResultStatus='Cross FY Mismatch' then 'Cross FY Mismatch'  
		when A.RecoResultStatus='Probable Match with Tolerance' then 'Probable Match with Tolerance'  
		when A.RecoResultStatus='Remote Match' then 'Remote Match'  
		when A.RecoResultStatus='Cross FY Match with Tolerance' then 'Cross FY Match with Tolerance'  
		when A.RecoResultStatus='ForceMatch' then 'ForceMatch'  
		when A.RecoResultStatus='Vendor PAN Match' then 'Vendor PAN Match'  
		when A.RecoResultStatus='Cross FY Probable Match with Tolerance' then 'Cross FY Probable Match with Tolerance'  
		when A.RecoResultStatus='Vendor PAN Probable Match with Tolerance' then 'Vendor PAN Probable Match with Tolerance'  
		END as RecoResultStatus,  
	   Replace(A.financialperiod, '-', '') AS FinancialPeriod,  
	   A.inum,  
	   A.transactiontype,  
	   Isnull(B.txval,0) txval,  
	   Isnull(B.igstamount,0) igstamount,  
	   Isnull(B.sgstamount,0) sgstamount,  
	   Isnull(B.cgstamount,0) cgstamount,  
	   Isnull(B.cessamount,0) cessamount,  
	   C.gstin,  
	   C.Gstregid,  
	   D.companyid  
FROM   dwh.gstr2cpinvoice A with(nolock)  
	   INNER JOIN dwh.gstr2cpinvoiceitem B with(nolock)  
			   ON B.cpinvoiceid = A.cpinvoiceid  
	   INNER JOIN dwh.gstreg C with(nolock)  
			   ON A.gstregid = C.gstregid  
	   INNER JOIN dwh.company D with(nolock)  
			   ON D.companyid = C.companyid  
WHERE  Isnull(recoresultstatus, '') != ''  
	   AND Isnull(isdeleted, 0) != 1  
	   AND recoresultstatus IN (  
			   'Remote Match with Tolerance', 'Match', 'Mismatch',  
			   'Match with Tolerance',  
								 'Cross FY Match', 'Cross FY Probable Match',  
									 'Probable Match','Probable Mismatch',  
														 'Cross FY Mismatch',  
								 'Probable Match with Tolerance', 'Remote Match'  
			   ,  
			   'Cross FY Match with Tolerance',  
									 'ForceMatch',  
								 'Vendor PAN Match',  
			   'Cross FY Probable Match with Tolerance'  
			   ,  
			   'Vendor PAN Probable Match with Tolerance' )  


SELECT companyid,  
	   gstin,  
	   Gstregid,  
	   Isnull(cfs, 'N/A') CFS,  
	   Isnull((SELECT Count(DISTINCT inum)  
			   FROM   #temp  
			   WHERE  companyid = a.companyid  
					  AND gstin = a.gstin  
					  AND Gstregid = a.Gstregid  
					  AND cfs = a.cfs  
					  AND financialperiod = a.financialperiod  
					  AND recoresultstatus = a.recoresultstatus  
					  ), 0)  
						  [Document Count],  
	   financialperiod,  
	   recoresultstatus,  
	   Isnull(( (SELECT Isnull(Sum(Isnull(txval, 0)),0)  
				 FROM   #temp  
				 WHERE  companyid = a.companyid  
						AND gstin = a.gstin  
						AND Gstregid = a.Gstregid  
						AND cfs = a.cfs  
						AND financialperiod = a.financialperiod  
						AND recoresultstatus = a.recoresultstatus  
						AND transactiontype IN(  
							'Purchases', 'Debit Note for Purchases'  
											  )) - (  
					   SELECT Isnull(Sum(Isnull(txval, 0)),0)  
						 FROM   #temp  
						 WHERE  companyid = a.companyid  
						AND gstin = a.gstin  
						AND Gstregid = a.Gstregid  
						AND cfs = a.cfs  
						AND financialperiod = a.financialperiod  
						AND recoresultstatus = a.recoresultstatus  
								AND  
				transactiontype = 'Credit Note for Purchases') ), 0  
	   )                  [Taxable Amount],  
	  Isnull(( (SELECT Isnull(Sum(Isnull(iamt, 0)),0)  
				 FROM   #temp  
				 WHERE  companyid = a.companyid  
						AND gstin = a.gstin  
						AND Gstregid = a.Gstregid  
						AND cfs = a.cfs  
						AND financialperiod = a.financialperiod  
						AND recoresultstatus = a.recoresultstatus  
						AND transactiontype IN(  
							'Purchases', 'Debit Note for Purchases'  
											  )) - (  
					   SELECT Isnull(Sum(Isnull(iamt, 0)),0)  
						 FROM   #temp  
						 WHERE  companyid = a.companyid  
								AND gstin = a.gstin  
								AND Gstregid = a.Gstregid  
								AND cfs = a.cfs  
								AND financialperiod = a.financialperiod  
								AND recoresultstatus = a.recoresultstatus  
								AND  
				transactiontype = 'Credit Note for Purchases') ), 0  
	   )                  IGST,  
	   Isnull(( (SELECT Isnull(Sum(Isnull(camt, 0)),0)  
				 FROM   #temp  
				 WHERE  companyid = a.companyid  
						AND gstin = a.gstin  
						AND Gstregid = a.Gstregid  
						AND cfs = a.cfs  
						AND financialperiod = a.financialperiod  
						AND recoresultstatus = a.recoresultstatus  
						AND transactiontype IN(  
							'Purchases', 'Debit Note for Purchases'  
											  )) - (  
					   SELECT Isnull(Sum(Isnull(camt, 0)),0)  
						 FROM   #temp  
						 WHERE  companyid = a.companyid  
								AND gstin = a.gstin  
								AND Gstregid = a.Gstregid  
								AND cfs = a.cfs  
								AND financialperiod = a.financialperiod  
								AND recoresultstatus = a.recoresultstatus  
								AND  
				transactiontype = 'Credit Note for Purchases') ), 0  
	   )                  CGST,  
	   Isnull(( (SELECT Isnull(Sum(Isnull(samt, 0)),0)  
				 FROM   #temp  
				 WHERE  companyid = a.companyid  
						AND gstin = a.gstin  
						AND Gstregid = a.Gstregid  
						AND cfs = a.cfs  
						AND financialperiod = a.financialperiod  
						AND recoresultstatus = a.recoresultstatus  
						AND transactiontype IN(  
							'Purchases', 'Debit Note for Purchases'  
											  )) - (  
					   SELECT Isnull(Sum(Isnull(samt, 0)),0)  
						 FROM   #temp  
						 WHERE  companyid = a.companyid  
								AND gstin = a.gstin  
								AND Gstregid = a.Gstregid  
								AND cfs = a.cfs  
								AND financialperiod = a.financialperiod  
								AND recoresultstatus = a.recoresultstatus  
								AND  
				transactiontype = 'Credit Note for Purchases') ), 0  
	   )                  SGST,  
	   Isnull(( (SELECT Isnull(Sum(Isnull(csamt, 0)),0)  
				 FROM   #temp  
				 WHERE  companyid = a.companyid  
						AND gstin = a.gstin  
						AND Gstregid = a.Gstregid  
						AND cfs = a.cfs  
						AND financialperiod = a.financialperiod  
						AND recoresultstatus = a.recoresultstatus  
						AND transactiontype IN(  
							'Purchases', 'Debit Note for Purchases'  
											  )) - (  
					   SELECT Isnull(Sum(Isnull(csamt, 0)),0)  
						 FROM   #temp  
						 WHERE  companyid = a.companyid  
								AND gstin = a.gstin  
								AND Gstregid = a.Gstregid  
								AND cfs = a.cfs  
								AND financialperiod = a.financialperiod  
								AND recoresultstatus = a.recoresultstatus  
								AND  
				transactiontype = 'Credit Note for Purchases') ), 0  
	   )                  CESS,  
	   Isnull(( (SELECT Isnull(Sum(Isnull(txval, 0) + Isnull(iamt, 0)  
							+ Isnull(camt, 0) + Isnull(samt, 0)  
							+ Isnull(csamt, 0)),0)  
				 FROM   #temp  
				 WHERE  companyid = a.companyid  
						AND gstin = a.gstin  
						AND Gstregid = a.Gstregid  
						AND cfs = a.cfs  
						AND financialperiod = a.financialperiod  
						AND recoresultstatus = a.recoresultstatus  
						AND transactiontype IN(  
							'Purchases', 'Debit Note for Purchases'  
											  )) -  
						 (SELECT Isnull(Sum(Isnull(txval, 0) + Isnull(iamt, 0)  
									 + Isnull(camt, 0) + Isnull(samt, 0)  
									 + Isnull(csamt, 0)),0)  
						  FROM   #temp  
						  WHERE  companyid = a.companyid  
								 AND gstin = a.gstin  
								 AND Gstregid = a.Gstregid  
								 AND cfs = a.cfs  
								 AND financialperiod = a.financialperiod  
								 AND recoresultstatus = a.recoresultstatus  
								 AND transactiontype =  
									 'Credit Note for Purchases') ),  
	   0)                 Total   
INTO   #company  
FROM   #temp A  
GROUP  BY companyid,  
		  gstin,  
		  Gstregid,  
		  cfs,  
		  financialperiod,  
		  recoresultstatus  

INSERT INTO #purchase_register_gstr2b  
			(companyid,  
			 gstin,  
			 Gstregid,  
			 financialperiod,  
			 recoresultstatus,  
			 [taxable amount],  
			 igst,  
			 cgst,  
			 sgst,  
			 cess,  
			 total)  
SELECT companyid,  
	   gstin,  
	   Gstregid,  
	   financialperiod,  
	   'Total of Matching Status' [RecoResultStatus],  
	   Sum(Isnull([taxable amount], 0)) [Taxable Amount],  
	   Sum(Isnull(igst, 0))             IGST,  
	   Sum(Isnull(cgst, 0))             CGST,  
	   Sum(Isnull(sgst, 0))             SGST,  
	   Sum(Isnull(cess, 0))             CESS,  
	   Sum(Isnull(total, 0))            Total  
FROM   #company  
GROUP  BY companyid,  
		  gstin,  
		  Gstregid,  
		  financialperiod  

INSERT INTO #purchase_register_gstr2b  
			(companyid,  
			 gstin,  
			 Gstregid,  
			 cfs,  
			 [document count],  
			 financialperiod,  
			 recoresultstatus,  
			 [taxable amount],  
			 [taxable amount%],  
			 igst,  
			 [igst%],  
			 cgst,  
			 [cgst%],  
			 sgst,  
			 [sgst%],  
			 cess,  
			 [cess%],  
			 total,  
			 [total%])  
SELECT companyid,  
	   gstin,  
	   Gstregid,  
	   cfs,  
	   [document count],  
	   financialperiod,  
	   [recoresultstatus],  
	   [taxable amount],  
	   CASE  
		 WHEN [taxable amount] IS NULL  
			   OR [taxable amount] = 0 THEN 0  
		 ELSE [taxable amount] * 100 / Sum([total])  
										 OVER (  
										   partition BY companyid, gstin, cfs,  
										 [recoresultstatus], financialperiod  
										   ORDER BY financialperiod)  
	   END AS [Taxable Amount%],  
	   igst,  
	   CASE  
		 WHEN igst IS NULL  
			   OR igst = 0 THEN 0  
		 ELSE igst * 100 / Sum([total])  
							 OVER (  
							   partition BY companyid, gstin, cfs,  
							 [recoresultstatus],  
							 financialperiod  
							   ORDER BY financialperiod)  
	   END AS [IGST%],  
	   cgst,  
	   CASE  
		 WHEN cgst IS NULL  
			   OR cgst = 0 THEN 0  
		 ELSE cgst * 100 / Sum([total])  
							 OVER (  
							   partition BY companyid, gstin, cfs,  
							 [recoresultstatus],  
							 financialperiod  
							   ORDER BY financialperiod)  
	   END AS [CGST%],  
	   sgst,  
	   CASE  
		 WHEN sgst IS NULL  
			   OR sgst = 0 THEN 0  
		 ELSE sgst * 100 / Sum([total])  
							 OVER (  
							   partition BY companyid, gstin, cfs,  
							 [recoresultstatus],  
							 financialperiod  
							   ORDER BY financialperiod)  
	   END AS [SGST%],  
	   cess,  
	   CASE  
		 WHEN cess IS NULL  
			   OR cess = 0 THEN 0  
		 ELSE cess * 100 / Sum([total])  
							 OVER (  
							   partition BY companyid, gstin, cfs,  
							 [recoresultstatus],  
							 financialperiod  
							   ORDER BY financialperiod)  
	   END AS [CESS%],  
	   total,  
	   CASE  
		 WHEN total IS NULL  
			   OR total = 0 THEN 0  
		 ELSE ( total * 100 ) / Sum([total])  
								  OVER (  
									partition BY companyid, gstin, cfs,  
								  [recoresultstatus],  
								  financialperiod  
									ORDER BY financialperiod)  
	   END AS [Total%]  
FROM   #company  
GROUP  BY companyid,  
		  gstin,  
		  Gstregid,  
		  cfs,  
		  financialperiod,  
		  [taxable amount],  
		  [recoresultstatus],  
		  igst,  
		  cgst,  
		  sgst,  
		  cess,  
		  total,  
		  [document count]  

INSERT INTO #purchase_register_gstr2b  
			(companyid,  
			 gstin,  
			 Gstregid,  
			 financialperiod,  
			 recoresultstatus,  
			 [taxable amount],  
			 igst,  
			 cgst,  
			 sgst,  
			 cess,  
			 total)  
SELECT companyid,  
	   gstin,  
	   Gstregid,  
	   financialperiod,  
	   'Total as per PR'                [RecoResultStatus],  
	   Sum(Isnull([taxable amount], 0)) [Taxable Amount],  
	   Sum(Isnull(igst, 0))             IGST,  
	   Sum(Isnull(cgst, 0))             CGST,  
	   Sum(Isnull(sgst, 0))             SGST,  
	   Sum(Isnull(cess, 0))             CESS,  
	   Sum(Isnull(total, 0))            Total  
FROM   #purchase_register_gstr2b  
WHERE  recoresultstatus not in ('Missing in PR','Total of Matching Status')  
GROUP  BY companyid,  
		  gstin,  
		  Gstregid,  
		  financialperiod  

INSERT INTO #purchase_register_gstr2b  
			(companyid,  
			 gstin,  
			 Gstregid,  
			 financialperiod,  
			 recoresultstatus,  
			 [taxable amount],  
			 igst,  
			 cgst,  
			 sgst,  
			 cess,  
			 total)  
SELECT companyid,  
	   gstin,  
	   Gstregid,  
	   financialperiod,  
	   'Total as per GSTN'              [RecoResultStatus],  
	   Sum(Isnull([taxable amount], 0)) [Taxable Amount],  
	   Sum(Isnull(igst, 0))             IGST,  
	   Sum(Isnull(cgst, 0))             CGST,  
	   Sum(Isnull(sgst, 0))             SGST,  
	   Sum(Isnull(cess, 0))             CESS,  
	   Sum(Isnull(total, 0))            Total  
FROM   #purchase_register_gstr2b  
WHERE  recoresultstatus not in ('Missing in GSTN','Total as per PR','Total of Matching Status')  
GROUP  BY companyid,  
		  gstin,  
		  Gstregid,  
		  financialperiod  

SELECT companyid,  
	   gstin,  
	   Gstregid,  
	   financialperiod,  
	   [document count],  
	   recoresultstatus,  
	   [taxable amount],  
	   igst,  
	   cgst,  
	   sgst,  
	   cess,  
	   total  
INTO   #diffpr_gstnpr  
FROM   #purchase_register_gstr2b  
WHERE  recoresultstatus = 'Total as per PR'  

SELECT companyid,  
	   gstin,  
	   Gstregid,  
	   financialperiod,  
	   [document count],  
	   recoresultstatus,  
	   [taxable amount],  
	   igst,  
	   cgst,  
	   sgst,  
	   cess,  
	   total  
INTO   #diffpr_gstn  
FROM   #purchase_register_gstr2b  
WHERE  recoresultstatus = 'Total as per GSTN'  

INSERT INTO #purchase_register_gstr2b  
			(companyid,  
			 gstin,  
			 Gstregid,  
			 financialperiod,  
			 recoresultstatus,  
			 [taxable amount],  
			 igst,  
			 cgst,  
			 sgst,  
			 cess,  
			 total)  
SELECT A.companyid,  
	   A.gstin,  
	   A.Gstregid,  
	   A.financialperiod,  
	   'Difference(PR-GSTN)'                   RecoResultStatus,  
	   A.[taxable amount] - B.[taxable amount] [Taxable Amount],  
	   A.igst - B.igst                         iGST,  
	   A.cgst - B.cgst                         CGST,  
	   A.sgst - B.sgst                         SGST,  
	   A.cess - B.cess                         CESS,  
	   A.total - B.total                       Total  
FROM   #diffpr_gstnpr A  
	   JOIN #diffpr_gstn B  
		 ON A.companyid = B.companyid  
			AND A.gstin = B.gstin  
			AND Isnull(A.financialperiod, '') = Isnull(B.financialperiod, '')  



	Delete A  
	from dwh.Fct_Purchase_Register_GSTR2B_R8 A  
	   INNER JOIN #Purchase_Register_GSTR2B B  
			ON ISNULL(A.CompanyID, '') = ISNULL(B.CompanyID, '')  
			   AND ISNULL(A.GSTIN, '') = ISNULL(B.GSTIN, '')  
             

             
	INSERT INTO dwh.Fct_Purchase_Register_GSTR2B_R8  
	(  
		CompanyID,  
		GSTIN,  
		Gstregid,  
		CFS,  
		idt,  
		FinancialPeriod,  
		[Document Count],  
		RecoResultStatus,  
		[Taxable Amount],  
		[Taxable Amount%],  
		iGST,  
		[iGST%],  
		CGST,  
		[CGST%],  
		SGST,  
		[SGST%],  
		CESS,  
		[CESS%],  
		Total,  
		[Total%],  
		Financialmonth,  
		Financialyear  
	)  
	SELECT CompanyID,  
		   GSTIN,  
		   Gstregid,  
		   CFS,  
		   '' as Idt,  
		   FinancialPeriod,  
		   [Document Count],  
		   RecoResultStatus,  
		   [Taxable Amount],  
		   [Taxable Amount%],  
		   iGST,  
		   [iGST%],  
		   CGST,  
		   [CGST%],  
		   SGST,  
		   [SGST%],  
		   CESS,  
		   [CESS%],  
		   Total,  
		   [Total%],  
		   left(ltrim(FinancialPeriod),2)  Financialmonth,  
		   right(rtrim(FinancialPeriod),4) Financialyear  
	FROM #Purchase_Register_GSTR2B  


	/* Deleting Temp Tables */  
	IF OBJECT_ID('tempdb..#Temp') IS NOT NULL  
	BEGIN  
		DROP TABLE #Temp  
	END  

	IF OBJECT_ID('tempdb..#company') IS NOT NULL  
	BEGIN  
		DROP TABLE #company  
	END  

	  IF OBJECT_ID('tempdb..#Purchase_Register_GSTR2B') IS NOT NULL  
	BEGIN  
		DROP TABLE #Purchase_Register_GSTR2B  
	END  
			IF OBJECT_ID('tempdb..#DiffPR_GSTNPR') IS NOT NULL  
	BEGIN  
		DROP TABLE #DiffPR_GSTNPR  
	END  

			IF OBJECT_ID('tempdb..#DiffPR_GSTN') IS NOT NULL  
	BEGIN  
		DROP TABLE #DiffPR_GSTN  
	END  


END TRY  
BEGIN CATCH  
	/* Temp Tables Creation Start */  
	IF OBJECT_ID('tempdb..#Temp') IS NOT NULL  
	BEGIN  
		DROP TABLE #Temp  
	END  

	IF OBJECT_ID('tempdb..#company') IS NOT NULL  
	BEGIN  
		DROP TABLE #company  
	END  

	  IF OBJECT_ID('tempdb..#Purchase_Register_GSTR2B') IS NOT NULL  
	BEGIN  
		DROP TABLE #Purchase_Register_GSTR2B  
	END  
			IF OBJECT_ID('tempdb..#DiffPR_GSTNPR') IS NOT NULL  
	BEGIN  
		DROP TABLE #DiffPR_GSTNPR  
	END  

			IF OBJECT_ID('tempdb..#DiffPR_GSTN') IS NOT NULL  
	BEGIN  
		DROP TABLE #DiffPR_GSTN  
	END  




	DECLARE @ERROR_NUMBER [nvarchar](50),  
			@ERROR_MESSAGE [nvarchar](4000),  
			@ERROR_PROCEDURE [nvarchar](100),  
			@ERROR_STATE [int],  
			@ERROR_SEVERITY [int],  
			@GETDATE [DATETIME2]  
	set @ERROR_NUMBER = ERROR_NUMBER()  
	set @ERROR_MESSAGE = ERROR_MESSAGE()  
	set @ERROR_PROCEDURE = 'stg_Purchase_Register_vs_GSTR2B_R8'  
	set @ERROR_STATE = ERROR_STATE()  
	set @ERROR_SEVERITY = ERROR_SEVERITY()  
	set @GETDATE = GETDATE()  
	INSERT INTO dwh.errorlogtbl  
	(  
		ErrorNumber,  
		ErrorDescription,  
		ErrorProcedure,  
		ErrorState,  
		ErrorSeverity,  
		ErrorTime  
	)  
	Values  
	(@ERROR_NUMBER, @ERROR_MESSAGE, @ERROR_PROCEDURE, @ERROR_STATE, @ERROR_SEVERITY, @GETDATE)  
END CATCH

GO

Erland Sommarskog 119.9K Reputation points MVP

2022-12-14T22:27:23.663+00:00
Since you have tagged this with azure-synapse-analytics, I assume that this runs in Synapse and not regular SQL Server? Can you share the output of "SELECT @@version"?

There is no one here that can point out there the problem might be in those 800 lines of code. But we can give you some advice on how to troubleshoot. The first thing is to find out which statement(s) that are slow. Since there are that very many statements, you do something like this:

In the beginning of the procedure, you say

DECLARE @d datetime2 = sysdatetime()

And between each statement, you have something like:

PRINT concat('Query #1 took ', datediff(ms, @d, systdatetime()), ' ms.') SELECT @d = sysdatetime()

Once you have identified the slow query, you need to look at the execution plan. If you want help with it, you can share it on http://www.pastetheplan.com/ and post the link here. We may still ask for followup questions.
Erland Sommarskog 119.9K Reputation points MVP

2022-12-14T22:30:37.273+00:00

I also like to make two unrelated comments on your code. I see that you have WITH (NOLOCK) in several places. This is very dangerous, because not only may you read uncommitted and inconsistent data, but you can also fail to read committed data, which can lead to transient data errors that are very difficult to repeat.

Also, there is no need to drop the temp tables in the CATCH block or at the end of the procedure. A temp table created in a stored procedure is dropped automatically when the procedure exits.
Kothapalli Srinivasarao 131 Reputation points

2022-12-15T04:12:13.68+00:00

Query #1 took 96 ms.
Query #2 took 450 ms.
Query #3 took 270 ms.

(553060 rows affected)
Query #4 took 11296 ms.

(3559632 rows affected)
Query #5 took 418910 ms.

(2220457 rows affected)
Query #6 took 25917 ms.

(11987 rows affected)
Query #7 took 139843 ms.

(2674 rows affected)
Query #8 took 1833 ms.

(11987 rows affected)
Query #9 took 2230 ms.

(1501 rows affected)
Query #10 took 2197 ms.

(2607 rows affected)
Query #11 took 1993 ms.

(1501 rows affected)
Query #12 took 1370 ms.

(2607 rows affected)
Query #13 took 1596 ms.

(1434 rows affected)
Query #14 took 2437 ms.

(20188 rows affected)
Query #15 took 810 ms.

(20203 rows affected)
Query #16 took 1456 ms.
Query #17 took 1100 ms.
Erland Sommarskog 119.9K Reputation points MVP

2022-12-15T22:01:17.623+00:00

Looks like you should work with Query #5 and Query #7.

As I said, we need at least the actual execution plan to be able help you with these queries. There is not much we can do with only the query text. And, no, I am not going to go through your code to find out which these queries are.

You need to understand that when you ask people to use their free time to help you, you need to make an effort yourself and help us to help you.

2 answers

Your answer

Erland Sommarskog 119.9K Reputation points MVP

2022-12-14T22:27:23.663+00:00

Since you have tagged this with azure-synapse-analytics, I assume that this runs in Synapse and not regular SQL Server? Can you share the output of "SELECT @@version"?

There is no one here that can point out there the problem might be in those 800 lines of code. But we can give you some advice on how to troubleshoot. The first thing is to find out which statement(s) that are slow. Since there are that very many statements, you do something like this:

In the beginning of the procedure, you say

DECLARE @d datetime2 = sysdatetime()

And between each statement, you have something like:

PRINT concat('Query #1 took ', datediff(ms, @d, systdatetime()), ' ms.') SELECT @d = sysdatetime()

Once you have identified the slow query, you need to look at the execution plan. If you want help with it, you can share it on http://www.pastetheplan.com/ and post the link here. We may still ask for followup questions.
Erland Sommarskog 119.9K Reputation points MVP

2022-12-14T22:30:37.273+00:00

I also like to make two unrelated comments on your code. I see that you have WITH (NOLOCK) in several places. This is very dangerous, because not only may you read uncommitted and inconsistent data, but you can also fail to read committed data, which can lead to transient data errors that are very difficult to repeat.

Also, there is no need to drop the temp tables in the CATCH block or at the end of the procedure. A temp table created in a stored procedure is dropped automatically when the procedure exits.
Kothapalli Srinivasarao 131 Reputation points

2022-12-15T04:12:13.68+00:00

Query #1 took 96 ms.
Query #2 took 450 ms.
Query #3 took 270 ms.

(553060 rows affected)
Query #4 took 11296 ms.

(3559632 rows affected)
Query #5 took 418910 ms.

(2220457 rows affected)
Query #6 took 25917 ms.

(11987 rows affected)
Query #7 took 139843 ms.

(2674 rows affected)
Query #8 took 1833 ms.

(11987 rows affected)
Query #9 took 2230 ms.

(1501 rows affected)
Query #10 took 2197 ms.

(2607 rows affected)
Query #11 took 1993 ms.

(1501 rows affected)
Query #12 took 1370 ms.

(2607 rows affected)
Query #13 took 1596 ms.

(1434 rows affected)
Query #14 took 2437 ms.

(20188 rows affected)
Query #15 took 810 ms.

(20203 rows affected)
Query #16 took 1456 ms.
Query #17 took 1100 ms.
Erland Sommarskog 119.9K Reputation points MVP

2022-12-15T22:01:17.623+00:00

Looks like you should work with Query #5 and Query #7.

As I said, we need at least the actual execution plan to be able help you with these queries. There is not much we can do with only the query text. And, no, I am not going to go through your code to find out which these queries are.

You need to understand that when you ask people to use their free time to help you, you need to make an effort yourself and help us to help you.

Answer 1

PercyTang-MSFT 12,511 Microsoft External Staff

Hi @Anonymous

1.When creating a temporary table, if you insert a large amount of data at one time, you can use select into the table instead of creating the table to avoid logging and improve speed. If the amount of data is not large, in order to ease the resources of the system tables, we recommend that you create a table first, and then insert it.
For example, the first time you can use select into to create #temp and insert data, and then insert data into the created #temp if you want to continue inserting data in #temp.

2.If the data volume of the temporary table is large and indexes need to be established, the process of creating the temporary table and indexing should be placed in a separate sub-stored procedure to ensure that the system can use the index of the temporary table well.

3.If temporary tables are used, be sure to explicitly delete all temporary tables at the end of the stored procedure, truncate the table first, and then drop the table, so as to avoid a long lock on the system table.

Best regards,
Percy Tang

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

James Longstreet 26 Reputation points

2022-12-16T16:00:51.13+00:00

Possibly take a look at wait states as well. When processing threads are busy, they can't accept new threads until the older ones complete and thus they queue and form wait states. Understanding how they work may be a good way to re-architect your solution.

https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-wait-stats-transact-sql?view=sql-server-ver16
Bhargava-MSFT 31,226 Reputation points Microsoft Employee

2022-12-20T22:52:24.593+00:00

Hi @Anonymous

I am checking to see if the above answers are helpful here.

If this answers your question, please consider accepting the answer by hitting the Accept answer button, as it helps the community.

Answer 2

jose de jesus 141

You are extensively using temporary table which are slowing your performance due to contention on Tempdb
I suggest you use a physical staging table rather than using temporary tables

The current design will blow up the server if these temporary tables are loaded with large volume of data

Temporary tables are not optimized for data transformation purposes in your ETL
physical staging tables are the way to go

consider replacing this with physical staging tables
#Temp
#company
#Purchase_Register_GSTR2B
#DiffPR_GSTNPR

Make sure to add index to your staging tables

Erland Sommarskog 119.9K Reputation points MVP

2022-12-15T22:06:35.35+00:00

You are extensively using temporary table which are slowing your performance due to contention on Tempdb

Sorry, but this is plain wrong. There is absolutely nothing wrong with the use of temp tables in Srinivasa's procedure. I've written procedures with many more temp tables in them.

You don't get contention in tempdb just because you have a stored procedure with lots of temp tables. But if many users are running that procedure simultaneously, you may get a bit of a problem. But as I understand it, Srinivasa gets the problem when he is running the procedure all alone.

Share via

My stored procedure taking so much time to excection

2 answers

Your answer