February 5, 2015 at 11:41 am
Looking for help tuning this query (plan attached) which contains a cross apply
thanks!
February 5, 2015 at 12:01 pm
3 million rows filtered down to 9 by a late filter... Ouch.
Since you're only going for the first row within the cross apply, why not change that to a simple TOP 1 with an order by? That's what I'd try. Or, see if you can put an index on there that satisfies that filter criteria to avoid the table scan. You'll end up with a bookmark lookup, but I'll bet that's small potatoes to what you're seeing now.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 5, 2015 at 12:36 pm
The legendary ScaryDBA! I'm a big fan of your distilled tuning book and Red Gate in general. Thanks the for the help. My thoughts were in line with yours, I actually tested using TOP, it performed worse than the ROW_NUM version, even with a covering supporting index. Both tests were ran on a clean buffer and plan cache.
original query:
Table 'cat_stock_nbr'. Scan count 712562, logical reads 2163168, physical reads 337, read-ahead reads 10650, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 11938 ms, elapsed time = 14741 ms.
------------------------------------------------------------------------------
TOP version:
Table 'cat_stock_nbr'. Scan count 712562, logical reads 2324353, physical reads 353, read-ahead reads 10650, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 10392 ms, elapsed time = 14789 ms.
February 5, 2015 at 12:43 pm
I believe part of the problem has to do with the OR in the WHERE clause, removing it gives me a better plan that runs in subseconds.
select
stockNew.stock_nbr_ky as stock_nbr_ky_new,
stockOld.stock_nbr AS from_stock_nbr,
stockOld.item_desc AS from_stock_desc,
stockOld.unit_price / stockOld.ui_conversion_factor AS from_stocking_ui_price,
stockNew.stock_nbr AS to_stock_nbr,
stockNew.item_desc AS to_stock_desc,
stockNew.unit_price / stockNew.ui_conversion_factor AS to_stocking_ui_price,
stockNew.pgm_id,
stockNew.lst_tran_dt_tm AS as_of_dt_tm,
stockNew.lst_updtd_by AS change_by,
stockNew.history_record_generated_dt_tm,
stockOld.priorRecord_stock_nbr_ky,
stockOld.stock_nbr,
stockOld.item_desc,
stockOld.unit_price,
stockOld.ui_conversion_factor,
stockOld.lin_tamcn_ky,
stockOld.reportable_commodity_type_ky,
stockOld.security_commodity_type_ky,
stockOld.stocking_unit_of_issue_cd,
stockNew.stocking_unit_of_issue_cd,
stockOld.stores_account_cd,
stockOld.stock_item_cd as stock_item_cd_old,
stockNew.stock_item_cd as stock_item_cd_new,
stockOld.federal_supply_class_cd as federal_supply_class_cd_old,
stockNew.federal_supply_class_cd as federal_supply_class_cd_new,
stockOld.history_record_generated_dt_tm as old_history_record_generated_dt_tm,
stockOld.stores_account_cd as stores_account_cd_old,
stockNew.stores_account_cd as stores_account_cd_new
FROM his.cat_stock_nbr stockNew
-- Get the most recent stock number history record that was there before the new record was logged
CROSS APPLY (
SELECT
stock_nbr_ky as priorRecord_stock_nbr_ky,
stock_nbr,
item_desc,
unit_price,
ui_conversion_factor,
lin_tamcn_ky,
reportable_commodity_type_ky,
security_commodity_type_ky,
stocking_unit_of_issue_cd,
stores_account_cd,
stock_item_cd,
federal_supply_class_cd,
history_record_generated_dt_tm,
ROW_NUMBER()OVER(PARTITION BY stock_nbr_ky ORDER BY history_record_generated_dt_tm DESC) rownum
FROM his.cat_stock_nbr priorRecord
WHERE priorRecord.stock_nbr_ky = stockNew.stock_nbr_ky
AND priorRecord.cat_stock_nbr_his_ky < stockNew.cat_stock_nbr_his_ky
AND priorRecord.history_record_generated_dt_tm < stockNew.history_record_generated_dt_tm
) as stockOld
WHERE (stockNew.stock_nbr_ky = 000000 OR stockOld.priorRecord_stock_nbr_ky = 00000)
and stockOld.rownum = 1
and stockNew.operation$ <> 2
February 5, 2015 at 12:51 pm
What is the OR for anyway?
Doesn't the WHERE clause within your apply guarantee that the both of those values are the same, meaning that the conditions are always both true or neither true?
Or am I misreading it?
February 5, 2015 at 12:56 pm
Move the specific nbr_ky to the inner query to limit the rows that have to be read there:
CROSS APPLY (
SELECT TOP (1)
stock_nbr_ky as priorRecord_stock_nbr_ky,
stock_nbr,
item_desc,
unit_price,
ui_conversion_factor,
lin_tamcn_ky,
reportable_commodity_type_ky,
security_commodity_type_ky,
stocking_unit_of_issue_cd,
stores_account_cd,
stock_item_cd,
federal_supply_class_cd,
history_record_generated_dt_tm,
FROM his.cat_stock_nbr priorRecord
WHERE priorRecord.stock_nbr_ky = stockNew.stock_nbr_ky
AND priorRecord.cat_stock_nbr_his_ky < stockNew.cat_stock_nbr_his_ky
AND priorRecord.history_record_generated_dt_tm < stockNew.history_record_generated_dt_tm
AND priorRecord._stock_nbr_ky = 732504
ORDER BY history_record_generated_dt_tm DESC
) as stockOld
WHERE (stockNew.stock_nbr_ky = 732504)
and stockOld.rownum = 1
and stockNew.operation$ <> 2
Edit: Bolded where this query was different from original.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
February 5, 2015 at 1:16 pm
I know for this example the stock_nbr_ky values are the same but they won't always be
February 5, 2015 at 1:28 pm
jonesboy21 (2/5/2015)
I know for this example the stock_nbr_ky values are the same but they won't always be
Not sure what you mean by this. Your cross apply has priorRecord.stock_nbr_ky = stockNew.stock_nbr_ky in its where clause.
Can you give an example where they wont be the same but the OR should catch them?
February 5, 2015 at 1:47 pm
What if you filter the records first before CROSS APPLY?
select
stockNew.stock_nbr_ky as stock_nbr_ky_new,
stockOld.stock_nbr AS from_stock_nbr,
stockOld.item_desc AS from_stock_desc,
stockOld.unit_price / stockOld.ui_conversion_factor AS from_stocking_ui_price,
stockNew.stock_nbr AS to_stock_nbr,
stockNew.item_desc AS to_stock_desc,
stockNew.unit_price / stockNew.ui_conversion_factor AS to_stocking_ui_price,
stockNew.pgm_id,
stockNew.lst_tran_dt_tm AS as_of_dt_tm,
stockNew.lst_updtd_by AS change_by,
stockNew.history_record_generated_dt_tm,
stockOld.priorRecord_stock_nbr_ky,
stockOld.stock_nbr,
stockOld.item_desc,
stockOld.unit_price,
stockOld.ui_conversion_factor,
stockOld.lin_tamcn_ky,
stockOld.reportable_commodity_type_ky,
stockOld.security_commodity_type_ky,
stockOld.stocking_unit_of_issue_cd,
stockNew.stocking_unit_of_issue_cd,
stockOld.stores_account_cd,
stockOld.stock_item_cd as stock_item_cd_old,
stockNew.stock_item_cd as stock_item_cd_new,
stockOld.federal_supply_class_cd as federal_supply_class_cd_old,
stockNew.federal_supply_class_cd as federal_supply_class_cd_new,
stockOld.history_record_generated_dt_tm as old_history_record_generated_dt_tm,
stockOld.stores_account_cd as stores_account_cd_old,
stockNew.stores_account_cd as stores_account_cd_new
FROM (
SELECT *
FROM his.cat_stock_nbr
WHERE stockNew.stock_nbr_ky = 732504
UNION
SELECT *
FROM priorRecord_stock_nbr_ky = 732504
)StockFilter
CROSS APPLY (
SELECT
stock_nbr_ky AS priorRecord_stock_nbr_ky,
stock_nbr,
item_desc,
unit_price,
ui_conversion_factor,
lin_tamcn_ky,
reportable_commodity_type_ky,
security_commodity_type_ky,
stocking_unit_of_issue_cd,
stores_account_cd,
stock_item_cd,
federal_supply_class_cd,
history_record_generated_dt_tm,
ROW_NUMBER()OVER(PARTITION BY stock_nbr_ky ORDER BY history_record_generated_dt_tm DESC) rownum
FROM his.cat_stock_nbr priorRecord
WHERE priorRecord.stock_nbr_ky = stockNew.stock_nbr_ky
AND priorRecord.cat_stock_nbr_his_ky < stockNew.cat_stock_nbr_his_ky
AND priorRecord.history_record_generated_dt_tm < stockNew.history_record_generated_dt_tm
) AS stockOld
WHERE
stockOld.rownum = 1
AND stockNew.operation$ <> 2
February 5, 2015 at 1:54 pm
I believe this is sort of what I was looking for, let me test this one out and see how it performs. I'll let you know what I find. Thanks
February 5, 2015 at 1:56 pm
Thanks for the kind words.
What if you just work on the inner part of the apply query. Can you get that to filter on it's own, either by changing the index, modify the query, something. Sometimes breaking down the problem can help.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 5, 2015 at 2:07 pm
Nevyn (2/5/2015)
What is the OR for anyway?Doesn't the WHERE clause within your apply guarantee that the both of those values are the same, meaning that the conditions are always both true or neither true?
Or am I misreading it?
No, you're absolutely right. This query only works for the two hardcoded values. There's no point in attempting to optimise a query which won't ever be run - except as an exercise.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply