How can eliminate these two sort that are very expensive ? and reduce cost on index seek

Marekx371 2017-10-12 20:55:13

So I have this proc that you pass parameter in as a string then it split those values into table variable and joins to this view which returns a select result which later is used at a website.
but its running slow and i cant figure it out what to do with it .

SDyckes2 2017-10-13 18:51:49
The View you are utilizing is complex, the slow down could be any number of factors. Possibly breaking the View into smaller chunks may be a solution or checking that the statistics on all the tables are up-to-date. But without seeing an actual plan with the parameters being passed it is difficult to guess where the slow down is occurring.
Marekx371 2017-10-13 19:11:03
parameters are always different, sometimes it could be one itemID, other times it could be 100 items ids, same thing with a site parameter sometimes its only for 1 site other times its for 10 sites. its all depends on the user input. yes checked the statistic recently few hours ago and they look up to date, cant break the view because of the business module but with my recent changes it runs quite fast, sometimes 10 seconds to get result set other times 3 seconds. I eliminated all the key look/RID look ups and most of the tables scans on huge tables and because of the parameter sniffing I used a inner variable to past into my filter statements and it sure did help, So waiting for users feedback.
SDyckes2 2017-10-13 19:55:11
That sounds like you have hit the major slowdown potentials and addressed them.
I do see you have a Cluster Index Scan on the Site table, PK_Sites, with an estimated 46285 records passing into a Sort operation then into a Merge Join, this could cause some slowdowns if the actual number of records is high enough.
If it runs slow again can you provide an actual plan for the slow query with parameters?

Thanks!

Marekx371 2017-10-19 21:12:31
So far it has been running good, and no users complain so I am leaving it at this state. Could you take a look at my other task I a highly expensive hash match.

https://answers.sqlperformance.com/questions/4762/how-can-i-reduce-the-host-of-hash-match.html