isuckatsql (10/4/2013)
In testing the sub queries proved faster than Joins, as did the MAXDOP and NOLOCK.The data returned, in before and after testing of the above hints, was the same.
This query with the count(*) included takes 300 ms to process 700k records.
Using the non count(*) methods, it takes 20 ms to process 700k records, but i lose the total count.
20ms is pretty fast IMHO 🙂
I have researched a ton of data on the internet regarding query performance, and hired a couple of very senior SQL DBA's to also help with query performance and optimization, and this is what i have ended up with.
Thanks for your feedback !
So you have spent thousands of dollars hiring consultants to help you with the query and what they came up with isn't fast enough so you turned to an online forum and continue to defend the code that you paid for? I am not a performance expert by any means but there is a LOT of low hanging fruit in that code for improvements. It seems to me that you turned to this forum because you need some help. We can help but you either have to listen to our ideas and move away from your consultant's efforts or you will be stuck trying to sort it out without our help.
I don't who your consultants were but I can safely say that I would listen very closely to anything Gail has to say when it comes to this type of thing. She can make this thing scream if you work with her.
_______________________________________________________________
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/