SQL Server Performance Forum – Threads Archive
Overcome Clustered index scan
Is there any way i can overcome clustered index scan? Thanks,Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"
A clustered index scan is not so bad – it’s much better than a table scan. It suggests that you may not have the ideal indexes for the query that you’re running – but this may be a non-critical query, and the indexes may be appropriate for more important queries. It might also be a statistics issue. And of course your query may be improved upon.
I have a proc which runs for an hour to delete the duplicate records in a table and the execution time increases day by day as the incoming records to that table increase… The table (4.1 million rows) has 29 constraints, 50 statistics indexes (that start with _WA_SYS), 1 Clustered index that is up-to-date and 1 non-clustered index is not so frequently updated and well fragmented. but my query doesnt need that. is there a way to tune this??? i have attached the time consuming part of my code below…
INSERT INTO TBL_MD_DEBUG_ALLOCATION
( ALLOCATION_ID
,RECORD_TYPE
,MGN_TIME_ID
,PAR_MONTH
,TIME_STAMP
,REC_deleted
)
SELECT ALLOCATION_ID
,RECORD_TYPE
,MGN_TIME_ID
,t1.FIS_YEAR * 100 + t1.FIS_MONTH
,getdate()
,0
FROM TBL_WH_MGN_PEND_EVAL
JOIN tbl_wh_time t1
ON t1.time_id = TBL_WH_MGN_PEND_EVAL.MGN_TIME_ID
JOIN TBL_WH_PERIOD
ON t1.time_date >= TBL_WH_PERIOD.START_DATE
AND t1.time_date <= TBL_WH_PERIOD.END_DATE
WHERE TBL_WH_MGN_PEND_EVAL.EVAL_FLAG = 1 –RECORD HAS BEEN EVALUATED
AND TBL_WH_PERIOD.PERIOD_STATUS = 1 –PERIOD IS CLOSED
AND RECORD_TYPE <> @RTMType
GROUP BY RECORD_TYPE
,ALLOCATION_ID
,t1.FIS_YEAR * 100 + t1.FIS_MONTH
,MGN_TIME_ID
HAVING COUNT(*) > 1
UNION ALL
SELECT ALLOCATION_ID
,RECORD_TYPE
,TRAN_TIME_ID
,PAR_MONTH
,getdate()
,0
FROM TBL_WH_MGN_PEND_EVAL
INNER JOIN TBL_WH_TIME T1
ON T1.TIME_ID = TBL_WH_MGN_PEND_EVAL.MGN_TIME_ID
INNER JOIN TBL_WH_PERIOD
ON T1.TIME_DATE >= TBL_WH_PERIOD.START_DATE
AND T1.TIME_DATE <= TBL_WH_PERIOD.END_DATE
WHERE TBL_WH_MGN_PEND_EVAL.EVAL_FLAG = 1 –RECORD HAS BEEN EVALUATED
AND TBL_WH_PERIOD.PERIOD_STATUS = 1 –PERIOD IS CLOSED
AND record_type IN (@DepotType, @DirectType, @DirectCorrectionType)
GROUP BY ALLOCATION_ID
,TRAN_time_ID
,PAR_MONTH
,RECORD_TYPE
HAVING count(*) > 1
INSERT INTO TBL_MD_DEBUG_MGN_PEND_EVAL (
[TBL_WH_MGN_PEND_EVAL] ,
[ALLOCATION_ID] ,
[ALLOC_POD_ID] ,
[ALLOC_UNITS] ,
[ALLOC_UNIT_ID] ,
[CIF_PER_TRAY] ,
[COST_PER_TRAY] ,
[COST_PRI_CUR_ID],
[COST_PRI_EXCH_RATE] ,
[COST_VALUE] ,
[COST_VALUE_GBP] ,
[COUNTRY_ID] ,
[DEL_TYPE_ID] ,
[DEL_UNIT_QTY],
[DEPOT_ID] ,
[DUTY_PER_TRAY] ,
[EST_COST] ,
[EST_WGT] ,
[EVAL_FLAG] ,
[EVAL_DATE_ID] ,
[EXCISE_PER_TRAY] ,
[INIT_CIF_PER_TRAY] ,
[INIT_COST_PER_TRAY] ,
[INIT_COST_PRI_CUR] ,
[INIT_COST_PRI_EXC_RATE] ,
[INIT_DUTY_PER_TRAY] ,
[INIT_EXCISE_PER_TRAY] ,
[INIT_ORIG_SELL_PRI] ,
[INIT_ORIG_SELL_PRI_CATCH_WT_FLAG] ,
[INIT_SELL_CUR_EXCH_RATE] ,
[INIT_SELL_PRI_CATCH_WT_FLAG] ,
[INIT_STORE_SELL_PRI] ,
[INIT_TRAYS] ,
[INIT_TRAY_WGT] ,
[INIT_TRF_PRI] ,
[INIT_TRF_PRI_CATCH_WT_FLAG] ,
[INIT_TRF_PRI_CUR] ,
[INIT_TRF_PRI_EXCH_RATE] ,
[INIT_TRF_PRI_TYPE] ,
[INIT_TRF_VALUE] ,
[INIT_UNITS] ,
[INIT_UNITS_PER_TRAY] ,
[INTO_STORE_TIME_ID] ,
[KEY_FIELD] ,
[MGN_CIF_VALUE] ,
[MGN_CIF_VALUE_GBP] ,
[MGN_COST_VALUE] ,
[MGN_COST_VALUE_GBP] ,
[MGN_DUTY_VALUE] ,
[MGN_DUTY_VALUE_GBP] ,
[MGN_EXCISE_VALUE] ,
[MGN_EXCISE_VALUE_GBP] ,
[MGN_ORI_SELL_VALUE] ,
[MGN_ORI_SELL_VALUE_GBP] ,
[MGN_SELL_VALUE] ,
[MGN_SELL_VALUE_GBP] ,
[MGN_TIME_ID] ,
[MGN_TRAYS] ,
[MGN_TRF_VALUE] ,
[MGN_TRF_VALUE_GBP] ,
[MGN_UNITS] ,
[MNS_OWN_DATE_ID] ,
[ORIG_SELL_PRI] ,
[ORIG_SELL_PRI_CATCH_WT_FLAG] ,
[ORIG_SELL_VALUE] ,
[ORIG_SELL_VALUE_GBP] ,
[PAR_MONTH] ,
[POD_MATCH_IND] ,
[PRD_VAT_PER] ,
[RECORD_TYPE] ,
[RTM_REASON_CODE] ,
[SELL_VALUE] ,
[SELL_VALUE_GBP] ,
[SELL_PRI_EXCH_RATE] ,
[SELL_PRI_CURR] ,
[STORE_ID] ,
[STORE_SELL_PRI] ,
[STORE_SELL_PRI_CATCH_WT_FLAG] ,
[SUPP_ID] ,
[THIRD_PTY_INV_FLAG] ,
[THIRD_PTY_FLAG] ,
[TRAN_TIME_ID] ,
[TRAN_QTY] ,
[TRAN_VALUE] ,
[TRAY_WGT] ,
[TRAYS] ,
[TRF_PRI] ,
[TRF_PRI_CATCH_WT_FLAG] ,
[TRF_PRI_CUR_ID] ,
[TRF_PRI_EXCH_RATE] ,
[TRF_PRI_TYPE] ,
[UNITS] ,
[UNITS_PER_TRAY] ,
[UPC_ID] ,
[VAT_PER] ,
[WEIGHT_FLAG] ,
[TIME_STAMP]
)
SELECT [TBL_WH_MGN_PEND_EVAL] ,
[ALLOCATION_ID] ,
[ALLOC_POD_ID] ,
[ALLOC_UNITS] ,
[ALLOC_UNIT_ID] ,
[CIF_PER_TRAY] ,
[COST_PER_TRAY] ,
[COST_PRI_CUR_ID],
[COST_PRI_EXCH_RATE] ,
[COST_VALUE] ,
[COST_VALUE_GBP] ,
[COUNTRY_ID] ,
[DEL_TYPE_ID] ,
[DEL_UNIT_QTY],
[DEPOT_ID] ,
[DUTY_PER_TRAY] ,
[EST_COST] ,
[EST_WGT] ,
[EVAL_FLAG] ,
[EVAL_DATE_ID] ,
[EXCISE_PER_TRAY] ,
[INIT_CIF_PER_TRAY] ,
[INIT_COST_PER_TRAY] ,
[INIT_COST_PRI_CUR] ,
[INIT_COST_PRI_EXC_RATE] ,
[INIT_DUTY_PER_TRAY] ,
[INIT_EXCISE_PER_TRAY] ,
[INIT_ORIG_SELL_PRI] ,
[INIT_ORIG_SELL_PRI_CATCH_WT_FLAG] ,
[INIT_SELL_CUR_EXCH_RATE] ,
[INIT_SELL_PRI_CATCH_WT_FLAG] ,
[INIT_STORE_SELL_PRI] ,
[INIT_TRAYS] ,
[INIT_TRAY_WGT] ,
[INIT_TRF_PRI] ,
[INIT_TRF_PRI_CATCH_WT_FLAG] ,
[INIT_TRF_PRI_CUR] ,
[INIT_TRF_PRI_EXCH_RATE] ,
[INIT_TRF_PRI_TYPE] ,
[INIT_TRF_VALUE] ,
[INIT_UNITS] ,
[INIT_UNITS_PER_TRAY] ,
[INTO_STORE_TIME_ID] ,
[KEY_FIELD] ,
[MGN_CIF_VALUE] ,
[MGN_CIF_VALUE_GBP] ,
[MGN_COST_VALUE] ,
[MGN_COST_VALUE_GBP] ,
[MGN_DUTY_VALUE] ,
[MGN_DUTY_VALUE_GBP] ,
[MGN_EXCISE_VALUE] ,
[MGN_EXCISE_VALUE_GBP] ,
[MGN_ORI_SELL_VALUE] ,
[MGN_ORI_SELL_VALUE_GBP] ,
[MGN_SELL_VALUE] ,
[MGN_SELL_VALUE_GBP] ,
[MGN_TIME_ID] ,
[MGN_TRAYS] ,
[MGN_TRF_VALUE] ,
[MGN_TRF_VALUE_GBP] ,
[MGN_UNITS] ,
[MNS_OWN_DATE_ID] ,
[ORIG_SELL_PRI] ,
[ORIG_SELL_PRI_CATCH_WT_FLAG] ,
[ORIG_SELL_VALUE] ,
[ORIG_SELL_VALUE_GBP] ,
[PAR_MONTH] ,
[POD_MATCH_IND] ,
[PRD_VAT_PER] ,
[RECORD_TYPE] ,
[RTM_REASON_CODE] ,
[SELL_VALUE] ,
[SELL_VALUE_GBP] ,
[SELL_PRI_EXCH_RATE] ,
[SELL_PRI_CURR] ,
[STORE_ID] ,
[STORE_SELL_PRI] ,
[STORE_SELL_PRI_CATCH_WT_FLAG] ,
[SUPP_ID] ,
[THIRD_PTY_INV_FLAG] ,
[THIRD_PTY_FLAG] ,
[TRAN_TIME_ID] ,
[TRAN_QTY] ,
[TRAN_VALUE] ,
[TRAY_WGT] ,
[TRAYS] ,
[TRF_PRI] ,
[TRF_PRI_CATCH_WT_FLAG] ,
[TRF_PRI_CUR_ID] ,
[TRF_PRI_EXCH_RATE] ,
[TRF_PRI_TYPE] ,
[UNITS] ,
[UNITS_PER_TRAY] ,
[UPC_ID] ,
[VAT_PER] ,
[WEIGHT_FLAG] ,
getdate()
FROM TBL_WH_MGN_PEND_EVAL
where allocation_id in (
SELECT DISTINCT ALLOCATION_ID
FROM TBL_MD_DEBUG_ALLOCATION
WHERE REC_DELETED = 0
)
Begin Transaction USP_DELETE_DUPLICATE_MARGIN DELETE FROM TBL_WH_MGN_PEND_EVAL
FROM TBL_WH_MGN_PEND_EVAL PEND
WHERE PEND.TBL_WH_MGN_PEND_EVAL NOT IN (
SELECT MIN(TBL_WH_MGN_PEND_EVAL)
FROM TBL_WH_MGN_PEND_EVAL PEND1
WHERE PEND1.ALLOCATION_ID IN (
SELECT DISTINCT ALLOCATION_ID
FROM TBL_MD_DEBUG_ALLOCATION
WHERE REC_DELETED = 0
)
GROUP BY PEND1.ALLOCATION_ID
)
AND PEND.ALLOCATION_ID IN (
SELECT DISTINCT ALLOCATION_ID
FROM TBL_MD_DEBUG_ALLOCATION
WHERE REC_DELETED = 0
) UPDATE TBL_MD_DEBUG_ALLOCATION
SET REC_DELETED = 1
WHERE REC_DELETED = 0 Commit transaction USP_DELETE_DUPLICATE_MARGIN Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"
I would look at NOT inserting duplicate records in the first place. Deletes take more time than inserts.
When i analysed the execution plan for the above query, the clustered index scan on the table TBL_WH_MGN_PEND_EVAL is constituting for 85% of the total cost and that is why i badly want to eliminate the clustered index scan Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"
Hi Ram,
I have read many times that EXISTS is much quicker than IN.
So you can try
SELECT cols
FROM TBL_WH_MGN_PEND_EVAL t
where EXISTS( SELECT ALLOCATION_ID
FROM TBL_MD_DEBUG_ALLOCATION t1
WHERE REC_DELETED = 0 and t.allocation_id =t1.allocation_id)
What I meant was that you should avoid deleting rows. So don’t insert them in the first place. The clustered index scan takes 85% of the execution plan? In itself, that is a good sign, as a relatively fast process makes up 85% of your query. But you’re probably having to wait a long time for the delete to finish – deletes are just SLOW. Is the clustered index a composite index? That would make both inserts and deletes even slower.
Yes, I do very much agree that. but it is out of my hands. my clients are not agreeing for that. they just want to make this delete run faster. still i have proposed to check the duplicates at insertion. but as an immediate solution to the prod issue, we need some solution for this. Clustered index has only one column and that too an identity column. Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"
Thanks Ranjit for pointing that. I am in a hurry and i am missing some oblivious things…
quote:Originally posted by ranjitjain
Hi Ram,
I have read many times that EXISTS is much quicker than IN.
So you can try
SELECT cols
FROM TBL_WH_MGN_PEND_EVAL t
where EXISTS( SELECT ALLOCATION_ID
FROM TBL_MD_DEBUG_ALLOCATION t1
WHERE REC_DELETED = 0 and t.allocation_id =t1.allocation_id)
I have read many times that EXISTS is much quicker than IN.
So you can try
SELECT cols
FROM TBL_WH_MGN_PEND_EVAL t
where EXISTS( SELECT ALLOCATION_ID
FROM TBL_MD_DEBUG_ALLOCATION t1
WHERE REC_DELETED = 0 and t.allocation_id =t1.allocation_id)
Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"
i think you are missing the point,
how do you expect to find duplicate records without scannning the table a clustered index scan is a table scan, its just that the table is stored as an index instead of a heap you might consider finding only duplicate records for a given customer, instead of the entire table,
]]>