Cognos sql vs native sql

[dssd]

Please help with respect to performance tuning. I plan to compare cognos sql and native sql. I would try to figure out the difference between the two. But, what do i do with the difference between the two. Next steps?

[blom0344]

The Cognos SQL is basically 'rewritten' by the specific (database) UDA to be compiled for the RDBMS that you use. Cognos offers a glance at the proposed SQL that it will generate, but remember that this is a proposition. The actual SQL executed may differ, or be split according to many variables.

Some of the Cognos SQL may be missing in the native presentation , like summary filters and running aggregates. These may be executed by the server AFTER query execution.

The one and only way to examine the SQL that is effectively used is to run traces on the database. Capturing that SQL may be the first step towards performance tuning, but tuning itself is a field in its own.

Could you tell us more about your issues (if any) and what you  are planning to gain?

[dssd]

Well what i thought that whatever is part of Cognos sql and is not part of Native sql is potenitally being done on the server. And, that it would help if we could move it to the database as wel. but, i wasnt sure of how it can be moved.

[blom0344]

I think that you look at this from the wrong angle. Cognos offers out-of-the-box functionality that is pretty hard to work into database SQL.  Running aggregates are a pretty good example.  DB2 and Oracle have window-based functions that allow computing them relatively easy within a database, but SQL server will offer the same type of function in the 2012 version for the first time.

Prior to applying these functions you had to resort to 'triangular join' constructions that were VERY inefficient.

Summary filters are easier to implement , but may result in having to code 'having' clauses or treating the full SQL executed as an inline view

Keep in mind that the RESULTSET from the executed SQL may just be a fraction of the data that is queried, so that the cognos server itself may only need to process relative few rows