March 13, 2012 at 1:11 pm
The query written below is taking randomly distinct time to execute, every time it is executed. It remains suspended for long time under PAGEIOLATCH_SH and PAGEIOLATCH_EX. On an average it is taking 15 minutes to complete the transaction.
I made sure that event log is not issuing error 833 in order to check for disk subsystem problem but, I could not see MSSQLSERVER 833 error. I did check for Avg. disk sec/Transfer and it remains average at about 0.018 which is a symptom of something wrong with I/O issuing.
The bottom line is I want to optimize this query and I could not see any way to do so, Since it has non SARGable predicate which I cannot avoid.
I have also attached the execution plan.
Any suggestions to optimize this query.
UPDATE
SHP_MANIFEST
SET
DELIVERED = D.DELIVERED_PST,
DELIVERED_DATE_DWKEY = DATEDIFF(dd, '1/4/1998', D.DELIVERED_PST)
FROM
SHP_DELIVERED_PACKAGES D INNER JOIN
SHP_MANIFEST M ON D.TRACKING_NUMBER = M.TRACKING_NUMBER AND (DATEDIFF(dd, '1/4/1998', D.DELIVERED_PST) - M.LABEL_PRINTED_DATE_DWKEY BETWEEN -10 AND 100)
WHERE
D.CARRIER_KEY = 2 AND
(
M.DELIVERED <> D.DELIVERED_PST OR
M.DELIVERED IS NULL
) AND
DATEDIFF(dd, '1/4/1998', D.DELIVERED_PST) >= LABEL_PRINTED_DATE_DWKEY
The tables involved are:
1)SHP_MANIFEST: number of rows 26345486
2)SHP_DELIVERED_PACKAGES: number of rows 20205076
Indexes involved are:
CREATE NONCLUSTERED INDEX [INDEX_SHP_MANIFEST_2] ON [dbo].[SHP_MANIFEST]
(
[TRACKING_NUMBER] ASC,
[LABEL_PRINTED_DATE_DWKEY] ASC,
[DELIVERED] ASC,
[DELIVERED_DATE_DWKEY] ASC
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [index_SHP_DELIVERED_PACKAGES_1] ON [dbo].[SHP_DELIVERED_PACKAGES]
(
[CARRIER_KEY] ASC,
[TRACKING_NUMBER] ASC
)
INCLUDE ( [DELIVERED_PST]) ON [PRIMARY]
March 13, 2012 at 1:30 pm
i just moved the datediff to a cross apply so that it is not calculated twice (i assume it is, i may be worng)
UPDATE
SHP_MANIFEST
SET
DELIVERED = D.DELIVERED_PST,
DELIVERED_DATE_DWKEY = CrsApp.DtDiffDlvrdPST
FROM
SHP_DELIVERED_PACKAGES D INNER JOIN
SHP_MANIFEST M ON D.TRACKING_NUMBER = M.TRACKING_NUMBER AND ( DATEDIFF(dd, '1/4/1998', D.DELIVERED_PST) - M.LABEL_PRINTED_DATE_DWKEY BETWEEN -10 AND 100)
CROSS APPLY (SELECT DATEDIFF(dd, '1/4/1998', D.DELIVERED_PST) ) CrsApp (DtDiffDlvrdPST)
WHERE
D.CARRIER_KEY = 2 AND
(
M.DELIVERED <> D.DELIVERED_PST OR
M.DELIVERED IS NULL
) AND
CrsApp.DtDiffDlvrdPST >= LABEL_PRINTED_DATE_DWKEY
March 13, 2012 at 1:31 pm
Wow that's a large update! Off hand I see an index scan in your execution plan that may save a little time if you modified your existing index to have the proper columns.
Secondly, I would attempt to do this in smaller batches. Create a temp table with the cols you need. Populate it with a SELECT TOP 1000 rows (or some other number) from your main query, UPDATE the table that needs the update with the data from the temp table?
You could either wrap it in a WHILE LOOP and keep updating until all records are processed or just schedule it to run in an Agent Job until it's all done
Ex: (obviously you'd have to change this in order for it to work)DECLARE @Manifest TABLE (
idx int IDENTITY(1,1),
CarrierKey int,
..--[. Any other columns which would make a record unique]
UpdDelivered datetime,
Delivered_DDwkey datetime)
INSERT INTO @Manifest
SELECT TOP 1000
D.DELIVERED_PST, CARRIER_KEY, DELIVERED_DATE_DWKEY = DATEDIFF(dd, '1/4/1998', D.DELIVERED_PST)
FROM
SHP_DELIVERED_PACKAGES D INNER JOIN
SHP_MANIFEST M ON D.TRACKING_NUMBER = M.TRACKING_NUMBER
AND (DATEDIFF(dd, '1/4/1998', D.DELIVERED_PST) - M.LABEL_PRINTED_DATE_DWKEY BETWEEN -10 AND 100)
WHERE
D.CARRIER_KEY = 2
AND (M.DELIVERED <> D.DELIVERED_PST OR M.DELIVERED IS NULL)
AND DATEDIFF(dd, '1/4/1998', D.DELIVERED_PST) >= LABEL_PRINTED_DATE_DWKEY
/* Now run the update */
UPDATE
SHP_MANIFEST
SET
DELIVERED = UpdDelivered, DELIVERED_DATE_DWKEY = Delivered_DDwkey
FROM @Manifest
WHERE
CARRIER_KEY = CarrierKey
AND ...--[ther conditions which make the record unique (adjust temp table accordingly)]
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 13, 2012 at 2:42 pm
MyDoggieJessie (3/13/2012)
Wow that's a large update! Off hand I see an index scan in your execution plan that may save a little time if you modified your existing index to have the proper columns.
I could not make it to index seek since it is a predicate on inequality. I tried all combination of columns in the index.
March 13, 2012 at 4:54 pm
ColdCoffee (3/13/2012)
i just moved the datediff to a cross apply so that it is not calculated twice (i assume it is, i may be worng)
UPDATESHP_MANIFEST
SET
DELIVERED = D.DELIVERED_PST,
DELIVERED_DATE_DWKEY = CrsApp.DtDiffDlvrdPST
FROM
SHP_DELIVERED_PACKAGES D INNER JOIN
SHP_MANIFEST M ON D.TRACKING_NUMBER = M.TRACKING_NUMBER AND ( DATEDIFF(dd, '1/4/1998', D.DELIVERED_PST) - M.LABEL_PRINTED_DATE_DWKEY BETWEEN -10 AND 100)
CROSS APPLY (SELECT DATEDIFF(dd, '1/4/1998', D.DELIVERED_PST) ) CrsApp (DtDiffDlvrdPST)
WHERE
D.CARRIER_KEY = 2 AND
(
M.DELIVERED <> D.DELIVERED_PST OR
M.DELIVERED IS NULL
) AND
CrsApp.DtDiffDlvrdPST >= LABEL_PRINTED_DATE_DWKEY
It updated the same numbers of rows but, have no effect on performance....execution plan is also the same
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply