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 !