Slow delete query 1

[bevan]

HI Everyone,

I am running a big delete query through some code and it is TAKING AGES. It took about 20-30 seconds on a test database but when i tried it on our production database (that has to remain in use) it took about 4 hours.

All up this query cycles through only about 400 records (the ones in the deleteme table) so i dont understand why it would take so long except to assume that it is due to the fact that the database is in production and therefore being used concurrently.

The code actually worked fine and did exactly what i wanted but it is just too slow to run.

It is MS Access 2000 sitting on Win2k server and i connected to it by linking the tables in another MS Access 2000 db on my PC (win2k).

Any ideas...



Function bigdelete()

Dim objConn As ADODB.Connection
Dim objRS As ADODB.Recordset
Dim strSQL As String

Set objConn = CurrentProject.Connection
Set objRS = New ADODB.Recordset

strSQL = "SELECT DeleteMe.id FROM DeleteMe;"
objRS.Open strSQL, objConn, adOpenStatic, adLockOptimistic

'accurate record count
objRS.MoveLast
objRS.MoveFirst

'suppress warnings while we do all the deletes
DoCmd.SetWarnings False

'Loop through recordset performing three queries on each ID number
For A = 1 To objRS.RecordCount
DoCmd.RunSQL "DELETE table1.* FROM table1 WHERE table1.ID = '" & objRS!id & "';", False

DoCmd.RunSQL "DELETE table2.* FROM table2 WHERE table2.ID = '" & objRS!id & "';", False

DoCmd.RunSQL "DELETE table3.* FROM table3 WHERE table3.ID = '" & objRS!id & "';", False

objRS.MoveNext
Next

'Set warnings back on
DoCmd.SetWarnings True

'Close recordset
Set objRS = Nothing
Set objConn = Nothing

End Function

 

[KenReay]

Hi

How about trying:

Function bigdelete()

'suppress warnings while we do all the deletes
DoCmd.SetWarnings False

DoCmd.RunSQL "DELETE * FROM table1 WHERE table1.ID IN (SELECT DeleteMe.id FROM DeleteMe);"

DoCmd.RunSQL "DELETE * FROM table2 WHERE table2.ID IN (SELECT DeleteMe.id FROM DeleteMe);"

DoCmd.RunSQL "DELETE * FROM table3 WHERE table3.ID IN (SELECT DeleteMe.id FROM DeleteMe);"

'Set warnings back on
DoCmd.SetWarnings True

End Function
Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Nikita6003]

D'you have an index on the ID field for each of the tables where you perform a delete? Could be Access is trying to do a table scan when deleting ...

Cheers
Nikki

 

[Frink]

Hallo,

It probably all depends on your db structure, speed of connection, number of users etc.

Would it be possible to run the delete ovenight (or whenever user interaction is at a minimum. Deleting records while people are using the data could be asking for trouble.

Are your ID fields indexed? Indexing them (no duplicates, if poss.) could help.

Do they have relationships to other tables with cascade delete set? If they do, you could try removing these tables first.

You could also try using a transaction around the whole set of deletes, although this may be a bad thing if there's lots of other people using the data.

Another thing might be to look at what sort of locking you have (pessimistic, optimistic, whatever) This will affect multiple user stuff, but it does get quite complicated...

I guess it's just best to try different things, run a few tests and see what happens

- Frink

 

[bevan]

Thanks everyone for the advice.

I will do some more tests over the next couple of days and see if any of your ideas help.


Bevan