More report columns - impact on performance

[cognosun]

Hi,

Previously we have simple list reports in our module with 50 columns in each of them.

As per our latest requirements we added 50 more new columns to the existing reports ( we added them to same list report thus taking the total number of columns count to 100).

Now we are seeing decrease in performance....when I've talked with DBA...he said that increase in number of columns shouldn't impact the performance.

But I doubt this...as report size now obviously increases ( as 50 new columns pull more extra data ) againt the same unchanged environment ( or JVM Size)

Also with the increase in 50 more columns, more data comes and sits in temp folder..bringing report performance down ( not vast decrease but little decrease in performance).

Can anyone provide more inputs on this scenario ?

[blom0344]

Your DBA is probably right if you just read an additional 50 column from a table that is already adressed. This will only increase IO slightly and make a report a little slower. However, if the column originate from different tables, then the SQL may be less performant, hence slower report.  In general , having to display more data makes a report slower as well, but not in orders of magnitude..

By the way,  it does indeed pay of to post topics that contain relevant information, doesn't it?

[cognosun]

I totally agree with 2 of the cases you mentioned for degrade in performance ( more I/O and more tables/more joins )

A select query just reading more columns won't impact performance is logically correct....but here my tiny concern is we are not dealing with dumping "resultset" onto some 'SQL interface'.

But here a reporing service ( in case of cognos it's 'presentation service') should work 'more hard at stretch' to fetch those extra columns from Cognos server's 'temp directory' and compose them onto report by taking into account, any additional report side formatting ( I mean it can't just dump plane data).

[bdbits]

Can you quantify the degradation - how much longer it is taking? Of course an increase in report content is going to have *some* impact on each step of the report generation process. There are server-side Cognos tracing facilities which can give you pretty detailed information on where the time is being spent. Unless you have server access you'll need a Cognos admin to assist you with this, and the process varies a little depending on the version of Cognos you are on and the type of trace you want to run.

[cognosun]

report with 50 columns is taking 1 min ( html output ) and same report with 100 columns taking 2 mins ( html output).

For say 1000 rows.

[CognosPaul]

My guess is the majority of the loading time is Cognos rendering the HTML. Remember, lists are simply HTML tables. Each text item is wrapped with a span tag. The following is from a report I made using the default List report:

Code Select Expand

<TD class="cRS16 lc" name="cRS16" type="datavalue" uid="16" cid="0"><SPAN class=textItem tabIndex=-1 ctx="5">2012</SPAN></TD>

That's 125 characters. Assuming that all the fields will be roughly that size (not entirely true, of course), and assuming that all characters are worth 1 byte, we can assume 125 bytes * 1000 rows * 100 columns = 12,500,000 / 1024 / 1024 = 11.92 MB. That's not including the HTML for rows (9 byes * 1000 rows = 8ish KB) and anything else on the page. With the various Cognos overheads, lets call it an even 12 MB (although it's probably more).

Personally I think that 2 minutes is reasonable for a 12 meg HTML report.