SQL Stored Procedure query takes too long

[thopper]

I have a SQL stored procedure that runs in a reasonable time but when it's used by a report the execution time increases enormously.

for example, the sp returns 165,000 rows in 1 min 12 secs;
a report that simply runs this sp using a SQL object query into a list container returns the same 165,000 rows in 17 mins 20 secs!

Wow! This is slow!

Anybody got any ideas why this might happen?

I'd be grateful for any help.

Thanks.

[blom0344]

1. How are you so sure the SP returns that many rows in such a short period. Did you test it by redirecting the output to a table?

2. I expect that the Cognos server will have a hard time processing 165,000 rows of output.

A better strategy would be to design an ETL proces that directs the SP output to a table and use this one for Cognos purposes. I assume nobody is interested in scanning 165,000 rows in a report, but this amount of data may well be the basis of a meaningful condensed report..