Deleting selected records from a large database table

[TedH]

I'm looking for some thoughts or explanation of the following.

I need to delete a number of records of a specific type from a very big table (many thousands of records)

I thought the most obvious method would be:
Dbs.Execute "DELETE FROM " & tblAcronym & " WHERE " & acrSOURCE & " = '" & sRecordType & "'"

And this works fine, but takes it about 2 minutes, leaving the user to watch a "spinning cursor".

So then I tried this method (which I expected to be slower):
Create a selected Dynaset, then just delete all its records (which also deletes then from the underlying table).

With Dbs.CreateQueryDef(vbNullString, "SELECT * FROM " & tblAcronym & " WHERE " & acrSOURCE & " = '" & sRecordType & "'").OpenRecordset(dbOpenDynaset)
Do While Not .EOF
.Delete
.MoveNext
Loop
.Close
End With

Surprisingly, this only took about 30 seconds and works fine, and I am wondering why the Dbs.Execute was so much slower?

[Schmidt]

I thought the most obvious method would be:
Dbs.Execute "DELETE FROM " & tblAcronym & " WHERE " & acrSOURCE & " = '" & sRecordType & "'"

It is - and from my experience, it should have outperformed your "explicit looping"-approach.

The reasons it didn't could be caused by:
- your DB is sitting on a Network-Share
- you currently don't have an Index defined on the Field(s): acrSOURCE
- you didn't wrapp the Execute-Statement in a transaction

I'm quite sure, that with "proper indexing" - then "performing the Delete-Action" against a local DB (placed on one of your local Disks) -
and wrapping the thing in a transaction - it would "fly" and outperform the explicit looping.

Olaf

[TedH]

Thanks for those comments.

The DB is not on a network share.
The primary (and only) index is on another field.
I was not using a transaction wrap.

I will try adding an index and/or using a transaction and see what that does.

Many thanks for your suggestions.

[TedH]

Ok, I ran some tests with the same block of test data each time.

I ran
Dbs.Execute "DELETE FROM " & tblAcronym & " WHERE " & acrSOURCE & " = '" & sRecordType & "'"
It took 60 seconds.

I added an index to the acrSOURCE field and re-ran the test.
It dropped to 30 second.

However the dbOpenDynaset looping method only took 15 seconds!

I tried wrapping it with Transactions, but that just runs for a while and stops with an error.
Error 3035 System Resource Exceeded

[ChrisE]

Hi,

use a Param. Query

here a sample to Create and Execute.

Private Sub Command1_Click()
   Dim db As DAO.Database
   Dim qry As DAO.QueryDef
   ' Open the database
   Set db = DBEngine.OpenDataBase(".\NorthWind.mdb")
   ' Create query
   Set qry = db.CreateQueryDef("qryDeleteByID", _
      "PARAMETERS [prmID]LONG;" & _
      "Delete * FROM tbl_TestDelete WHERE OrderID = [prmID]")
   db.Close
End Sub



Private Sub Command2_Click()

   Dim db As DAO.Database
   Dim qdf As DAO.QueryDef
 
    Set db = DBEngine.OpenDataBase(".\NorthWind.mdb")
    Set qdf = db.QueryDefs("qryDeleteByID")
    'Set the value of the QueryDef's parameter
    qdf.Parameters("prmID").Value = "10252"
    'Execute the query
    qdf.Execute dbFailOnError
    'Clean up
    qdf.Close
    Set qdf = Nothing
    Set dbs = Nothing
End Sub

regards
Chris

[TedH]

Just tried that.
The CreateQueryDef method works ok, but takes exactly the same amount of time as
the Dbs.Execute method.

[Schmidt]

I added an index to the acrSOURCE field and re-ran the test.
It dropped to 30 second.

However the dbOpenDynaset looping method only took 15 seconds!

Long time no DAO-usage for me - dbOpenDynaset seems comparable to adOpenKeyset
(both Flags allowing the same kind of an "only RowID-based enumeration" IIRC -
then expanding to "more Record-Details" only, when you access the Fields-List on a current Record).

So the external loop is apparently using the "most efficient Flag" already, which the engine provides.

Though why on earth the same kind of "efficient looping" is not applied to an internal engine-operation,
which has the advantage to use internal calls (and no need to work through a COM-interface) - that's baffling to me.

Maybe one has to support the engine a bit more, by giving appropriate "hints and stuff" - as perhaps (with Chris's example)
the explicit QueryType - dbQDelete or something... (but normally the engine should switch its operation-state automatically
to "the right thing", whilst parsing the SQL-text -> a Delete is a Delete...

Alternatively one could play around with the several Flag-Values one can pass into the .Execute Method optionally.
But you see, I'm drifting into "guessing territory"...

I tried wrapping it with Transactions, but that just runs for a while and stops with an error.
Error 3035 System Resource Exceeded

Seems the whole thing is quite memory intensive then (due to your "lots of records") ... in SQLite one
can adjust, where the DBEngine stores its "Transaction-Journal" (memory, or temp-file) - but not sure,
what DAO-DBEngine-Options one could adjust for stuff like that... hmm, googling a bit...

DBEngine.SetOption dbFlushTransactionTimeout, 0 might worth a test in that regard - but as said, all guessing here...

Olaf

[ChrisE]

Hi,

it's been a while since I used DAO, what type of PC do you have ..CPU ; Memory etc..

a few questions
how many records ar in the Table
do you how many records are going to be deleted (just roughly)

try this...

Private Sub Command2_Click()

'   Dim db As DAO.Database
'   Dim qdf As DAO.QueryDef
'
'    Set db = DBEngine.OpenDataBase(".\NorthWind.mdb")
'    Set qdf = db.QueryDefs("qryDeleteByID")
'    'Set the value of the QueryDef's parameter
'    qdf.Parameters("prmID").Value = "10252"
'    'Execute the query
'    qdf.Execute dbFailOnError
'    'Clean up
'    qdf.Close
'    Set qdf = Nothing
'    Set dbs = Nothing

 On Error GoTo DAOTransactions_Err

   Dim wks As DAO.Workspace
   Dim db As DAO.Database
   Dim strSql As String
   Dim prmID As Long
   
   Dim bTrans As Boolean

   ' Get the default workspace
   Set wks = DBEngine.Workspaces(0)

   ' Open the database
   Set db = wks.OpenDataBase(".\NorthWind.mdb")

   ' Begin the Transaction
   wks.BeginTrans
   prmID = 10255
   bTrans = True

strSql = "Delete * FROM tbl_TestDelete WHERE OrderID=" & prmID
   ' MsgBox strSql
   
   db.Execute strSql
   ' Commit the transaction
   wks.CommitTrans
   Exit Sub
   

Set db = Nothing
Set wks = Nothing
   

DAOTransactions_Err:
   If bTrans Then wks.Rollback

   Debug.Print DBEngine.Errors(0).Description
   Debug.Print DBEngine.Errors(0).Number
End Sub

regards
Chris

[dilettante]

Doesn't seem all that hard:

    Set DeleteCommand = New ADODB.Command
    With DeleteCommand
        .CommandType = adCmdText
        .CommandText = "DELETE FROM [Somedata] WHERE [Type] = ?"
        Set .ActiveConnection = Connection
        .Execute DeleteCount, TypeToDelete, adExecuteNoRecords
    End With

This was for a 500,000 row table.

[dilettante]

BTW:

Indexing the Type field shaved a little but not enough to make it useful. Wrapping the delete operation in a transaction actually made it slower by quite a bit.

[jpbro]

Indexing the Type field shaved a little but not enough to make it useful. Wrapping the delete operation in a transaction actually made it slower by quite a bit.

Interesting...definitely depends on the DB engine I think. I tried modifying your demo to use Sqlite and wrapping the INSERT loop in a transaction dramatically improved performance (total time to create the database was ~5.5 seconds for SQLite vs. ~12 seconds for the MDB). If I didn't use a transaction around the Add loop, then SQLite was slooooow. I didn't bother to let it finish running.

Adding an INDEX to the type column didn't seem to help at all for DELETES in the SQLite database either.

An interesting difference - even with SQLite's "limited" data-types, the DB size after initial creation was ~72MB for SQLite vs. ~92MB for the MDB.

Lastly, on my computer the total DELETE & COMPACT run time took was ~2.5 seconds for both the MDB and SQLite DBs, so no advantage for either there.

[TedH]

Hi,

it's been a while since I used DAO, what type of PC do you have ..CPU ; Memory etc..

a few questions
how many records ar in the Table
do you how many records are going to be deleted (just roughly)

Chris

About 27000 records in total.
The delete might be about 5000 to 10000 records.

[TedH]

>>Doesn't seem all that hard:


The results I got using your code were:

Database created and compacted. Took: 13,032 ms.
Type to delete is: 0
Database re-opened. Took: 0 ms.

Deleted 49,968 records where Type was 0. Took: 890 ms.

Database closed and compacted. Took: 2,078 ms.
Done.


Looks interesting, but I will need to add it to my program to try it on my data.
You are using a couple of references I don't have.
Microsoft Jet Replication Objects 2.6 Library
Microsoft ActiveX Data Objects

Mine just uses:
Microsoft DAO 3.6 Object Library

The program is a huge 20 year old "legacy" program that I am trying to improve and add some new features to, but I have to be very careful not to break compatibility with existing versions.

[ChrisE]

About 27000 records in total.
The delete might be about 5000 to 10000 records.

that isn't really huge, but it depends on what is in each record.
so we don't know, you will have to give more details.

the other DAO Methode would be to go threw the records first...

Private Sub Command6_Click()

Dim rstemp As DAO.Recordset
Dim dbtemp As DAO.Database
Dim prmID As Long
Dim CountRS As Long

prmID = 2

Set dbtemp = DBEngine.Workspaces(0).OpenDataBase(".\Northwind.mdb")
Set rstemp = dbtemp.OpenRecordset("SELECT * from tbl_TestDelete WHERE OrderID=" & prmID, dbOpenDynaset) 

'reading the number of records
    If Not rstemp.EOF Then rstemp.MoveLast
    If Not rstemp.BOF Then rstemp.MoveFirst
    CountRS = rstemp.RecordCount - 1
    For i = 0 To CountRS
        rstemp.Delete
        rstemp.MoveNext
    Next i
   
rstemp.Close
Set rstemp = Nothing
dbtemp.Close
Set dbtemp = Nothing
End Sub

and as you can see from dilettantes sample a rewrite might be an better option.
Also we don't know if you have to change
from DAO to ADO.

EDIT:

And this works fine, but takes it about 2 minutes, leaving the user to watch a "spinning cursor".

2min ??
what PC are you working with ?? CPU and RAM



regards
Chris

[TedH]

Many thanks for all the suggestions.

The PC is a fast, modern one. 8GB ram, Intel i5 2.2G processor, but who know what sort of computer other users are running it on?

This is not a "paying project", so available time and effort are rather limited! :-)
Changing from DAO to ADO is a non-starter. Far too much work to do.
About 50,000 lines of code. Some very convoluted and hard to understand.

I tried to incorperate dilettantes sample but it runs in to problems, as the database is already open in exclusive mode.
Looks like I have now hit the buffers with trying to improve this particular part of the code.

[dilettante]

I ran my tests on a 10 year old PC, so I wasn't shading things by throwing raw power at it. The CPU is slow, the hard drive is slow, and there is little RAM by current standards.

How hard could it be to do the same thing using DAO?

Ignoring compaction (which DAO can do anyway, but just take that out of it) and a parameter query (which DAO can do anyway too via QueryDefs, but take that too)... there just isn't much left.

We're actually right back to your original "obvious method."


This suggests a couple of things.

One is that you are not opening the database with exclusive access to perform this operation. If your database doesn't have multiple users you should be doing that anyway. This bypasses all of the locking logic which can rob you of performance in a single-user application.

The other is that your table rows may be far larger than those in my demo. That might double or even triple the time required, but surely it won't take 10 to 100 times as long?

And I suppose another might be that your database is a mess in desperate need of compaction anyway. Running DBEngine.CompactDatabase before starting may do wonders for you.

[dilettante]

What database format are you using?

Jet 4.0 performs far better than the older Jet3.x format, and it supports Unicode and Unicode compression. This makes the databases larger but they also can be larger than Jet 3.x MDBs because a database page size of 4KB is used instead of the old-format's 2KB.

[dilettante]

The actual size of the database on disk can be another issue. As it gets larger disk caching becomes less of an aid to performance.

[TedH]

OK, some progress.

Changing the database from Shared mode to Exclusive mode has helped. I don't know why it was ever opened in shared mode, as it is a single user system.

The SQL method now takes the same time as the Dynaset Do-Loop method. I think this is now an acceptable delay.
At least the SQL method LOOKS better than the messy Dynaset Do-loop option!

The database is regually compacted with DBEngine.CompactDatabase, so this is not the problem.
Th database size on my PC is now 380Mb, although it will vary depending on what options a user has chosen.

How do I tell what version of Jet it is using? (I believe Win 10 comes with Jet 4)
Does using DAO 3.6 Object Library have anything to do with it?
(3.6 seems to be the lastest in the available references list on my PC)

The program is now only targeted to run on Win 7 and above, athough it originally ran on Windows 3 !

[dilettante]

DAO 3.6x was the crutch Microsoft offered to help VB5 coders keep creaky old code working in the ADO Age. It was created in order to allow these programs to work with a database upgraded to Jet 4.0 format, so the odds are good that you are using Jet 4.0 which is the only one that ships as part of Windows anyway.

But you could try something like:

Option Explicit

Private Sub Main()
    Dim Lookup As Collection

    Set Lookup = New Collection
    With Lookup
        .Add "UNKNOWN", CStr(0)
        .Add "JET10", CStr(1)
        .Add "JET11", CStr(2)
        .Add "JET2X", CStr(3)
        .Add "JET3X", CStr(4)
        .Add "JET4X", CStr(5)
        .Add "DBASE3", CStr(10)
        .Add "DBASE4", CStr(11)
        .Add "DBASE5", CStr(12)
        .Add "EXCEL30", CStr(20)
        .Add "EXCEL40", CStr(21)
        .Add "EXCEL50", CStr(22)
        .Add "EXCEL80", CStr(23)
        .Add "EXCEL90", CStr(24)
        .Add "EXCHANGE4", CStr(30)
        .Add "LOTUSWK1", CStr(40)
        .Add "LOTUSWK3", CStr(41)
        .Add "LOTUSWK4", CStr(42)
        .Add "PARADOX3X", CStr(50)
        .Add "PARADOX4X", CStr(51)
        .Add "PARADOX5X", CStr(52)
        .Add "PARADOX7X", CStr(53)
        .Add "TEXT1X", CStr(60)
        .Add "HTML1X", CStr(70)
    End With
    With New ADODB.Connection
        .Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
            & "Data Source='Sample.mdb';Mode=Share Exclusive"
        On Error Resume Next
        MsgBox "Jet Engine Type is " _
             & Lookup(CStr(.Properties("Jet OLEDB:Engine Type").Value))
        If Err Then
            On Error GoTo 0
            MsgBox "Jet Engine Type was invalid"
        End If
        .Close
    End With
End Sub

[ChrisE]

OK, some progress.


The program is now only targeted to run on Win 7 and above, athough it originally ran on Windows 3 !

Window 3 ?
what Access version are you using, 2.0 perhaps

regards
Chris

[TedH]

Window 3 ?
what Access version are you using, 2.0 perhaps

regards
Chris

It doesn't use Access. It uses the MS Jet engine that comes with Windows.

[TedH]

> Lookup(CStr(.Properties("Jet OLEDB:Engine Type").Value))

Reports: Jet Engine Type is JET4X