report performance question

[aetcognos]

Hello gurus,

I have a cognos 10.2.2 report studio question. 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

[Andrei I]

1) Can you use a custom SQL in the query instead of RS Query Designer?
2) What is your DB source?

[Francis aka khayman]

how about joining the two queries instead?

[aetcognos]

1) Can you use a custom SQL in the query instead of RS Query Designer?
2) What is your DB source?

1) Can you use a custom SQL in the query instead of RS Query Designer?
2) What is your DB source?

Yes, we are trying to not use Custom SQL, because we need to pass parameter map calculations to the query, but if that is the only option to speed up the query, then possibly we could try that option.
Oracle is the datasource.

Thanks
Derek

[aetcognos]

how about joining the two queries instead?

Thanks, we did try that option and it is about the same time as the in clause.

[Andrei I]

Oracle is the datasource.

If you did a Cognos "usual suspects" check ( everything is pushed to the DB - no Cognos engine processing )  then capture both SQL queries for Query 2 and Query 3 and dive into Oracle Performance tuning:
E.g. https://stackoverflow.com/questions/47906949/oracle-sql-how-to-optimize-in-uncorrelated-subquery