SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 What am I doing wrong
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

regelos
Starting Member

USA
13 Posts

Posted - 03/27/2013 :  12:25:48  Show Profile  Reply with Quote
I have this query and the pieces (master query with no sub query's and sub query's) run instantly without an issue but put them together like below and it takes FOREVER to run (canceled after 25 min) and slows processing down on the SQL server pretty bad.

Any help would be appreciated and thanks in advance.

SELECT
SUBSTRING(OD.PRODUCT_CODE, 4, 2)AS PRODUCT_SUB,
OD.PRODUCT_CODE,
OD.SHIP_MASTER_CUSTOMER_ID,
C.LAST_NAME,
C.FIRST_NAME,
MAS.SHIP_MASTER_CUSTOMER_ID,
ASOC.MASTER_CUSTOMER_ID AS ASOC_MASTER_CUSTOMER_ID

FROM ORDER_DETAIL OD
LEFT JOIN (SELECT DISTINCT OD3.SHIP_MASTER_CUSTOMER_ID
FROM ORDER_DETAIL OD3
INNER JOIN PRODUCT P3 ON (OD3.PRODUCT_ID = P3.PRODUCT_ID)
WHERE OD3.SUBSYSTEM = 'MBR'
AND P3.PRODUCT_CLASS_CODE = 'DUES'
AND OD3.LINE_STATUS_CODE = 'A'
AND OD3.PRODUCT_CODE NOT LIKE '%JOIN%'
AND OD3.CYCLE_END_DATE >= DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
AND OD3.CYCLE_BEGIN_DATE <=DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))) AS MAS ON (OD.SHIP_MASTER_CUSTOMER_ID = MAS.SHIP_MASTER_CUSTOMER_ID)
LEFT JOIN (SELECT DISTINCT OMA.MASTER_CUSTOMER_ID
FROM ORDER_DETAIL OD1
INNER JOIN ORDER_MBR_ASSOCIATE OMA ON (OD1.ORDER_NO = OMA.ORDER_NO AND OD1.ORDER_LINE_NO = OMA.ORDER_LINE_NO)
INNER JOIN PRODUCT P1 ON (OD1.PRODUCT_ID = P1.PRODUCT_ID)
WHERE OD1.SUBSYSTEM = 'MBR'
AND P1.PRODUCT_CLASS_CODE = 'DUES'
AND OD1.LINE_STATUS_CODE = 'A'
AND OD1.PRODUCT_CODE NOT LIKE '%JOIN%'
AND OD1.CYCLE_END_DATE >= DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
AND OD1.CYCLE_BEGIN_DATE <= DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))) AS ASOC ON (OD.SHIP_MASTER_CUSTOMER_ID = ASOC.SHIP_MASTER_CUSTOMER_ID)
INNER JOIN CUSTOMER C ON (OD.SHIP_MASTER_CUSTOMER_ID = C.MASTER_CUSTOMER_ID)

WHERE (OD.PRODUCT_CODE LIKE '%CC%' OR OD.PRODUCT_CODE LIKE '%PS%' OR OD.PRODUCT_CODE LIKE '%LC%')

AND OD.CYCLE_END_DATE >= DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
AND OD.CYCLE_BEGIN_DATE <=DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
AND OD.LINE_STATUS_CODE <> 'C'
AND OD.PRODUCT_CODE NOT LIKE '%_FEE'
AND OD.SUBSYSTEM = 'MTG'

TG
Flowing Fount of Yak Knowledge

USA
5914 Posts

Posted - 03/27/2013 :  14:36:19  Show Profile  Reply with Quote
see if these changes make any difference:

SELECT SUBSTRING(OD.PRODUCT_CODE, 4, 2)AS PRODUCT_SUB,
       OD.PRODUCT_CODE,
       OD.SHIP_MASTER_CUSTOMER_ID,
       C.LAST_NAME,
       C.FIRST_NAME,
       MAS.SHIP_MASTER_CUSTOMER_ID,
       ASOC.MASTER_CUSTOMER_ID AS ASOC_MASTER_CUSTOMER_ID

FROM   ORDER_DETAIL OD

INNER JOIN CUSTOMER C 
       ON (OD.SHIP_MASTER_CUSTOMER_ID = C.MASTER_CUSTOMER_ID)

LEFT JOIN (
       SELECT OD3.SHIP_MASTER_CUSTOMER_ID
       FROM ORDER_DETAIL OD3
       INNER JOIN PRODUCT P3 
              ON (OD3.PRODUCT_ID = P3.PRODUCT_ID)
       WHERE OD3.SUBSYSTEM = 'MBR'
       AND P3.PRODUCT_CLASS_CODE = 'DUES'
       AND OD3.LINE_STATUS_CODE = 'A'
       AND OD3.PRODUCT_CODE NOT LIKE '%JOIN%'
       AND OD3.CYCLE_END_DATE >= DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
       AND OD3.CYCLE_BEGIN_DATE <=DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
       group by OD3.SHIP_MASTER_CUSTOMER_ID
       ) AS MAS 
              ON (OD.SHIP_MASTER_CUSTOMER_ID = MAS.SHIP_MASTER_CUSTOMER_ID)
LEFT JOIN (
       SELECT OMA.MASTER_CUSTOMER_ID
       FROM ORDER_DETAIL OD1
       INNER JOIN ORDER_MBR_ASSOCIATE OMA 
              ON (OD1.ORDER_NO = OMA.ORDER_NO AND OD1.ORDER_LINE_NO = OMA.ORDER_LINE_NO)
       INNER JOIN PRODUCT P1 
              ON (OD1.PRODUCT_ID = P1.PRODUCT_ID)
       WHERE OD1.SUBSYSTEM = 'MBR'
       AND P1.PRODUCT_CLASS_CODE = 'DUES'
       AND OD1.LINE_STATUS_CODE = 'A'
       AND OD1.PRODUCT_CODE NOT LIKE '%JOIN%'
       AND OD1.CYCLE_END_DATE >= DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
       AND OD1.CYCLE_BEGIN_DATE <= DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
       group by OMA.MASTER_CUSTOMER_ID
       ) AS ASOC 
              ON (OD.SHIP_MASTER_CUSTOMER_ID = ASOC.SHIP_MASTER_CUSTOMER_ID)

WHERE (OD.PRODUCT_CODE LIKE '%CC%' OR OD.PRODUCT_CODE LIKE '%PS%' OR OD.PRODUCT_CODE LIKE '%LC%')
AND OD.CYCLE_END_DATE >= DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
AND OD.CYCLE_BEGIN_DATE <=DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
AND OD.LINE_STATUS_CODE <> 'C'
AND OD.PRODUCT_CODE NOT LIKE '%_FEE'
AND OD.SUBSYSTEM = 'MTG'


Be One with the Optimizer
TG
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4347 Posts

Posted - 03/27/2013 :  18:22:41  Show Profile  Reply with Quote
You have a lot of predicates that are not sargable. Is that the issue? Impossible to tell from the information provided.

How big are the tables? What indexes do they have? What does the execution plan tell you? etc..

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000