Slow report with stored procedure

[xxyyzz49]

I have a report that uses a stored procedure that runs very slow. The stored procedure executed through SQL Server Management studio runs in less than 2 seconds and returns about 2000 rows, 16t columns, of data. The table is indexed and the SP only joins one other table with < 600 rows of data. The SP takes two parameters that are date time type. My original report has the sub report invoking the SP so to prove that sub reports or my grouping was not the issue I created a simple report to only list the returned record set with no additional processing and it is just as slow. I have another report with a comparable SP and processing by CR and it runs quickly comparable to the times seen just running the SP in SSMS. The report uses an ADO connection and I get similar results using RDO and ODBC.

Given that similar reports run well this one is driving me nuts, short drive. Appreciate some new thinking on this problem.

 

[xxyyzz49]

The DBA cleared the cache with DBCC FREEPROCCACHE and the SP now runs instantaneously. The results were remarkable.

The apparent problem most likely had to do with parameter sniffing. There are numerous posts about the subject and the problems are easily avoided. Two links follow:

[URL unfurl="true"]http://www.sqlpointers.com/2006/11/parameter-sniffing-stored-procedures.html[/url]
[URL unfurl="true"]http://www.simple-talk.com/sql/t-sql-programming/parameter-sniffing/[/url]