Can index Scan be replaced with index seek, in order to avoid a huge scan

SQLDB 2016-07-13 20:08:11

Hi,

We have this SP, which has went to run for about 40 mins, which a week agao was running in 5-10 seconds. Yes approx 20K records were added and indexes were adjusted but yet no improvements.

I beleive the the problmeatic part in that plan can be the thick pipe where an index scan is occuring. I added the required index to avoid predicate but seems not to worked:

CREATE NONCLUSTERED INDEX [TBLAssetValidationDForEmp_] ON [dbo].[TblAssetValidationDataForEmployee]
(
[CycleID] ASC,
[IsValidatedByEmployee] ASC
)
INCLUDE ( [AssetID],
[EmployeeID],
[DeptID])

Note** As per many suggestions and recommendations from SQLKIWI for adjusting the codes and split string function, we have been doing for many of them. Many, thanks for his great suggestion we have improved the performances , but few cannot be due to application constraint.

Any suggestions would be of great help.

/storage/attachments/1656-Plan.pesession

Aaron Bertrand 2016-07-14 16:17:04

With an OR condition, it's not very likely there is a simple way to avoid a scan. The best solution I have found is to use dynamic SQL and apply only the predicates that matter due to parameter values, which might be a viable solution here (the query is too large for me to help you construct it, since it is truncated in the query plan XML, but you can follow the general guidelines here). A filtered index might also help if the majority of rows have NULL or NOT NULL for the column referenced in that operator.

July 13, 2016

No Comments