Ideas To improve the runtime of report at report level?

[ejaycognos]

Ideas To improve the runtime of report at report level?


using UNion query of combinng 3 query in a crosstab with 5 measures. All the calculations and filters are done at union query only , as at individual query it throws some error , thus doing it at union query.

The execution time is very slow.how to improve it ?

 

[blom0344]

Probably the most effective route is to create a database object that constitutes the union. If you can create a database view / cognos sql object , you can then decide whether any duplicates need to be removed from the set.
'union' removes duplicates, but is costly as it involves sorting the data.
'union all' is more effective as it takes for granted no duplicate rows can exist between the 3 seperate sets, so no ordering is required.

With large datasets ordering (sorting) data is quite often the performance killer

Ties Blom

 

[ejaycognos]

Thanks blom for the reply.

I have only access at report studio level,how to create a cognos sql object? I have not worked much on these union , pls guide.

Also, in Union I set the properties to Remove duplicates , so still I need to consider Union All ?

This is what I done in my rport :
I combined all the 3 Queries Q1 + Q2 + Q3 into Q4 or do I need to group Q1+Q2=Q3 U Q4 = Q5 - Union query .

I tried either way keeping all the calculations and filters at union query or tried keeping the calculations and filters at seperate query and then unioned them.
Either way is slow.
(Needless to say sometimes I get CCL out of memory error or DMB cube build error has exceeded its limit.)

All measures are calculated field ,I kept the aggregate func for all measures inside the calculation.

Thanks

 

[blom0344]

1. You can have more than 2 queries unioned into one new query..

2. The setting 'remove duplicate' will lead to a UNION instead of an UNION ALL.
Example: suppose each query would relate to a specific country and the country code is fetched, then no duplicates can ever exist. UNION ALL is then in order and this may be a lot faster as no ordering of the dataset is required (ordering = needed to remove duplicate records)


Ties Blom

 

[bicog8]

Have you tried this :

Q1+Q2=Q3 'Union all' Q4 = Q5 - Union query .

 

[ejaycognos]

Thanks blom for clarifying the UNION All query.
I guess then using UNION ALL may improve the performance , I am going try with union all.

Thanks bicog8 for your reply .. no I haven't tried this , I will create a test report and implement this scenario and will inform how it worked.

 

[ejaycognos]

union all also takes some time.

or should I contact cog support , to clear some space in temp folder and increase its size if needed?
I did some reasearch in cog document and they told to check on the available space in temp folder.

 

[ejaycognos]

got it works fine