Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
regelos
Starting Member
13 Posts |
Posted - 2013-03-27 : 12:25:48
|
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_IDFROM 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
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-03-27 : 14:36:19
|
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_IDFROM ORDER_DETAIL ODINNER 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 OptimizerTG |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-03-27 : 18:22:41
|
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.. |
|
|
|
|
|
|
|