Burst report performance

[Bindiya]

Hi all,
I have a report which is taking long time when bursted. Below is its behavior. The report basically displays information of a member. The prompt page has State, District, and member prompts.

When report is run on demand by selecting a member from prompt - it takes 20 sec to display result.
When report is bursted by selecting 2 members - it takes 40 secs and generates 2 PDFs, one for each member.
When report is bursted by selecting 10 members - it takes 7mins.
When the report is bursted by selecting one state and one district - It took 40 mins to burst the first PDF and even after 10min the second one did not get bursted. (There are around 450 members for the selection made. THIS IS THE ISSUE.

Please help..

Thank you.

[Bindiya]

Some more details about my report.. The query is based out of tabular SQL and the report contains 10 different lists sourced from this query. Also, this query is my burst and recipient query too.

I read some where that if the burst/recipient query is different from list query then for each burst key in burst query, the list query will be executed. But if there is only one query for all these, then it would be executed only once. Why is my report taking so long even though I had only one query?

My TSQL is little complex and takes 6 min when run against Teradata (for 450 members) and 5secs for 1 member.

Appreciate any suggestions.

[Bindiya]

I noticed one thing here, on running the report (in background) for one state and one district (which has 450 members), it took 38 min to generate the o/p which is one single PDF with info of all 450 members (note that I have not bursted this time).
This makes me feel that when report is bursted, it is first trying to generate the same information and then filtering for particular burst key. Hence, for each PDF generation, it was taking 40 min.

Is this what is actually happening? How to make it run only for specific burst key instead of running for whole thing and then applying the burst key filter?

Please share your thoughts.

[velniaszs]

We had the same issue bursting reports from Teradata more than 1000 outputs.

There are few ways of making it usable.

1) Make your report Master Detail driven.
2) Use as little queries as possible
3) In Teradata use recomended statistics for report queries generated
4) If you are using Teradata views rename them to something like this:
RENAME VIEW DB_NAME.VIEW_NAME (FIELD1, PK_ID_FIELD_NAME) AS
SELECT
T1.FIELD1,T2.PK_ID_FIELD_NAME
FROM
DB_NAME.PARENT_TABLE T1,
DB_NAME.CHILD_TABLE T2
Here is the reason why : http://mincingthoughts.blogspot.co.uk/2012/11/gotcha-teradata-views.html
5) Try removing all charts see how much performance you gain.
6) Try recreating your report from this example:
http://www.ibm.com/developerworks/data/library/cognos/reporting/performance_and_tuning/page582.html

[velniaszs]

Another hint: Bursting works fast only when you report from single list and your bursting is configured from the same query.
Then it will fire 1 SQL query, and generate N outputs for each burstkey.
As soon as you have Crosstab,chart, second list It will fire SQL query for each output.

Also you mentioned that you use 10 lists for the report. That is bursting killer. Try using only 1 lists see how much performance you gain.

[Bindiya]

Thanks a lot for your reply, Velniaszs. Here are some more thoughts.

There are no multiple queries in my report to make it master-detail driven. Of course, I can break the query to have a master-detail format, but having a single query is more better than having multiple queries with master detail setup right?

My teradata view is a simple select without any joins and so no column ambiguity as explained in the link.

The requirement is to display the data in separate sections and so I used 10 lists. So, I really can't getaway with those. There are no charts BTW.

Finally, till now I was in an assumption that having multiple queries with master-detail setup will run the detail queries for every burst key from master and so would take time. But now I am understanding that even though I have single query (which serves as burst, recipient and source for all lists), it doesn't mean that query is run only once. It runs multiple times for each list present in the report. Correct? I am lil confused..

[velniaszs]

If you have 1 query you don't want master detail driven report. Well there is no 100% answer but for teradata it is usualy better 1 query.
Try using the suggested view replace for the following reason: Teradata fails to identify views columns with framework manager and for each query it starts with
help table;
help column for table;
...
then the query
(try checking statistics while you run the burst job, you will be surprised)

For the second part yes usually even though you run all lists from same query Cognos identifies it as separate queries and runs them separatly unless it hits the cache
(http://pic.dhe.ibm.com/infocenter/cbi/v10r1m0/index.jsp?topic=%2Fcom.ibm.swg.im.cognos.ug_fm.10.1.0.doc%2Fug_fm_id19403QueryReuse.html )
It is hard to hit Cognos cache. From what I found its best to add data items to properties of list of all data items used in first list and make appropriate options to reuse caching.


In the end what we ended up doing is moving away from reporting directly from Teradata, to Cognos Tranformer cubes.

[Bindiya]

How to generate the log file that displays the queries that are executed against database to fetch the report o/p?
We tried "Enable query execution trace" setting but could not see any log files in the path as specified in the below link.
http://pic.dhe.ibm.com/infocenter/caapps/v10r1m0/index.jsp?topic=%2Fcom.ibm.swg.im.cognos.ug_cra.10.1.0.doc%2Fug_cra_id13929ConfigSettings.html

Or is there any other log that would help in debugging how the queries are executed in the background to fetch the report o/p?

[Bindiya]

We are on Cognos 10.1.1..

[velniaszs]

Well what we use its not ideal because it shows only info what is being run for 1 table, but it gives the idea how many hits for bursting report it does.

First collect statistics on primary key, and any other columns that needs to have it, then run the burst job and with this query you can see live how many and what queries are being submited :
SELECT t3.queryid
, t3.collecttimestamp
, CAST(t3.SqlTextInfo AS VARCHAR(16384)) AS SQLSubmitted
FROM dbc.dbqlsqltbl t3
WHERE queryid IN
( SELECT t1.queryid
FROM dbc.dbqlogtbl t1
, dbc.dbqlobjtbl t2
WHERE t1.procid=t2.procid
AND t1.queryid=t2.queryid
AND t2.objectdatabasename='YOUR_DATABASE'
AND t2.objecttablename IN ('YOUR_TABLE')
AND objecttype NOT IN ('col','db')
AND StartTime >= '2013-08-01 15:06:00.00'
GROUP BY 1)
ORDER BY 2 DESC;