Re: 4gl questions :

On Mon, 6 Oct 1997, Nils Myklebust wrote: > johnl@informix.com (Jonathan Leffler) wrote: > :On 2 Oct 1997, Belakimem wrote: > ...snip... > :> Q2. Is there any harm in not closing open cursors, just reopening every time > :> they are needed? > : > :It depends on whether the code will ever need to work against a MODE ANSI > :database. If not, then it isn't critical, but it is a good idea to close > :the cursor when you finish with it (and FREE it if you won't use it very > :often); this cuts down on the memory needed in the engine. > > We have found two more issues: > 1. If you do need the cursor very often it used to be faster to not > close it, just do another open and fetch. I haven't realy tested this > on 7.x OnLine though. True enough, in general. The CLOSE operation requires a round-trip message to the database engine and back, in general. I think that the latest versions of ESQL/C optimize on this by piggy-backing several messages at a time, but I've not researched into that. Nevertheless, I still think it is cleaner programming to close the cursors explicitly, unless clear performance measurements demonstrate that the close operation is a major (meaning measurable) factor in the performance of the application. One way in which it might be a measurable factor is if you are using two cursors, one (C1) with an ORDER BY clause and one (C2) with a FOR UPDATE clause, to do a series of updates. With this scenario, you'd be using the C1 cursor to fetch the primary keys for the rows to be updated, and then opening C2 for each such row, then doing the update and then closing C2. The close might produce a measurable overhead, but I'd still want the measurements to justify it. > 2. If the database is without logging (where this issue is most > acute as commit work would otherwise close all cursors not declared > with hold) If you are saying, as I think you are, that unclosed cursors are most problematic in unlogged databases because the transaction boundaries (commit or rollback) in a logged database automatically closes all open cursors, then you are correct. > we have found some locking problems caused by open cursors. If a cursor is FOR UPDATE, doesn't it apply a lock on the current row, even in an unlogged database? Heck, I ought to know that for sure, but I'm getting rusty... If there is such a lock, then when the cursor is closed, the lock would be released, and when the cursor is moved onto the next row, the original lock is released (and a new one acquired). If the cursor was left open, the lock would remain on the last row fetched indefinitely -- until the cursor was explicitly closed or the program terminated. The possibility of unintentional lock contention is probably justification enough for closing the cursor explicitly. > Some kind of locks seem not to be released in some cases until the > cursor is closed. This issue is still under investigation, so I don't > know any more details now. Yours, Jonathan Leffler (johnl@informix.com) #include <witticism.h>