If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Sybase query performance [Cognos 10.2.1]

Started by RubenvdLinden, 14 Mar 2017 04:01:03 AM

Previous topic - Next topic

RubenvdLinden

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?

MFGF

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.
Meep!

RubenvdLinden

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'.