SQL query in Excel takes far longer when using ? to get a cell value than with value specified in query

I've written an Excel query of a SQL Server database to display results from a complex view. The view uses a join on several tables of less than 10K records, returns about 620 rows and takes 1-4 seconds to execute. Always. Add a WHERE clause to limit the ID sought to a specific value, and it selects between 1 and 100 rows, depending on the ID sought, and it always executes in less than 1 second.

However, modify the WHERE clause to use ID = ?, and point it at a cell. Now the query takes 5-30 seconds to execute if less than 30 rows are selected, 2-4 minutes if 30-50 rows are selected, 10-15 minutes if 70-1开发者_JAVA百科00 rows are selected, and times out if more. These times are consistent.

Why does an Excel query take longer if a cell is requested using a ? parameter rather than a given value? And why do small queries finish fast, a little larger take longer, and "large" queries take so long?

This could be a 'parameter sniffing' problem. Try including the line:

OPTION (RECOMPILE)

at the end of your SQL query.

There is an article here explaining what parameter sniffing is: http://blogs.technet.com/b/mdegre/archive/2012/03/19/what-is-parameter-sniffing.aspx