We have several Sybase databases in our environment, based on the native Sybase Adaptive Server (CT-15) driver.
Query performance is terrible, even on some single table queries with a simple date between WHERE clause.
I checked the generated SQL and noticed that Cognos automatically adds "FOR READ ONLY" to all queries. I never saw this before, so I Googled it. It seems that "FOR READ ONLY" instructs Sybase to use database cursors and shared locks to execute the SELECT statement. That would explain the poor performance. Is there a way to configure Cognos not to use the "FOR READ ONLY" clause?
Quote from: RubenvdLinden on 14 Mar 2017 04:01:03 AM
We have several Sybase databases in our environment, based on the native Sybase Adaptive Server (CT-15) driver.
Query performance is terrible, even on some single table queries with a simple date between WHERE clause.
I checked the generated SQL and noticed that Cognos automatically adds "FOR READ ONLY" to all queries. I never saw this before, so I Googled it. It seems that "FOR READ ONLY" instructs Sybase to use database cursors and shared locks to execute the SELECT statement. That would explain the poor performance. Is there a way to configure Cognos not to use the "FOR READ ONLY" clause?
If you look at the data source connection to Sybase in your Cognos Admin console, what is the transaction isolation level set to be currently? Can you set it explicitly to Read Uncommitted and see if that fixes the issue?
Cheers!
MF.
Sorry for my late reply.
I have already tried the 'Read Uncommited' isolation, but I didn't notice any performance improvements.
However, the 'Read Uncommited' isolation did have some stability issues so I reverted it to 'Use the default object gateway'.