July 18, 2013 at 3:05 pm
Much like your other post, if you want help with tuning you need to give us enough information to work with. Follow the advice about what to post from the link Gail posted in your other thread.
Actual execution plan
Table definition
Indexes definition
Also when posting code it will make it a lot easier to read if you can use the IFCode shortcuts. They are over on the left when you are posting. If you use the CODE one it will keep your formatting and coloring in addition to putting it inside the nice little scrolling window.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 18, 2013 at 4:26 pm
As Sean says, there is not much we can do with the query alone.
However, this:
and DATEDIFF(d, @SDate, a.dDateCreated) >= 0
and DATEDIFF(d, @EDate, a.dDateCreated) <= 0
is better written as
and a.dDateCreated >= @SDate
and a.dDateCreated < dateadd(DAY, 1, @EDate)
As this permits use of any index on dDateCreated. Although, guessing from the table names, the effect may be limited.
You could change UNION to UNION ALL, as UNION implies DISTINCT. Then again, if you need duplicates deleted, you should keep UNION.
I would suggest that you run one of the UNION queries at a time, to isolate the slow part.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 18, 2013 at 4:29 pm
I forgot: Change SET ANSI_NULLS OFF to ANSI_NULLS ON. ANSI_NULLS OFF is a legacy setting, and there are features in SQL Server that are unavailable when this setting is OFF.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 3 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply