report performance question

[aetcognos]

Hello gurus,

Sorry for posting this here, but looks like this is a more active group, and we will be moving to 11.1 in the next couple months anyway, so we have the same performance issue there too.

I have a cognos 10.2.2 report studio questionquerying oracle 12c db. I have 2 queries, one of which is used as a subquery for the 2nd query with an in clause. Now if i run the 1st query, it returns 125 rows in 30 seconds, and if I use those values in the in clause of the 2nd query, that query runs in like a minute.

But I need it to be dynamic, so now if I use the 1st query in the in clause of the 2nd query, the run time increases from a minute to 2 hours. Is there a way we can force the inner query to run before the outer query runs, to make it run faster, or is there a better way of doing this. Please let me know.

Query 1
select col1 from table 1 = 125 rows
run time 30 seconds  :)

Query 2
select col2, col 3 from table 2
where col1 in (the 125 values from Query 1 above)
run time 1 minute  :)

Query 2
select col2, col 3 from table 2
where col1 in (select col1 from table 1)
run time 120 minutes  :'(

Is there a way to make this query run faster.

Thanks
Derek

[BigChris]

Can't you join the two tables in Framework Manager?

[aetcognos]

Can't you join the two tables in Framework Manager?

Oh those are not the exact queries :)

I just simplified it to the bare minimum. The 2 queries are actually 2 queries using different fact tables, with date filters and such. So I cannot do the join in FM.

[adam_mc]

In the past when I've had this problem, rather than setting up a filter subquery in the second query, I have found it to perform significantly faster to create 2 separate distinct queries in Cognos and then create a 3rd as a result of an inner join of the first two queries.

Hope this helps.
Adam.

[aetcognos]

In the past when I've had this problem, rather than setting up a filter subquery in the second query, I have found it to perform significantly faster to create 2 separate distinct queries in Cognos and then create a 3rd as a result of an inner join of the first two queries.

Hope this helps.
Adam.

Yes, we have tried that approach too, and it takes about the same time as in clause.

[dougp]

Is all of the data available through the same data source?  In other words, who is doing the work -- the database server or the Cognos server?


Is Query2 filtered appropriately, or is it:

Query1
select col1
from table1
where filter = 'small'

Query2
select col2, col 3
from table2
where col1 in (select col1 from table1 where filter = 'small')
   (and no other filters -- so the IN is comparing against billions of rows)

I have one report that is like this.  There is no way to make it faster and still meet the logic requirements.  The users accept that the report is slow on the basis of how much benefit it provides them.


Generally, I agree with the approach presented by adam_mc:

Query 3:
select b.col2, b.col3
from Query1 a
  inner join Query2 b on b.col1 = a.col1

...where Query1 and Query2 are filtered well.


Another thing to consider is database partitioning.  Is your database partitioned?  Does your report take advantage of the performance gains that can provide?