split SELECT statements is giving me a splitting headache!

[cognosfreelancer]

I have noticed that whenever a report is written that queries against multiple tables in the FM model, ReportNet unfortunately generates several SELECT statements in its native SQL (Oracle)

For e.g. if you have a report that queries against 3 tables of which two are inner joins and the third a left outer join you see multiple statements being generated.

This is a huge problem as none of the filters are applied in the WHERE clause of the native SQL. These are applied only in Cognos SQL.

We end up retrieving huge entire tables from the database and then applying filters.

NKT

[BIsrik]

i don't see any problem with respect to performance having multiple selects. Lets take ur eg. if two tables are inner joined and third is outer joined then, even at the database level, it has to read all the records of the outer joined table and compare it with the inner joined query. RN does the same and even helps in improving performance. By having multiple selects it just retrieves the resultant of the inner join and outer join and then combines it locally, thus reducing the load at the database.

Regarding the where clause, use as much as database supported functions itself. If use the CRN functions it sometimes does that at the cognos sql side.

Srik

[cognosfreelancer]

Hi Srik

Wish it could be that simple.

The where clause does not contain anything that is not native to the underlying database.

Also I disagree with your statement that multiple SELECT statements that ReportNet issues actually improve performance.

It would if the SPLIT SELECT statements actually carry the filter with them.

Within Oracle the way a left join would work is to retrieve all rows from the outer joined table (with any restraints put in place by the Where clause) and all matching rows from the other table (again with any restraints put in place by the Where clause).

This is where ReportNet is failing in a major way: not applying the where clause in its split statements.

NKT

[rmark17]

You can usually suppress the query "stiching" behavior (believe it or not, an attempt by RN to optimize your query) by changing the table relationships in FM to 1:1 or 1:0. Your meta data is not exactly correct but your SQL will be better.   

[CoginAustin]

"Your meta data is not exactly correct but your SQL will be better.  "

This is a strange statement. how can it be better if you need a LEFT JOIN and used a 0:1 and now it is not there?