Hello,

I have a script that takes longer times actually more than 5 minutes already the result did not display. Please any one can give a suggestion to optimize the below query. The first query is the original script it takes only 2-3 minutes to generate the report with more than 100,000 records then i nmodified the query I added  condition using OR operator and it takes more time.  Thank you in advance.

Note: This script has has 4 CTE's

--Old Script
;With CTE AS
(
	SELECT *
	FROM Table1
)
	SELECT
	    distinct
        * --just remove some fields
	FROM cte c
	JOIN INVENTDIM s2
	ON (s2.INVENTSERIALID  =c.Recovered_esn_no) and s2.DATAAREAID ='pnr'
	JOIN PRODTABLE s3
	ON  (s3.INVENTDIMID =s2.INVENTDIMID and  s3.PRODID = c.PRODID and   s3.DATAAREAID ='pnr') 
	    OR (s3.INVENTDIMID =s2.INVENTDIMID and  s3.DATAAREAID ='pnr' )



--Modified script
;With CTE AS
(
	SELECT *
	FROM Table1
)
	SELECT
	    distinct
        * --just remove some fields
	FROM cte c
	JOIN INVENTDIM s2
	ON (s2.INVENTSERIALID  =c.Recovered_esn_no) OR (s2.INVENTSERIALID  =c.ESN_Received COLLATE Chinese_Taiwan_Stroke_CI_AS)  and s2.DATAAREAID ='pnr'
	JOIN PRODTABLE s3
	ON  (s3.INVENTDIMID =s2.INVENTDIMID and  s3.PRODID = c.PRODID and   s3.DATAAREAID ='pnr') 
	    OR (s3.INVENTDIMID =s2.INVENTDIMID and  s3.DATAAREAID ='pnr' )

 

• Edited by Lenoj Monday, July 06, 2015 9:23 AM

Hi Uri, its a large table which use for transactions. I tried this approach using union all and its a little bit faster as compare with the old script.  any advice if this is good enough. thanks.

--Modified script
;With CTE AS
(
	SELECT *
	FROM Table1
)
	SELECT
	    distinct
        * --just remove some fields
	FROM cte c
	JOIN INVENTDIM s2
	ON (s2.INVENTSERIALID  =c.ESN_Received COLLATE Chinese_Taiwan_Stroke_CI_AS)  and s2.DATAAREAID ='pnr'
	JOIN PRODTABLE s3
	ON  (s3.INVENTDIMID =s2.INVENTDIMID and  s3.PRODID = c.PRODID and   s3.DATAAREAID ='pnr') 
	    OR (s3.INVENTDIMID =s2.INVENTDIMID and  s3.DATAAREAID ='pnr' )

UNION ALL

	SELECT
	    distinct
        * --just remove some fields
	FROM cte c
	JOIN INVENTDIM s2
	ON (s2.INVENTSERIALID  =c.Recovered_esn_no)   and s2.DATAAREAID ='pnr'
	JOIN PRODTABLE s3
	ON  (s3.INVENTDIMID =s2.INVENTDIMID and  s3.PRODID = c.PRODID and   s3.DATAAREAID ='pnr') 
	    OR (s3.INVENTDIMID =s2.INVENTDIMID and  s3.DATAAREAID ='pnr' )

• Edited by Lenoj Monday, July 06, 2015 10:09 AM

Hi Patrick,

Herewith is my whole script. kindly help to optimize this script if this can be shorten the codes. By the way, i plan to create a suumary report.  its possible to create a summary report using raw data in SSRS instead of summarizing/ grouping the data in script before transfering to SSRS. thank you in advance.

Declare @timezoneOffset int,@FromDateTime datetime, @ToDateTime datetime
Set @timezoneOffset=8
Set @FromDateTime='2015-01-01 12:00AM'
Set @ToDateTime ='2015-02-01 12:00AM'

----Main Query
;WITH Cte AS
(
	SELECT DISTINCT 
			m.esn_no AS ESN_Received,
			m.ESNReceipt_Date,
	        COALESCE(NULLIF(s1.RecoveredSerial,''),m.esn_no COLLATE Chinese_Taiwan_Stroke_CI_AS) Recovered_esn_no ,
			Supplier_Code, m.rma_type_id, m.Operation_type ,	
			m.Model_name ,
			m.modelcode,
			s1.SERIALIDUSED,
			s1.PRODID 
	FROM #BatchTemp m
	LEFT JOIN
		REPAIRRECOVEREDSERIAL  s1
		ON m.esn_no =s1.SERIALIDUSED 
), Cte2 AS
(
	--Getting Location, PO Created and PO Ended Date	
	SELECT
		c.ESN_Received 
		,c.ESNReceipt_Date 
		,c.Recovered_esn_no
		,c.SERIALIDUSED 
		,c.Supplier_Code
		,c.rma_type_id 
		,c.modelcode 
		,c.Model_name 
		,c.Operation_type 
		,c.PRODID
		,s2.WMSLOCATIONID 
		,s3.ASUPRODLINEID 
		,s3.INVENTDIMID 
		,DATEADD(HOUR,convert(INT,@timezoneOffset), s3.CREATEDDATETIME) AS PO_CREATEDDATE
		,DATEADD(HOUR,convert(INT,@timezoneOffset), s3.ASURAFDATETIME ) AS PO_ENDED
		,s3.STUPDATE 
		,s3.PRODSTATUS 
	    ,ROW_NUMBER() OVER(PARTITION BY c.ESN_Received  ORDER BY s3.ASURAFDATETIME DESC) RN
	FROM cte c
	JOIN INVENTSIM s2
	ON (s2.INVENTSERIALID  =c.ESN_Received COLLATE Chinese_Taiwan_Stroke_CI_AS) AND s2.DATAAREAID ='pnr'
	JOIN PRDTABLE s3
	ON  s3.INVENTDIMID =s2.INVENTDIMID and  s3.PRODID = c.PRODID AND s3.DATAAREAID ='pnr'

	UNION 

	SELECT
		c.ESN_Received 
		,c.ESNReceipt_Date 
		,c.Recovered_esn_no
		,c.SERIALIDUSED 
		,c.Supplier_Code
		,c.rma_type_id 
		,c.modelcode 
		,c.Model_name 
		,c.Operation_type 
		,c.PRODID
		,s2.WMSLOCATIONID 
		,s3.ASUPRODLINEID 
		,s3.INVENTDIMID 
		,DATEADD(HOUR,convert(INT,@timezoneOffset), s3.CREATEDDATETIME) AS PO_CREATEDDATE
		,DATEADD(HOUR,convert(INT,@timezoneOffset), s3.ASURAFDATETIME ) AS PO_ENDED
		,s3.STUPDATE 
		,s3.PRODSTATUS 
	    --,ROW_NUMBER() OVER(PARTITION BY c.ESN_Received  ORDER BY s3.ASURAFDATETIME DESC) RN
	FROM cte c
	JOIN INVENTSIM s2
	ON  (s2.INVENTSERIALID  =c.Recovered_esn_no)  AND s2.DATAAREAID ='pnr'  
	JOIN PRDTABLE s3
	ON  s3.INVENTDIMID =s2.INVENTDIMID AND s3.DATAAREAID ='pnr'

), cte3 as	    
(

	SELECT 
		 c2.ESN_Received 
		,c2.ESNReceipt_Date
		,c2.Recovered_esn_no 
		,c2.SERIALIDUSED 
		,c2.Supplier_Code 
		,c2.rma_type_id 
		,c2.modelcode 
		,c2.Model_name 
		,c2.Operation_type 
		,c2.INVENTDIMID 
		,c2.PRODID 
		,c2.WMSLOCATIONID 
		,c2.ASUPRODLINEID 
		,CASE WHEN CAST(c2.PO_ENDED AS DATE)='1900-01-01' OR CAST(c2.PO_ENDED AS DATE)='1900-01-01' THEN NULL ELSE c2.PO_ENDED END AS PO_ENDED
		,c2.PO_CREATEDDATE 
		,c2.PRODSTATUS 
		,c2.STUPDATE 
		,c2.RN
		,ROW_NUMBER() OVER(PARTITION BY c2.ESN_Received  ORDER BY c2.PO_CREATEDDATE   ASC) ROwNumber
	FROM Cte2 c2
	WHERE (c2.PO_ENDED >= @FromDateTime) OR (c2.PRODSTATUS=4)

), Cte4 AS
(
 --Breakdown Finish Good and Scrap
SELECT
     c3.ESN_Received  
    ,c3.Recovered_esn_no        AS Recovered_ESN
    ,c3.SERIALIDUSED 
    ,c3.WMSLOCATIONID 
    ,c3.PO_CREATEDDATE 
    ,c3.PO_ENDED 
    ,c3.STUPDATE 
    ,c3.PRODID 
    ,c3.PRODSTATUS
	,CASE WHEN LEFT(c3.PO_ENDED,3)='Jan' THEN CAST('01'+'-'+LEFT(c3.PO_ENDED,3) AS NVARCHAR(10))
         WHEN LEFT(c3.PO_ENDED,3)='Feb' THEN CAST('02'+'-'+LEFT(c3.PO_ENDED,3) AS NVARCHAR(10))
         WHEN LEFT(c3.PO_ENDED,3)='Mar' THEN CAST('03'+'-'+LEFT(c3.PO_ENDED,3) AS NVARCHAR(10))
         WHEN LEFT(c3.PO_ENDED,3)='Apr' THEN CAST('04'+'-'+LEFT(c3.PO_ENDED,3) AS NVARCHAR(10))
         WHEN LEFT(c3.PO_ENDED,3)='May' THEN CAST('05'+'-'+LEFT(c3.PO_ENDED,3) AS NVARCHAR(10))
         WHEN LEFT(c3.PO_ENDED,3)='Jun' THEN CONVERT(NVARCHAR(10),'06'+'-'+LEFT(c3.PO_ENDED,3))
         WHEN LEFT(c3.PO_ENDED,3)='Jul' THEN CAST('07'+'-'+LEFT(c3.PO_ENDED,3) AS NVARCHAR(10))
         WHEN LEFT(c3.PO_ENDED,3)='Aug' THEN CAST('08'+'-'+LEFT(c3.PO_ENDED,3) AS NVARCHAR(10))
         WHEN LEFT(c3.PO_ENDED,3)='Sep' THEN CAST('09'+'-'+LEFT(c3.PO_ENDED,3) AS NVARCHAR(10))
         WHEN LEFT(c3.PO_ENDED,3)='Oct' THEN CAST('10'+'-'+LEFT(c3.PO_ENDED,3) AS NVARCHAR(10))
         WHEN LEFT(c3.PO_ENDED,3)='Nov' THEN CAST('11'+'-'+LEFT(c3.PO_ENDED,3) AS NVARCHAR(10))
         WHEN LEFT(c3.PO_ENDED,3)='Dec' THEN CAST('12'+'-'+LEFT(c3.PO_ENDED,3)  AS NVARCHAR(10))
    END AS MONTHDESC_FG
	,c3.ROwNumber 
	,ShipDate = (
				Select TOP 1 audit_date
				FROM ESN_AUDIT bea 
				where c3.ESNReceipt_Date  < bea.audit_date   and bea.esn_no  = c3.Recovered_esn_no 
				and bea.operation_type =2 
				order by audit_date  asc
            )
FROM cte3 c3
WHERE  c3.ROwNumber =1
)
SELECT shp.*
FROM 
(
--Get overall total per model 
	SELECT
		 BU = CASE WHEN m.modelcode LIKE '%iP%' THEN 'IPH' ELSE 'Bas' END 
	    ,CORETYPE = CASE WHEN LEFT(m.Model_name,3) ='CR' THEN 'CR'
	                     WHEN LEFT(m.Model_name,2)='TS' THEN 'TS'
	                     WHEN LEFT(m.Model_name,3) ='FU' THEN 'FR' END
	    ,[STATUS]= CASE WHEN c4.PRODSTATUS=4 THEN 'WIP'
			            WHEN c4.PRODSTATUS=7  AND c4.WMSLOCATIONID ='FD' OR c4.WMSLOCATIONID ='PD_FD' THEN 'FD'
			            WHEN c4.PRODSTATUS=7  AND c4.WMSLOCATIONID ='Scrap' OR c4.WMSLOCATIONID ='PD_SOF' THEN 'Scrap'
		 ELSE 'WIP' END 	    
		,m.esn_no				    AS WH_ESNReceived
		,c4.Recovered_ESN        
		,c4.SERIALIDUSED 
		,m.ESNReceipt_Date	        AS ESN_Received_Date
		,m.rma_type_id              AS Recovered_RMA_Type
		,m.Model_name               AS WH_ModelName
		,m.modelcode                AS WH_ModelCode 
		,m.Operation_type 
		,m.Supplier_Code            AS WH_Supplier_Code
		,finphones.TotalESNReceived AS TotalESNReceived 
		,CASE WHEN LEFT(m.Model_name,3)='COR' OR LEFT(m.Model_name,2)='TS' THEN 'Normal'
		ELSE 'Special' END AS Category
		,DATEDIFF(dd,m.ESNReceipt_Date,c4.PO_CREATEDDATE ) AS TAT_ESNReceived_POCreated
		,DATEDIFF(dd,m.ESNReceipt_Date,c4.PO_ENDED ) AS TAT_ESNReceived_POEnded
		,DATEDIFF(dd,c4.PO_CREATEDDATE,c4.PO_ENDED ) AS TAT_POCreated_POEnded
		,CASE WHEN c4.PRODSTATUS=4 THEN '' ELSE c4.WMSLOCATIONID END AS WMSLOCATIONID
		,c4.PO_CREATEDDATE 
		,CASE WHEN c4.PO_ENDED  IS NULL  THEN NULL ELSE c4.PO_ENDED END AS PO_ENDED
		,c4.ShipDate		
		,c4.STUPDATE 
		,CASE WHEN c4.PRODSTATUS=4 and c4.PO_ENDED IS NULL THEN 'WIP With PO'
			  WHEN c4.PRODSTATUS=7 THEN 'Ended PO'
		 ELSE 'WIP No PO' END AS PRODSTATUS	  
		,c4.PRODID 
		,DATEDIFF(dd,m.ESNReceipt_Date,c4.PO_ENDED ) AS TAT_ESNReceive_ShpDate
		,CASE WHEN c4.PO_ENDED IS NULL AND c4.PRODID IS NULL THEN  DATEDIFF(dd,m.ESNReceipt_Date,GETDATE()) ELSE '' END AS AGING_DAYS
		,c4.MONTHDESC_FG
		,CASE WHEN c4.PO_ENDED IS NULL OR c4.PRODSTATUS=4 THEN  DATEDIFF(dd,c4.PO_CREATEDDATE ,GETDATE()) ELSE '' END AS TAT_OPEN_PO
	FROM Cte4 c4 
	RIGHT JOIN #BatchTemp m
	ON  c4.ESN_Received  = m.esn_no

	--Get Overall Total Received Per model 
	RIGHT JOIN  
	(
		SELECT pt.modelcode, COUNT(pt.modelcode) AS TotalESNReceived  
		FROM #BatchTemp pt  
		GROUP BY pt.modelcode 
	) finphones ON (finphones.modelcode  = m.modelcode)
) Shp
Order BY shp.WH_ModelCode

• Edited by Lenoj Tuesday, July 14, 2015 6:00 AM

Good day :-)

are you sure for " (s3.INVENTDIMID =s2.INVENTDIMID and  s3.PRODID = c.PRODID and   s3.DATAAREAID ='pnr') 
   OR (s3.INVENTDIMID =s2.INVENTDIMID and  s3.DATAAREAID ='pnr' )"

condition because it looks if you replace it with (s3.INVENTDIMID =s2.INVENTDIMID and  s3.DATAAREAID ='pnr' ) only then it will be working ok. and should also perform better

>> are you sure

Yes this is true :-)
"If (A and B and C) or (A and B)"
Is the same as
"if (A and B)

Since if (A and B and C) is true, then (A and B) is also true, and if  (A and B and C) is false but (A and B) is true, then you still get true, since you use "OR" and the second condition is true.

Hi Patrick,

I just create a datamart/table to help speed things up. the result of the query above put into one table. So, right now my main query is already faster. Anyway, thank you very much.

>> If this issue is close then please close the thread by marking one or more answers that gave you the solution (and you can vote for useful responses as well).

Hello

Is there an index on c.ESN_Received?  you are also translating the c.ESN_Received into a different collation; how much data is being translated here?

I dont have an index. I run the script month to date which have 120T plus records. so far its already working.  Thank you very much guys.