"Scott" wrote:

> I have spent some time writing a complex stored procedure for use in a
> Crystal Report. My app uses Crystal as supplied with VS.NET 2003. The SP
> accesses about 6 tables, several OUTER JOINs, a WHERE BETWEEN clause on a
> datetime column, and a GROUP BY to SUM severals columns from about 100,000
> rows into about 100 rows.
>
> From MS Access and the SQL Server tools, the SP was performing well
> considering all it had to do, then I put it into a report. The report ran up
> to ten times slower than the SP alone. SQL Profiler proved this, and showed
> when run from the report it was doing up to 30 times more reads than when it
> was called directly.
>
> More digging, and I eventually noticed in the Profiler that Access will
> issue another TSQL statement immediately before it executes the SP. It is:
>
> SET NO_BROWETABLE OFF
>
> Apperently, this is an undocumented feature, but I did find a little about
> it, relating to FOR BROWSE clauses. However, when I add that statement to my
> SP, the report runs almost as fast as the SP on its own. Great news, but
> why? Can anyone shed any light on this?
>
> Do I need to add that line to every stored procedure I call from Crystal???
>
> Thanks,
> Scott.