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 21 hours 43 minutes ago

Yes this way will force SQL Server to work harder , how big are the tables? Perhaps you can keep some data into a temporary table and operate with it later on...

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 20 hours 58 minutes ago

In your script what point having a cte? You can reference directly to table1,no?

DISTINCT also takes resources, is that possible to remove it, if not looking at the script I would remove DISTINCT clause but use UNION instead of UNION ALL  to remove 'duplicates'

This I don't understand:

     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' )

Isn't this the same as:

      JOIN PRODTABLE s3
      ON  (s3.INVENTDIMID =s2.INVENTDIMID and  s3.DATAAREAID ='pnr' )

OR conditions often makes things difficult for the optimizer, and it often helps to replace them with UNION (or UNION ALL, if you know for sure that the conditions are mutually exclusive). But in this case, I don't see any reason for the OR at all.

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

    INNER 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' 
         )

Your two predicates here are the same, with the exception that on one side of the OR, you must also match on PRODID. Given this, what's the point of having that part of the predicate there?

You would return the same result with:

    INNER JOIN PRODTABLE s3
      ON s3.INVENTDIMID =s2.INVENTDIMID 
      AND s3.DATAAREAID ='pnr' 
      

There are some records that doesnt have the PRODID while some has PRODID that i also need to consider. Its ok if I will  post the whole script so that you can help me to optimize. thank you for the replies. 

	JOIN PRODTABLE s3
	ON  (s3.INVENTDIMID =s2.INVENTDIMID and  s3.PRODID = c.PRODID)
	    OR (s3.INVENTDIMID =s2.INVENTDIMID) AND  s3.DATAAREAID ='pnr'