How can i make this query process faster?
It depends on a number of factors, not least whether you are looking to make the COUNT(*) query faster, or the commented-out code. Simply counting the number of rows is a very different proposition.
From the information provided, it's only really possible to make some general observations about the query and plan presented:
- Importing the plan from SSMS leaves out important information (for example, notice the Table I/O tab is greyed out). Running the query directly from Plan Explorer will populate that information, and more besides (especially if you are running the Pro version). This may help you identify the bottleneck.
- The query should benefit from parallelism. Check your server configuration to see why you're not getting a parallel plan here.
- Filtering and aggregating early is usually better. Try rewriting the aggregate and especially the OR predicates to promote this. Also try rewriting the outer joins as an inner join unioned with the appropriate not exists query.
- Consider indexed views and partial aggregates to reduce the row counts earlier in the plan. Indexed views do not support outer joins, but it is still often possible to rewrite the query to make use of indexed views for the bulk of the work.
- Avoid hash joins with large and similarly-sized inputs. Hash join works best when the build input is small compared to the probe input.
- Check for hash spilling while this query is running. The estimated row counts are pretty good, but your server and configuration may not allow for all the hash table memory this query would like. Spilling to tempdb can be very slow, especially if recursive spills are needed.
- Ensure you have suitable covering indexes for the query predicates. The current plan is limited to index filtering on DATAREADID, with all the date/time predicates performed by a filter after all the joins have been performed. This will likely require indexing changes and a query rewrite. Even so, the size of the data after the date/time filters is still quite large, so the benefits may be limited.
- In the longer term, if you are unable to rewrite the query or improve indexing to get good performance, consider upgrading to at least SQL Server 2012 to assess the benefits of using columnar storage. This will require Enterprise Edition, and 2014 is needed to benefit from batch-mode outer joins and writable columnar storage. Nevertheless, the performance improvements for this sort of query can be very impressive.