Local processing vs Database processing

[dech008]

Hello everyone,

I have a confusion about Local processing and Database processing.
I have seen it written many time that we should be using the Database only for Query processing. but i never see the use of it.

Say for example i have two tables and i am using 1:n joing for them. I put query iteams from both the table to my model query.
And now after testing if i see the COGNOS SQL and NATIVE SQL you will find the COGNOS SQL caries the joining of two tables but Native SQL has two Select Statements, each for a table.
So even after setting it as Database processing still it is going to be processed locally.
So what is the use of Database Processing.

Can sombody give me on what condition would the local processing and database processing be applicable.
(I already know that i need local processing for any Cognos function)

Thanks & Regrards,
Debashish

[MFGF]

i see the COGNOS SQL and NATIVE SQL you will find the COGNOS SQL caries the joining of two tables but Native SQL has two Select Statements, each for a table.

Hi,

If you have two tables from the same database, represented as query subjects, and you have defined a relationship between them with cardinality 1..1 <--> 1..n you should see the tables joined in both the Cognos SQL and the Native SQL. If you don't, it means you probably have something seriously wrong in your model.

You would only normally get two separate database queries where either:

a. The tables are from different data sources, or
b. You have a stitch query where the model thinks it is querying two fact tables.

Cheers!

MF.

[dech008]

Hi MF,

So that mean Database and local processing would give me the same SQL query (same database)
but as per my query processing option in report studio query processing will be done at cognos server or database server.

If this is not correct............
Would you be able to provide me with an example when query mode = database only can give better performance over local processing.

Thanks & Regards,
Debashish

[MFGF]

Would you be able to provide me with an example when query mode = database only can give better performance over local processing.

If you specify "database only" processing, you are disallowing the Cognos server from doing work locally that it believes would be optimal for query processing. Sometimes this can mean you simply can't do some things - eg unioning two tables from two different databases and sorting the result set - this would necessarily involve work being done on the Cognos server. Having processing set to "Database only" in this situation would prevent this from running.

In an ideal world, all processing would be possible in the database, and the database optimizer would figure out the best way to execute queries and return the results in the most efficient way. Sometimes life isn't like this, though, and you can't avoid using local processing.

Here are some other posts around this topic, which may help:

http://www.cognoise.com/index.php?topic=8502.0
https://www.ibm.com/developerworks/community/forums/html/topic?id=77777777-0000-0000-0000-000014635621
https://www.ibm.com/developerworks/community/forums/html/topic?id=77777777-0000-0000-0000-000014625407
http://www.cognoise.com/index.php?topic=26100.0
http://www.cognoise.com/index.php?topic=14268.0
http://www.cognoise.com/index.php?topic=17939.0

Cheers!

MF.