William Benson (VBACreations.Com)
vbacreations at gmail.com
Mon Jul 4 09:38:17 CDT 2011
Gustav and Stuart, After testing this, I don't think there is a temp table, at least not initially - and I doubt during or afterwards, either. The reason I conclude this is manifold. Primarily, because if you cancel instead of permitting the GUI to continue without UNDO, canceling the delete takes WAY less time than it would take to put the records back in a table. And if you're referring perhaps to some tiny temp table of records which might be deletable WITH THE ABILITY to undo, that is a far smaller chunk of data than the main bulk Access will ultimately delete when you allow it to proceed without undo. Furthermore, I have gone through the exercise I mentioned . Delete, No, Delete, No, . a dozen times or so and the database size never changes on disk. So I am pretty sure Access is storing data in RAM, not on Disk. And certainly it is not creating a temp table at any other time, since allowing it to delete all, or some (pressing Escape) records does not leave the database any bigger than before delete was pressed. I do lean towards the recordset walk however, that makes a lot of sense in terms of what behavior we observe. - Access is very slow to delete from large tables even when allowing it to continue without undo - Records are deleted exactly in the order in which they appear, top to bottom, in the block of selected records - Interrupting a deletion with the Escape key leaves you with whatever records Access didn't get to yet - Deleting Still gotta wonder why Jet would do it that way. With records selected, one would think there is some hidden "selected" flag which Access's delete operation could make use of, to write its own behind the scenes SQL that says "Delete from ThisTable where the UserSelectedMe flag = TRUE". and then delete the records as fast as a query does it! From: William Benson [mailto:vbacreations at gmail.com] Sent: Monday, July 04, 2011 5:52 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Delete query... fast ... deleting from datasheet view ... slow I will check again but I think what got me interested in the question was that ia manual delwte takes longer even taking into account just time AFTER the user confirms ok to continue without Undo. Where does Access store the temp table Gustav? In other words if you open a monstrously large table select all records and do several faux deletes one after another which you confirm but cancel at the "Continue without Undo?" Prompt....does the database bloat I wonder! I will check this when I get back to computer!_ Bill Benson Owner VBACreations, LLC On Jul 4, 2011 4:12 AM, "Gustav Brock" <Gustav at cactus.dk> wrote: > Hi William and Stuart > > I don't think that's the reason, but it is similar: Access walks through the recordset (which may be filtered) and copies all records to a temp table to hold them in case you - when asked later via the GUI - choose to undo the operation. Only if you choose to confirm the deleting of the records, this actually takes place. > > /gustav > > >>>> stuart at lexacorp.com.pg 03-07-2011 23:56 >>> > A SWAG: > Because Access can't tell that all the records are selected. It has to step through the rows > and checking the "selected" attribute. That means that it can't implement a simple "Delete * > From tblA" but has to specify each of the records separately for deletion. > > -- > Stuart > > On 3 Jul 2011 at 17:23, William Benson (VBACreations. wrote: > >> Why is a query that deletes all records from a table so fast in >> comparison with a manual delete operation on a table that is opened in >> datasheet view? > > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com