SQL running twice (ReportStudio)

[msnaidu]

hi,

I have created a complex structure for the Report in-order to meet clients requirement.
The Report is having multiple Tabular_Sets also i have writen SQL using Tabular_SQL.
The Problem is that when i run the report The Tabular SQl(Rate) and Tabular SQl(GRate) are running 20 times each(on total 40 Query).Due to which report Performance is very Poor.

Can anyone tell whts the reason and how to optimize this?
The Structure of the Query Explore in ReportStudio is something like this:
Query1
1-- Tabular Set1
11- Tabular Model

12- Tabular Model

121-- Tabular Set 2

1211- Tabular MOdel

1212- Tabular Set

12121- Tabular Set(Dim1)

121211- Tabualar Model
1212111- Tabular Model
1212112- Tabular Model
- Tabular SQl(Rate)
1212113- Tabular Model
- Tabular SQl(GRate)

121212- Tabular Model
1212121- Tabular Model
1212122- Tabular Model

12122- Tabular Set(Dim2)

121221- Tabualar Model
1212211- Tabular Model
1212212- Tabular Model
- Tabular SQl(Rate)
1212213- Tabular Model
- Tabular SQl(GRate)

121222- Tabular Model
1212221- Tabular Model
1212222- Tabular Model


Regards,
mk

 

[almeids]

Try creating 2 queries, put your Tabular Sql (Rate) in one and (GRate) in the other, and replace each of your current tabular sql objects with a tabular reference to the appropriate query.
Steve

 

[msnaidu]

Hi Steve,

Thanks For your Suggestion.
It helped me in improving the performance ,as now the Report is running bit faster but Still i am having same problem of generating all the Query for Rate and GRate for each CrossTab i am using The Query1 (Query 1 is having same structure as i posted in my previous reply)
Currently i am using 2 Conditional Block and within each Conditional Block i am using two CrossTab but both the Crosstab points to the same Query1.
So in this case total 2*2 = 4 CrossTab.AS i am having 4 Tabular SQL for a QUERY1.(now pointing to Reference Tabular SQL)
so now on total 4*4 = 16 SQL plus as Reportnet Repeating the SQL for each Query(here i am having 1 Query's:Query1) so now on Total 16 + (4*1) = 20 SQL are getting generated when i run the Report first time.

Is there way to stop the Repetition of the SQL's, because when i am creating another QUERY 2 of same structure the number of SQL is increasing to 40.
and Is there any other way i can improve the Performance?

Thanks and Regards,
Meera

 

[almeids]

Meera,
It's not clear to me from your response that what you tried is what I suggested; you mention "4 Tabular SQL for a QUERY1" but my suggestion was to create a query 2 (for example) consisting of the Rate tabular sql and a query 3 with the GRate tabular sql, then replace all occurrences of tabular sql in query 1 with tabular references to either query 2 or query 3. My understanding is that each tabular sql will only run once and that a tabular reference will reuse the result set without hitting the database again.
Looking at your original structure, each run of your query implies 17 database hits from tabular models and 4 from tabular sql; my suggestion only helps eliminate the 4 tabular sql hits, and only in subsequent queries. The other 17 can't be avoided assuming they are all unique and necessary to the report logic. You'll also have to contend with local processing at the query level (at least) to generate the crosstabs, which I don't think can be avoided.
Something else you hinted at was that the queries for crosstabs in all conditional blocks were still running (I think I may have read somewhere that that behavior has changed for the better in Cognos 8). Depending on your report layout and what you are trying to accomplish with the conditional blocks, you may be able to use conditional layouts instead which I think (sorry about all the uncertainty!) only run the queries which are needed.
good luck,
Steve