Kbase P152157: Queries reading 1 million+ records runs slow using versions of DataServer for MS SQL Server later th
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
07/09/2009 |
|
Status: Verified
SYMPTOM(s):
Queries reading 1 million+ records runs slow using versions of DataServer for MS SQL Server later than Progress 9.1D
Query processing 1 million records returns almost instantly when running with Progress 9.1D DataServer for MS SQL Server
Querying a large table using DataServer for MS SQL Server is very slow
Same query can take minutes to return in later versions of DataServer for MS SQL Server
FACT(s) (Environment):
Query is using NO-LOCK
Performance issue is not seen when executing the same query against a Progress database
Performance issue is not seen when executing the same query against DataServer for Oracle
Progress 9.1E and later use Firehose cursors
Attempting to disable Firehose cursors using following parameters has no effect:
-Dsrv PRGRS_CACHE_CONN,0,PRGRS_SP_CACHE,0,CONNECTION_POOLING,0
Progress 9.1E
OpenEdge 10.0x
OpenEdge 10.1x
OpenEdge 10.2A
Windows
MS SQL DataServer
CAUSE:
Bug# OE00169420
CAUSE:
Firehose cursors are used by default in the DataServer for MS SQL Server to handle NO-LOCK queries. Although, in most cases, use of firehose cursors results in the improvement of query performance, there are certain scenarios where the use of firehose cursors can be viewed as causing performance degradation. Specifically, the case whereby a query returns a very large result set but not all records in that result need to be processed. The upfront latency to process such a query with a firehose cursor can make the subsequent processing of that small percentage of results a costly proposal.
FIX:
Upgrade to 10.2A02
OpenEdge 10.2A02 introduces two connection level "-Dsrv" switches made available, QT_FIREHOSE and QT_NO_FIREHOSE. QT_FIREHOSE turns firehose on by default for all cursors in a given session and QT_NO_FIREHOSE turns it off. By default, all sessions will attempt to use firehose cursors so the QT_FIREHOSE connection setting should be considered the default.
In addition, OpenEdge 10.2A02 provides new QUERY-TUNING options to allow users to disable/enable the use of firehose cursors. The connect level "-Dsrv" settings described above can be overridden at the query level by setting a QUERY-TUNING switch. So if "-Dsrv QT_NO_FIREHOSE" was set at connection time, the QUERY-TUNING(FIREHOSE-CURSOR) option could be specified on a particular NO-LOCK query to override the connection level setting and use a firehose cursor for that particular query.