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

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 .

tmpEBCC.pesession (139.3 kB)
avatar image By Marekx371 1 asked Oct 12, 2017 at 08:55 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

2 answers: sort voted first

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.

avatar image By SDyckes2 96 answered Oct 13, 2017 at 06:51 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

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.

avatar image By Marekx371 1 answered Oct 13, 2017 at 07:11 PM
more ▼
(comments are locked)
avatar image SDyckes2 Oct 13, 2017 at 07:55 PM

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?


avatar image Marekx371 Oct 19, 2017 at 09:12 PM

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.

10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

We are Moving!


Follow this question



asked: Oct 12, 2017 at 08:55 PM

Seen: 62 times

Last Updated: Oct 19, 2017 at 09:15 PM