PLEASE HELP :-) How can I reduce the execution time of this query, currently taking 13 seconds..?

Sqljnr81 2015-03-17 15:37:10

Hi all, please can anyone shed any light on how I can improve the execution time of this query? A lot of the cost seems to occur for the index_seek on t_Request table, which returns the most amount of rows. I was trying to re-write the query so that it does the join for t_Response earlier in the process, thus negating the need to check the t_Request table. All the stats are up to date, I am not sure what further indexes could help, and surprise surprise when this is run on a dev server it takes milliseconds to execute, albeit the number of rows in the table are much smaller. Please help! Thanks in advance.
SQLkiwi 2015-03-18 07:46:41
The first thing I would check is that you're compiling and running this query with trace flag 4199 enabled. There have been a number of cardinality estimation bugs using date functions, conversions, and getdate with inequalities.

Alternatively (or as well) compute the date boundaries before the query starts and use a sniffable construction in the query itself. For example, if you use local variables to hold the date boundaries, you'll need OPTION (RECOMPILE) on the query to make the literal values available to the optimizer.

I'll have another look at the plan and query after you've checked those suggestions out.