Having problems with your account or logging in?
A lot of changes are happening in the community right now. Some may affect you. READ MORE HERE

OpenESQL slow performance on cursor close using ADO.NET with SQL Server

OpenESQL slow performance on cursor close using ADO.NET with SQL Server

Problem:

Customer has a Visual COBOL managed .NET application which uses OpenESQL with the DBMAN=ADO directive set.

The application declares a cursor that returns a large number of rows.

When the cursor is opened and the rows are fetched the performance is very good but when the CLOSE statement is issued for the cursor it can take several seconds to return.

What is causing this slowdown and how can it be improved?

Resolution:

This type of behavior can occur if you are defining a forward only read only cursor and only the first few rows are being read, ie a large number of rows are being left unread at close time. This is a scenario in which SQL Server is known to perform badly. The reason is in the design of the TDS protocol which returns the entire result set in a single response with no easy way for a client to stop the flow of rows from the server, so typically the provider will just sit in a loop discarding rows that the application doesn’t need. There are two possible workarounds.

1. Limit the number of rows returned by either fine tuning the WHERE clause or using SELECT TOP.
2. Switch to a server cursor by using DECLARE c FAST FORWARD CURSOR FOR

(2) will improve CLOSE time at the expense of slower OPEN and FETCH times because a server cursor only returns rows when the client requests them, so there is no overhead abandoning the remainder of the result set.

However, since OpenESQL with BEHAVIOR=MAINFRAME buffers 8 rows at a time by default the performance hit shouldn’t be too bad and overall you should see a net gain.

DISCLAIMER:

Some content on Community Tips & Information pages is not officially supported by Micro Focus. Please refer to our Terms of Use for more detail.
Top Contributors
Version history
Revision #:
1 of 1
Last update:
‎2014-04-25 20:59
Updated by:
 
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.