Cognos report running 4-9 times slower than generated SQL run through DB cmd

[jackg_tor]

Hi,

I've often found that the Cognos report seems significantly slower than the generated SQL run directly against the DB but I have a recent case where it's particularly bad.  My report generates 2 rows + a summary row comprising around 40 values, in total.  The generated Cognos SQL is essentially the same as the generated native SQL and the native SQL produces all the numbers in the report so I don't think that there's a lot of local processing happening.  Running the query takes 6-7 seconds with the DB2 command line but 20-70 seconds when run by the Cognos report.  Does anyone know what can cause this, how to find out where Cognos is spending the time or how to improve the Cognos time?

I'm running Cognos Analytics 11.0.1 on Linux with DB2 LUW 10.5.3.

Thanks,
Jack Goldstein

[AnalyticsWithJay]

The problem with the generated SQL shown in Cognos is that it's not the exact SQL that's sent to the database.

I would always start by capturing the exact native SQL in Cognos (via Native SQL trace) that's sent to the database and run it for a true comparison. Are you running in DQM or CQM?

You can take it a step further if needed with a RSVP trace, but that's not needed at this point in my opinion.

Things such as conditional formatting, local joins, display variables and render blocks will add some extra time. If you're using a chart or a crosstab and using relational data, Cognos will have to spin a cube on the fly to supply the pivoted data. This will add overhead as well. If you're outputting to XLS or PDF, you can expect a couple of added seconds.