Slow performance???

[PlayCognos]

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

[blom0344]

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)

[PlayCognos]

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

[rockytopmark]

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.

[PlayCognos]

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

[rockytopmark]

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

[blom0344]

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?

[PlayCognos]

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