Progress
Version 9
Product Update Bulletin


Progress DataServer For Microsoft SQL Server

The DataServer for Microsoft SQL Server provides enhancement to the new DataServer (–Dsrv) startup parameter options.

DataServer (–Dsrv) Startup Parameter Options

Query Result Order: The PRGRS_ALWAYS_INDEX Option

Some queries can gain a performance advantage when the result set from the query does not use an index to order the results. In older versions of the DataServer for Microsoft SQL Server, results sets were always indexed because queries and their results were gathered in segments with new cursors being generated for each query segment. However, most advanced drivers and data sources now allow a single cursor to be retained for the life of a given query, even across a transaction boundary. Drivers that can preserve cursors are allowed to execute unindexed queries if the PRGRS_ALWAYS_INDEX option is set to 0 (off). When this option is off and the user does not specify an index or BY clause for the results of their 4GL statement and the particular 4GL being executed does not require scrolling capability in the Progress client, the query result will remain unindexed by the DataServer.

NOTE: In an unindexed query, the DataServer will not send an ORDER BY clause to the data source. However, the DataServer has no control over whether or not the actual data source utilizes an index in order to generate a result set.

Concurrent Query Execution: The PRGRS_CACHE_CONN Option

It is possible to run read-only queries in separate connections through the driver to the data source. Opening a separate connection to run a query or stored procedure might provide better performance; although, having to many open connections might also degrade performance. The PRGRS_CACHE_CONN option allows you to set a limit for the maximum number of server connections available in the Progress DataServer session. If the session attempts to exceed the maximum threshold, a session will need to wait for an existing connection to complete first before an additional connection can be made.

Concurrent Procedure Results: The PRGRS_PROC_TRAN Option

The DataServer allows only one active request for running a sorted procedure before you run a second one. However, you can process results from several stored procedures concurrently if you set the PRGRS_PROC_TRAN switch to 1 (on). When switched on, this option will cause a separate connection to be used for each stored procedure request (assuming the PRGRS_CACHE_CONN threshold has not been exceeded).

CAUTION: When procedures run in separate connections of the same DataServer session, the scopes of their respective transactions are isolated from one other. If one running procedure attempts to update the same record used by another running procedure in the same DataServer session, a lock time out or even a deadlock could occur.

Cursor Characteristics: The PRGRS_STABLE_CURS Option

The SQL Server driver and its data source ultimately determine whether a cursor is stable during the commit or rollback of a transaction and if the cursor can persist beyond a single transaction scope. The DataServer normally resolves cursor characteristics by interrogating the driver and setting the run-time environment accordingly. Progress does not recommend bypassing normal operation under most circumstances. However, if your driver and/or data source do not provide stable cursors, then under very specific circumstances, you might improve performance by setting the PRGRS_STABLE_CURS option to 1 (on). If all the 4GL you run in your Progress DataServer session is without transactions or if all of its queries and resultant data sets are fully processed on one or the other side of any existing transaction boundaries, setting this option is possible.

NOTE: This is a session-level switch which means all 4GL run in that session must comply with the above requirements.

Wait Time for Asynchronous Connections: The PRGRS_WAIT_DELAY Option

The PRGRS_WAIT_DELAY option is ignored unless you are running in SQL Server asynchronous mode. Check your driver and Progress connection requirements to determine if you are allowed to run in asynchronous mode. This option allows you to determine the number of seconds you would want the DataServer to delay further execution while waiting for the driver to respond to a request that might initiate a lock on database resources. The delay is initiated when the NO–WAIT option is used in your 4GL and the DataServer is awaiting a response to a database request made through the SQL Server driver


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095