January 27, 2012 at 3:39 pm
I have a query and it is taking 1 hour and 30 mins to execute
what can I do with this query to make it faster
Execution plan is also attached
Table definitions
name rows reserved data index_size unused
REF_TNT_BY_ZIP 1869333
381768 KB 165152 KB
216384 KB 232 KB
name rows reserved data index_size unused
SUBORDERS 47857875
32856608 KB 12823168 KB
20006784 KB 26656 KB
name rows reserved data index_size unused
SHP_DEFAULT_FROM_TDC 300000 16080 KB 8696 KB 7312 KB
72 KB
name rows reserved data index_size unused
REF_GSI_SHIPPING_MATRIX 132
40 KB 32 KB
8 KB 0 KB
name rows reserved data index_size unused
REF_SERVICE_GROUPS 105
64 KB 8 KB
56 KB 0 KB
name rows reserved data index_size unused
REF_ZIP_TO_ZONE 60071
10000 KB 3272 KB
6528 KB 200 KB
name rows reserved data index_size unused
SHP_BILLING_BASERATES 591180 167584 KB 38848 KB 128528 KB
208 KB
name rows reserved data index_size unused
SHP_BILLING_SURCHARGERATES 3699318
1519528 KB 260432 KB
1258000 KB 1096 KB
name rows reserved data index_size unused
SHP_BILLING_FUELRATES 12401
3072 KB 704 KB
2024 KB 344 KB
WITH TNT AS (
SELECT *
FROM (
SELECT DEST_ZIP, LOCATION_ID, TNTDAYS
FROM REF_TNT_BY_ZIP
WHERE LOCATION_ID IN (1,17,18)
AND SERVICE_ID = 61
) P
PIVOT
( SUM(TNTDAYS) FOR LOCATION_ID IN ( [1], [17],[18])
) AS PIV
)
SELECT WAG_FISCAL_WEEK_NUM, WAG_FISCAL_YEAR, SHIP_WEIGHT, D.THE_DATE DATE, T.DC_ID ACUTAL_DC, S.PRIMARY_DC_ID OPTIMAL_DC,
ROUND(TOTAL_CHARGE,2) ACTUAL_TOTAL,
CAST(ROUND((ISNULL( ROUND(R.RATE,2),0)+ISNULL(DAS.RATE,0)+CASE WHEN Z.SERVICE_ID IN (60,63,66) AND R.RATE IS NOT NULL THEN 0.19 ELSE 0 END )*(1+ISNULL(F.RATE,0)),2) AS MONEY) OTHER_COST,
ROW_NUMBER () OVER (PARTITION BY T.ROW_KEY ORDER BY (CASE WHEN CAST(ROUND((ISNULL( ROUND(R.RATE,2),0)+ISNULL(DAS.RATE,0)+CASE WHEN Z.SERVICE_ID IN (60,63,66) AND R.RATE IS NOT NULL THEN 0.19 ELSE 0 END )*(1+ISNULL(F.RATE,0)),2) AS MONEY) = 0 THEN 9999 ELSE CAST(ROUND((ISNULL( ROUND(R.RATE,2),0)+ISNULL(DAS.RATE,0)+CASE WHEN Z.SERVICE_ID IN (60,63,66) AND R.RATE IS NOT NULL THEN 0.19 ELSE 0 END )*(1+ISNULL(F.RATE,0)),2) AS MONEY) END )) RANK
FROM
(SELECT ROW_KEY, CHANGE_DATE_DWKEY, CARRIER_ID, SERVICE_ID, TOTAL_CHARGE, SHIP_WEIGHT, LENGTH, WIDTH, HEIGHT, DS_ORDER_ID, SHIP_TO_STATE, TRACKING_NUMBER, SHIP_TO_ZIP, CARRIER_SERVICE, PO_FLAG, ORMD_FLAG, DC_ID FROM SHP_MANIFEST WHERE CHANGE_DATE_DWKEY BETWEEN 5110 AND 5111 /*5140*/) T
JOIN REF_DATE_DIM D ON CHANGE_DATE_DWKEY = D.DATE_DWKEY
LEFT OUTER JOIN TNT ON DEST_ZIP = LEFT(T.SHIP_TO_ZIP,5)
LEFT OUTER JOIN (SELECT * FROM SUBORDERS S WHERE DC_ACKNOWLEDGED > DATEADD(DD,-90,'1/1/2012')) S ON S.SUBORDER_ID = T.DS_ORDER_ID
LEFT OUTER JOIN SHP_DEFAULT_FROM_TDC DC ON DC.DEST_ZIP = LEFT(T.SHIP_TO_ZIP,5) AND RANK = 0
LEFT OUTER JOIN REF_GSI_SHIPPING_MATRIX SM ON
SM.SHIPPING_TYPE_ID = REPLACE(T.CARRIER_SERVICE, 12, 1) AND --> swap 12 with 1 (GSI matrix equiv). 12 and 1 = STANDARD
SM.PO_BOX = T.PO_FLAG AND
SM.ORMD = T.ORMD_FLAG AND
SM.SIG_REQ = 0 AND
SM.TOT_PRICE = 0 AND
T.CHANGE_DATE_DWKEY BETWEEN SM.BEGIN_DATE_DWKEY AND SM.END_DATE_DWKEY and
SM.ETL_GENERATED = 0 --> exclude matrix rows created by ETL (where GSI manually choose alt car grp)
LEFT OUTER JOIN REF_SERVICE_GROUPS SG ON SG.SERVICE_GROUP = CASE WHEN SHIP_TO_STATE IN ('AK','HI') THEN AK_HI WHEN SHIP_TO_STATE = 'PR' THEN PR WHEN SHIP_TO_STATE IN ('AA','AE','AP') THEN APO_FPO WHEN SHIP_TO_STATE IN ('AS','GU','MP','VI','UM','FM','MH','PW') THEN UST ELSE LOWER_48 END AND CHANGE_DATE_DWKEY BETWEEN EFFECTIVE_BEGIN_DATE_DWKEY AND EFFECTIVE_END_DATE_DWKEY
LEFT OUTER JOIN REF_ZIP_TO_ZONE Z ON Z.CARRIER_ID =SG.CARRIER_ID
AND Z.SERVICE_ID = SG.SERVICE_ID
AND Z.LOCATION_ID = DC.LOCATION_ID
AND CASE WHEN Z.SERVICE_ID = 69 AND SHIP_TO_STATE IN ('AK','HI','AA','AE','AP','AS','GU','MP','PR','VI','UM','FM','MH','PW') THEN CAST( 96702 AS INT) ELSE CAST(LEFT(T.SHIP_TO_ZIP,5) AS INT) END BETWEEN CAST(BEGIN_ZIP AS INT) AND CAST(END_ZIP AS INT)
AND (SG.TNT IS NULL OR Z.SERVICE_ID IN (64,65,68,70) OR SG.TNT >= CASE WHEN Z.LOCATION_ID = 1 THEN [1] WHEN Z.LOCATION_ID = 17 THEN [17] WHEN Z.LOCATION_ID = 18 THEN [18] END )
AND ISNUMERIC(Z.BEGIN_ZIP) = 1
AND ISNUMERIC(Z.END_ZIP) = 1
LEFT OUTER JOIN SHP_BILLING_BASERATES R ON R.WEIGHT = CEILING(
CASE
WHEN Z.SERVICE_ID IN (60,63) THEN SHIP_WEIGHT*16
WHEN Z.SERVICE_ID IN (64,65,68,70) AND LENGTH*WIDTH*HEIGHT/166 > SHIP_WEIGHT THEN LENGTH*WIDTH*HEIGHT/166
WHEN Z.SERVICE_ID IN (61) AND LENGTH*WIDTH*HEIGHT > 5184 AND LENGTH*WIDTH*HEIGHT/166 > SHIP_WEIGHT THEN LENGTH*WIDTH*HEIGHT/166
WHEN Z.SERVICE_ID IN (67) AND LENGTH*WIDTH*HEIGHT > 1728 AND Z.ZONE IN (5,6,7,8) AND LENGTH*WIDTH*HEIGHT/196 > SHIP_WEIGHT THEN LENGTH*WIDTH*HEIGHT/196
WHEN Z.SERVICE_ID IN (67) AND DBO.BALOON_CALC(LENGTH,WIDTH,HEIGHT) BETWEEN 84 AND 108 AND Z.ZONE IN (1,2,3,4) AND 20 > SHIP_WEIGHT THEN 20
ELSE SHIP_WEIGHT
END )
AND R.CARRIER_ID = Z.CARRIER_ID
AND R.SERVICE_ID = Z.SERVICE_ID
AND R.EFFECTIVE_END_DATE_DWKEY = 9999
AND R.TYPE = 'R'
AND CAST(Z.ZONE AS INT) = CAST(R.ZONE AS INT)
AND R.LOCATION_ID = Z.LOCATION_ID
LEFT OUTER JOIN SHP_BILLING_SURCHARGERATES DAS ON CAST(LEFT(T.SHIP_TO_ZIP,5) AS INT) = CAST(DAS.BEGIN_ZIP AS INT) AND DAS.SERVICE_ID = Z.SERVICE_ID AND DAS.CARRIER_ID = Z.CARRIER_ID AND DAS.TYPE = 'R' AND DAS.EFFECTIVE_END_DATE_DWKEY =9999 AND DAS.LOCATION_ID = 1
AND ISNUMERIC(DAS.BEGIN_ZIP) = 1
LEFT OUTER JOIN SHP_BILLING_FUELRATES F ON CHANGE_DATE_DWKEY BETWEEN F.EFFECTIVE_BEGIN_DATE_DWKEY AND F.EFFECTIVE_END_DATE_DWKEY
AND F.CARRIER_ID = R.CARRIER_ID
AND F.SERVICE_ID = R.SERVICE_ID
AND F.LOCATION_ID =R.LOCATION_ID
WHERE
ISNUMERIC(LEFT(SHIP_TO_ZIP,5)) = 1
AND ISNUMERIC(ISNULL(DAS.BEGIN_ZIP,1)) = 1
AND ISNUMERIC(Z.BEGIN_ZIP) = 1
AND ISNUMERIC(Z.END_ZIP) = 1
AND ISNUMERIC(TNT.DEST_ZIP) = 1
AND ISNUMERIC(DC.DEST_ZIP) = 1
Thanks
January 27, 2012 at 4:02 pm
Can you post the actual execution plan?
Based on that row count there's just a limited information where the actual problem is.
Other than that I recommend to try the divide'n'conqueur approach:
Store the data from the cte in an indexed temp table.
You might even need to separate the Row_number section (depends on the actual execution plan).
As a side note: I strongly recommend not to use reserved words as column names (e.g. RANK, DATE
January 28, 2012 at 3:27 am
I do not have access to execution plans. This is what my manager sent me.
January 28, 2012 at 4:02 am
Star Trek (1/28/2012)
I do not have access to execution plans. This is what my manager sent me.
If the number of rows is really representative (a few hundred at max) then the quey should run in a few milliseconds.
But it might be that's just a blocking side-effect: if there's a second session performing an update (or insert or delete) wrapped in a transaction and the transaction is not committed/rolled back yet, the query you've posted will have to wait until the update locks are released (assuming the standard database setting "read committed").
January 28, 2012 at 5:03 am
LutzM (1/28/2012)
Star Trek (1/28/2012)
I do not have access to execution plans. This is what my manager sent me.If the number of rows is really representative (a few hundred at max) then the quey should run in a few milliseconds.
But it might be that's just a blocking side-effect: if there's a second session performing an update (or insert or delete) wrapped in a transaction and the transaction is not committed/rolled back yet, the query you've posted will have to wait until the update locks are released (assuming the standard database setting "read committed").
Another strong possibility is that the estimates are hopelessly wrong, and the query ends up running those heap scans (and inner sides of loops joins in general) an enormous number of times. This is just past experience talking though; there is no way to say for sure from the information provided. One thing is certain though: the query is truly awful, and I am not just talking about the formatting!
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 28, 2012 at 6:20 am
for those of you who may be interested......formatted code ...(to the best of my ability!)
maybe the OP would care to check if anything has been missed.
WITH TNT
AS (SELECT *
FROM (SELECT DEST_ZIP,
LOCATION_ID,
TNTDAYS
FROM REF_TNT_BY_ZIP
WHERE LOCATION_ID IN ( 1, 17, 18 )
AND SERVICE_ID = 61) P PIVOT ( SUM(TNTDAYS) FOR LOCATION_ID IN ( [1], [17], [18]) ) AS PIV)
SELECT WAG_FISCAL_WEEK_NUM,
WAG_FISCAL_YEAR,
SHIP_WEIGHT,
D.THE_DATE DATE,
T.DC_ID ACUTAL_DC,
S.PRIMARY_DC_ID OPTIMAL_DC,
Round(TOTAL_CHARGE, 2) ACTUAL_TOTAL,
CAST(Round(( Isnull(Round(R.RATE, 2), 0)
+ Isnull(DAS.RATE, 0)
+ CASE
WHEN Z.SERVICE_ID IN ( 60, 63, 66 ) AND R.RATE IS NOT NULL
THEN 0.19
ELSE 0
END ) * ( 1 + Isnull(F.RATE, 0) ), 2) AS MONEY) OTHER_COST,
Row_number () OVER (PARTITION BY T.ROW_KEY
ORDER BY
(CASE
WHEN CAST(Round((Isnull(Round(R.RATE, 2), 0)+ Isnull(DAS.RATE, 0) +
CASE
WHEN Z.SERVICE_ID IN (60, 63, 66) AND R.RATE IS NOT NULL
THEN 0.19
ELSE 0
END ) *(1+Isnull(F.RATE, 0)), 2) AS MONEY) = 0
THEN 9999
ELSE CAST(Round((Isnull( Round(R.RATE, 2), 0)+Isnull(DAS.RATE, 0) +
CASE
WHEN Z.SERVICE_ID IN (60, 63, 66) AND R.RATE IS NOT NULL
THEN 0.19
ELSE 0
END )*(1+Isnull(F.RATE, 0)), 2) AS MONEY)
END )) RANK
FROM (SELECT ROW_KEY,
CHANGE_DATE_DWKEY,
CARRIER_ID,
SERVICE_ID,
TOTAL_CHARGE,
SHIP_WEIGHT,
LENGTH,
WIDTH,
HEIGHT,
DS_ORDER_ID,
SHIP_TO_STATE,
TRACKING_NUMBER,
SHIP_TO_ZIP,
CARRIER_SERVICE,
PO_FLAG,
ORMD_FLAG,
DC_ID
FROM SHP_MANIFEST
WHERE CHANGE_DATE_DWKEY BETWEEN 5110 AND 5111 /*5140*/) T
JOIN REF_DATE_DIM D ON CHANGE_DATE_DWKEY = D.DATE_DWKEY
LEFT OUTER JOIN TNT ON DEST_ZIP = LEFT(T.SHIP_TO_ZIP, 5)
LEFT OUTER JOIN (SELECT *
FROM SUBORDERS S
WHERE DC_ACKNOWLEDGED > Dateadd(DD, -90, '1/1/2012')) S ON S.SUBORDER_ID = T.DS_ORDER_ID
LEFT OUTER JOIN SHP_DEFAULT_FROM_TDC DC ON DC.DEST_ZIP = LEFT(T.SHIP_TO_ZIP, 5)
AND RANK = 0
LEFT OUTER JOIN REF_GSI_SHIPPING_MATRIX SM ON SM.SHIPPING_TYPE_ID = REPLACE(T.CARRIER_SERVICE, 12, 1)
AND --> swap 12 with 1 (GSI matrix equiv). 12 and 1 = STANDARD
SM.PO_BOX = T.PO_FLAG
AND SM.ORMD = T.ORMD_FLAG
AND SM.SIG_REQ = 0
AND SM.TOT_PRICE = 0
AND T.CHANGE_DATE_DWKEY BETWEEN SM.BEGIN_DATE_DWKEY AND SM.END_DATE_DWKEY
AND SM.ETL_GENERATED = 0 --> exclude matrix rows created by ETL (where GSI manually choose alt car grp)
LEFT OUTER JOIN REF_SERVICE_GROUPS SG ON SG.SERVICE_GROUP = CASE
WHEN SHIP_TO_STATE IN ( 'AK', 'HI' )
THEN AK_HI
WHEN SHIP_TO_STATE = 'PR'
THEN PR
WHEN SHIP_TO_STATE IN ( 'AA', 'AE', 'AP' )
THEN APO_FPO
WHEN SHIP_TO_STATE IN ( 'AS', 'GU', 'MP', 'VI',
'UM', 'FM', 'MH', 'PW' )
THEN UST
ELSE LOWER_48
END
AND CHANGE_DATE_DWKEY BETWEEN EFFECTIVE_BEGIN_DATE_DWKEY AND EFFECTIVE_END_DATE_DWKEY
LEFT OUTER JOIN REF_ZIP_TO_ZONE Z ON Z.CARRIER_ID = SG.CARRIER_ID
AND Z.SERVICE_ID = SG.SERVICE_ID
AND Z.LOCATION_ID = DC.LOCATION_ID
AND CASE
WHEN Z.SERVICE_ID = 69
AND SHIP_TO_STATE IN ( 'AK', 'HI', 'AA', 'AE',
'AP', 'AS', 'GU', 'MP',
'PR', 'VI', 'UM', 'FM',
'MH', 'PW' )
THEN CAST(96702 AS INT)
ELSE CAST(LEFT(T.SHIP_TO_ZIP, 5) AS INT)
END BETWEEN CAST(BEGIN_ZIP AS INT) AND CAST(END_ZIP AS INT)
AND ( SG.TNT IS NULL
OR Z.SERVICE_ID IN ( 64, 65, 68, 70 )
OR SG.TNT >= CASE
WHEN Z.LOCATION_ID = 1
THEN [1]
WHEN Z.LOCATION_ID = 17
THEN [17]
WHEN Z.LOCATION_ID = 18
THEN [18]
END )
AND Isnumeric(Z.BEGIN_ZIP) = 1
AND Isnumeric(Z.END_ZIP) = 1
LEFT OUTER JOIN SHP_BILLING_BASERATES R ON R.WEIGHT = Ceiling(CASE
WHEN Z.SERVICE_ID IN ( 60, 63 )
THEN SHIP_WEIGHT * 16
WHEN Z.SERVICE_ID IN ( 64, 65, 68, 70 )
AND LENGTH * WIDTH * HEIGHT / 166 > SHIP_WEIGHT
THEN LENGTH * WIDTH * HEIGHT / 166
WHEN Z.SERVICE_ID IN ( 61 )
AND LENGTH * WIDTH * HEIGHT > 5184
AND LENGTH * WIDTH * HEIGHT / 166 > SHIP_WEIGHT
THEN LENGTH * WIDTH * HEIGHT / 166
WHEN Z.SERVICE_ID IN ( 67 )
AND LENGTH * WIDTH * HEIGHT > 1728
AND Z.ZONE IN ( 5, 6, 7, 8 )
AND LENGTH * WIDTH * HEIGHT / 196 > SHIP_WEIGHT
THEN LENGTH * WIDTH * HEIGHT / 196
WHEN Z.SERVICE_ID IN ( 67 )
AND DBO.Baloon_calc(LENGTH, WIDTH, HEIGHT) BETWEEN 84 AND 108
AND Z.ZONE IN ( 1, 2, 3, 4 )
AND 20 > SHIP_WEIGHT
THEN 20
ELSE SHIP_WEIGHT
END)
AND R.CARRIER_ID = Z.CARRIER_ID
AND R.SERVICE_ID = Z.SERVICE_ID
AND R.EFFECTIVE_END_DATE_DWKEY = 9999
AND R.TYPE = 'R'
AND CAST(Z.ZONE AS INT) = CAST(R.ZONE AS INT)
AND R.LOCATION_ID = Z.LOCATION_ID
LEFT OUTER JOIN SHP_BILLING_SURCHARGERATES DAS ON CAST(LEFT(T.SHIP_TO_ZIP, 5) AS INT) = CAST(DAS.BEGIN_ZIP AS INT)
AND DAS.SERVICE_ID = Z.SERVICE_ID
AND DAS.CARRIER_ID = Z.CARRIER_ID
AND DAS.TYPE = 'R'
AND DAS.EFFECTIVE_END_DATE_DWKEY = 9999
AND DAS.LOCATION_ID = 1
AND Isnumeric(DAS.BEGIN_ZIP) = 1
LEFT OUTER JOIN SHP_BILLING_FUELRATES F ON CHANGE_DATE_DWKEY BETWEEN F.EFFECTIVE_BEGIN_DATE_DWKEY AND F.EFFECTIVE_END_DATE_DWKEY
AND F.CARRIER_ID = R.CARRIER_ID
AND F.SERVICE_ID = R.SERVICE_ID
AND F.LOCATION_ID = R.LOCATION_ID
WHERE Isnumeric(LEFT(SHIP_TO_ZIP, 5)) = 1
AND Isnumeric(Isnull(DAS.BEGIN_ZIP, 1)) = 1
AND Isnumeric(Z.BEGIN_ZIP) = 1
AND Isnumeric(Z.END_ZIP) = 1
AND Isnumeric(TNT.DEST_ZIP) = 1
AND Isnumeric(DC.DEST_ZIP) = 1
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply