I am running this on Report Studio. Here is the Cognos SQL:
select distinct
ABC.DEPARTMENT as Department,
PQR.TEST as Test,
XYZ.EMAIL as Email
from
(
DS1..OMGR.ABC ABC
join
DS2..OMGR.XYZ XYZ
on (ABC.PID = XYZ.PID)
)
left outer join
DS2..OMGR.PQR PQR
on (ABC.PID = PQR.PID)
It runs forever. But when I run this directly against the database, it takes 0.2 seconds (with this modification):
select distinct
ABC.DEPARTMENT as Department,
PQR.TEST as Test,
XYZ.EMAIL as Email
from
(
OMGR.ABC ABC
join
OMGR.XYZ XYZ
on (ABC.PID = XYZ.PID)
)
left outer join
OMGR.PQR PQR
on (ABC.PID = PQR.PID)
Any ideas why? I am kinda new to Cognos world.
Thank you.
PC
DS1 and DS2 suggest there are 2 datasources involved. Are you sure that the 2nd SQL is not a different one in just addressing 1 database?
With 2 different databases addressed the Cognos server will have to first fetch the data from 2 directions and then perform additional work on the server (which is possibly much more inefficient)
You are right. The Cognos SQL does go through 2 data sources whereas the direct SQL goes to one schema. On the Cognos side the access to data is based on roles. Since the tables needed for this report comes from two different roles, hence two different datasources. Would you have any suggestions how to work around it?
Thank you.
PC
Search this site for information about using parameter maps and session macros to control datasource query sourcing.
I have not done this, but I have seen these topics covered.
The strange thing is that when I remove data coming from DS2..OMGR.XYZ, the report executes in less than a second. This scenario still involves using both datasources(DS1 and DS2). Any clue what could be happening? Here is the Cognos SQL without data from DS2..OMGR.XYZ.
select distinct
ABC.DEPARTMENT as Department,
PQR.TEST as Test
from
DS1..OMGR.ABC ABC
left outer join
DS2..OMGR.PQR PQR
on (ABC.PID = PQR.PID)
Thank you for your feedback in advance.
PC
I'd get the NATIVE SQL from your original report and analyze that, not the Cognos SQL.
Sounds like your modeling has a relationship flaw or some relationship missing altogether
The distinct clause will dictate that the resultset is ordered first (the only way a distinct can work).
Removing data from DS2 would mean only an 'order by' action on the DS1 side (database sort?)
Depending on the data to be processed an 'order by' action can be very much slower if executed on the Cognos server.
Analyzing the native SQL may result in looking at 2 seperate statements (that are both fast) Did you check whether the native SQL is showing 2 seperate statements?
This is the native SQL for the report which runs forever:
select "ABC"."DEPARTMENT" "c0"
from "OMGR"."ABC" "ABC"
order by "c0" asc nulls last
select "PQR"."TEST" "c0"
from "OMGR"."PQR" "PQR"
order by "c0" asc nulls last
select "XYZ"."EMAIL"
from "OMGR"."XYZ" "XYZ"
The native SQL for the modified report without the columns from XYZ is (this one executes really quick):
select "ABC"."DEPARTMENT" "c0"
from "OMGR"."ABC" "ABC"
order by "c0" asc nulls last
select "PQR"."TEST" "c0"
from "OMGR"."PQR" "PQR"
order by "c0" asc nulls last
I still do not know what could be happening. Data from ABC is coming through DS1 and PQR comes through DS2. Both still involve order by conditions.
PC