Re: Delete Cursor: What To Use In Select Clause?

Tom asked: >From: nomad@codetel.net.do (Tom Hay) >Date: Tue, 09 Jul 1996 15:55:04 GMT >X-Informix-List-Id: <news.25900> > >I sometimes use SELECT ... with HOLD if I need to delete *a lot* of >rows from a table. DELETE ... WHERE ... grinds away for a while, then >aborts with Long Transaction. SELECT ... with HOLD, DELETE ... WHERE >CURRENT OF ..., and commit every 100 rows avoids that problem > >Is there a better way ? > >johnl@informix.com (Jonathan Leffler) wrote: >>I have seen a couple of answers, but neither of them addressed what I would >>regard as the fundamental question, which is: > >>* If you aren't interested in the values from the SELECT, why are you using >> a SELECT FOR UPDATE and DELETE WHERE CURRENT OF statements? > ><snip> Tom's question closely parallels Alex's response. I wrote back to Alex by private email, but since the question was asked publicly twice, I'm now sending my answer to c.d.i too as it is presumably of some general interest. I've taken the liberty of slightly expanding on one or two of the points I made earlier... Date: Tue Jul 9 09:28:51 1996 From: johnl@informix.com (Jonathan Leffler) To: alex.oss@films.com >From: "Alexander J. Oss" <alex.oss@films.com> >Date: Tue, 09 Jul 1996 09:07:06 -0300 >X-Informix-List-Id: <news.25904> > >Jonathan Leffler wrote: >> I have seen a couple of answers, [...] > >For a few reasons, actually: > >1. Like you mention, I am using a subquery that would have to refer to the > table being deleted. That can make life tricky. >2. I want to avoid long transaction errors and excessive locking. There are > begin/commit work pairs during the processing of the cursor. So it is actually a WITH HOLD cursor. There are several issues. One, if you are running into long transaction problems, then arguably your system is under configured -- it needs more logical log space. If locking is a problem, then maybe you should consider locking the table as a whole; you'd need to use an exclusive lock. If that raises problems, then you need to reconsider the whole way in which the operations are structured. Why are you trying to do such extensive revisions to the database. >3. I want to provide users with feedback while processing. I consider this a >very important part of the interface, and it also helps debugging weird >behavior... <grin> Since I'm using the shared memory IPC method, I cannot use >the sqlbreakcallback() function (although I'd like to!), according to p. 9-29 >of ESQL/C v7.1. The primary disadvantage of using the SELECT/DELETE approach is the increase in traffic between the OnLine engine and the application; that slows down the process enormously. Each row has to be sent back to the application singly; you cannot bunch rows when the cursor is FOR UPDATE because of the locking requirements -- in contrast to the non-update cursor where multiple rows can be returned in a single data exchange. Then the DELETE request has to be sent back to the engine, the delete done, and acknowledged, and the acknowledgement read. By contrast, a straight DELETE only has to relay the results. If you preprocess the records to be deleted into a temp table, you can report on how many records are to be deleted by analysing the temp table; quicker than dealing with the main table. You can, however, run into problems with the underlying table changing between the time your SELECT starts and the time your DELETE starts. Some of these can be relieved by using an appropriate isolation level (eg REPEATABLE READ), though that will probably stress the locking system again. Your discussion of sqlbreakcallback() means that you think the user should be able to cancel the operation part way through. Using intermediate transactions means that you cannot always recover to the state before the DELETE operations started -- which makes me very worried about the integrity of the database. Have you thought of spawning off a DELETE process -- you establish the criteria with the user, possibly counting the number of rows to be deleted, etc. Then, with their approval, you kick off a second process which actually does the DELETE using a new database connection? They aren't waiting for the DELETE to finish, so that deals with the waiting problem. >If you know of ways to solve the above issues, I'd certainly like to >hear them. Your idea of selecting the primary key into a temp table is >excellent, but does not eliminate the long transaction and locking problem. I don't have cut and dried answers to the issues -- I don't think there are any. But I'm very tempted to ask why interactive operations are doing such extensive deletions? Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>