OpenESQL slow performance on cursor close using ADO.NET with SQL Server
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?
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.