Can index Scan be replaced with index seek, in order to avoid a huge scan
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.