A filter with the attached query causing slowness and performance issues

SQLDB 2015-04-09 13:13:25


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.

SQLkiwi 2015-04-09 14:04:37
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).

SQLDB 2015-04-09 15:29:00
@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. .
SQLkiwi 2015-04-09 16:11:42
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 🙂
SQLDB 2015-04-09 16:25:11
@SqlKiwi, Absolutely correct. Agree with you! , we have already propose upgrade changes. Thanks!