A filter with the attached query causing slowness and performance issues


Attached query/query plan is a part of filter we use in our SSRS reports i.e. it is the query for clicking on a report(later comes the data fetching query part).

We have witnessed this running very slow.

Have tried updating stats and creating missing indexes, but seems no help.

Please help in understanding the cause and what can be done here to improve the performance, as it takes around 10 minutes to display the results when run on SSMS.

@SQLkiwi, new plan attached link text

Thank you.

avatar image By SQLDB 60 asked Apr 09, 2015 at 01:13 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

1 answer: sort voted first

Create an index like this on #temp after the insert:

 CREATE INDEX IX_sitidfk_soridfk
 ON #temp (tsosite_sitidfk)
 INCLUDE (tsoorganization_soridfk);

And this index on [tblWooWorkorder]:

 CREATE INDEX tblWooWorkorder_bcoIdFk_SitIDFk
 ON [tblWooWorkorder]
     (wooProfitCenter_bcoIdFk, wooSite_SitIDFk);

Please capture a new actual plan with those indexes in place and edit your question to add the new query analysis file (please do not add it as an 'answer).

avatar image By SQLkiwi ♦ 6.6k answered Apr 09, 2015 at 02:04 PM
more ▼
(comments are locked)
avatar image SQLDB Apr 09, 2015 at 03:29 PM

@SQlkiwi, Thank you! From 10 minutes it reduced to 2 mins after creating index as said above. Don't know if there is any scope to drill down further. Attached is the new query plan as requested. .

avatar image SQLkiwi ♦ Apr 09, 2015 at 04:11 PM

There are likely more improvements to be made, but that work would require more access to the database data and design than can reasonably be provided here. Even so: You should apply SP4 for 2005, then consider upgrading to a more recent version as soon as circumstances allow. That won't magically fix all problems, of course, but some of the suggestions I would have made require a more modern SQL Server version than 2005 :)

avatar image SQLDB Apr 09, 2015 at 04:25 PM

@SqlKiwi, Absolutely correct. Agree with you! , we have already propose upgrade changes. Thanks!

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.

Follow this question



asked: Apr 09, 2015 at 01:13 PM

Seen: 71 times

Last Updated: Apr 09, 2015 at 04:25 PM