SQL statement takes too long

[JohnPapa]

I use the following statement to delete all Customers that have no entry in tblTransaction

DoCmd.RunSQL "DELETE FROM tblCustomer " _
& " WHERE NOT EXISTS (SELECT * FROM tblTransaction WHERE tblCustomer!lngCustomerID = tblTransaction!lngPartyID) ;"

In the specific case there are 7000 customers. The only problem is that it takes 100 seconds on an i7, 8gb ram pc.

Is there a more efficient way of doing this?
John

 

[theDBguy]

Hi. Just curious, do you have to delete the records?

 

[Gasman]

Well you could start by just selecting the ID field? as that is all you need?

 

[CJ_London]

or use a left join

DELETE *
FROM tblCustomer LEFT JOIN tblTransaction ON tblCustomer.lngCustomerID= tblTransaction.lngPartyID
WHERE tblTransaction.lngPartyID is null

I presume both ID fields are indexed

 

[The_Doc_Man]

Do this in two stages. First, make a flag field for this purpose, then update the flag to mark records for deletion using a simple JOIN.
Then in a second pass, delete only the marked records. The single-table delete will run faster. The problem here is record deletion, which has to re-work the index for each deletion.

If this is a REALLY rare event, you might wish to consider doing it stand-along, dropping the indexes (after marking the records for deletion), and then doing the deletion as a simple single-table delete. Then re-establish the indexes. However, if you are going to have to do this more often, I would NOT do this. You would invite database bloat and the need for frequent Compact & Repair operations.

AND there is the other approach - don't delete ANY records ever. Just mark them as "obselete" or whatever term is appropriate and use queries that test your "obsolete" flag as your record sources to assure that you don't see deleted records in the future. If this operation is going to be frequent, you would do best to just never delete anything more often than once a year or something like that.

 

[JohnPapa]

Yes I have to for many reasons.

Hi. Just curious, do you have to delete the records?

 

[JohnPapa]

I tried it before and made no substantial difference

Well you could start by just selecting the ID field? as that is all you need?

 

[JohnPapa]

Thanks, will try.

or use a left join

DELETE *
FROM tblCustomer LEFT JOIN tblTransaction ON tblCustomer.lngCustomerID= tblTransaction.lngPartyID
WHERE tblTransaction.lngPartyID is null

I presume both ID fields are indexed

 

[JohnPapa]

This is a one time event and will try your suggestion.

Do this in two stages. First, make a flag field for this purpose, then update the flag to mark records for deletion using a simple JOIN.
Then in a second pass, delete only the marked records. The single-table delete will run faster. The problem here is record deletion, which has to re-work the index for each deletion.

If this is a REALLY rare event, you might wish to consider doing it stand-along, dropping the indexes (after marking the records for deletion), and then doing the deletion as a simple single-table delete. Then re-establish the indexes. However, if you are going to have to do this more often, I would NOT do this. You would invite database bloat and the need for frequent Compact & Repair operations.

AND there is the other approach - don't delete ANY records ever. Just mark them as "obselete" or whatever term is appropriate and use queries that test your "obsolete" flag as your record sources to assure that you don't see deleted records in the future. If this operation is going to be frequent, you would do best to just never delete anything more often than once a year or something like that.

 

[JohnPapa]

or use a left join

DELETE *
FROM tblCustomer LEFT JOIN tblTransaction ON tblCustomer.lngCustomerID= tblTransaction.lngPartyID
WHERE tblTransaction.lngPartyID is null

I presume both ID fields are indexed

Well CJ, your SQL statement did not work, BUT your assumption did. Field tblTransaction.lngPartyID was not indexed and when I indexed it it took a few second to run. I now have to figure out how to add the index programmatically. Thanks

 

[CJ_London]

depends on what 'did not work' means since it was freetyped with a bit of copy/paste thrown in. But indexing is very important for fast running.

Note that subqueries can also be slow since it requires tblTransaction to be interrogated for every row.

With regards adding indexes - see this link https://www.w3schools.com/sql/sql_create_index.asp

 

[The_Doc_Man]

Aha! If the field was not indexed, then my suggestion about dropping the indexes wouldn't have worked anyway. Indexing would have at least made the JOIN operate faster, so CJ was on a better track than I was in this case.

 

[JohnPapa]

depends on what 'did not work' means since it was freetyped with a bit of copy/paste thrown in. But indexing is very important for fast running.

Note that subqueries can also be slow since it requires tblTransaction to be interrogated for every row.

With regards adding indexes - see this link https://www.w3schools.com/sql/sql_create_index.asp

Many thanks, the SQL should delete all Customers who do not have a Transaction (where there is no entry in tblTransaction with a the Customer ID which is called lngPartyID in tblTransaction)

 

[CJ_London]

I understand that - since the query I suggested identifies all records in customers that do not have a transaction, really not sure what your issue is since you have only said what it is supposed to do, not what it is actually doing. Either way, you have a solution, your query runs faster with indexing, so lets leave it at that.

 

[JohnPapa]

Thanks again CJ, since this was solved with your input.

Regarding the creation of an index for field tblTransaction.lngPartyID, table tblTransaction is in an attached db. Merely executing a CREATE INDEX did not help. I will do some more research.

 

[CJ_London]

create a new db object of the other db (investigate opendatabase) then run your code - this is air code but gives you a direction

set bedb=opendatabase
bedb.execute "createindex sql")
bedb.close