SQL statement takes too long (1 Viewer)

JohnPapa

Registered User.
Local time
Today, 19:06
Joined
Aug 15, 2010
Messages
954
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

I’m here to help
Staff member
Local time
Today, 10:06
Joined
Oct 29, 2018
Messages
21,358
Hi. Just curious, do you have to delete the records?
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:06
Joined
Sep 21, 2011
Messages
14,044
Well you could start by just selecting the ID field? as that is all you need?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 17:06
Joined
Feb 19, 2013
Messages
16,553
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

Immoderate Moderator
Staff member
Local time
Today, 12:06
Joined
Feb 28, 2001
Messages
26,999
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

Registered User.
Local time
Today, 19:06
Joined
Aug 15, 2010
Messages
954
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

Registered User.
Local time
Today, 19:06
Joined
Aug 15, 2010
Messages
954
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

Registered User.
Local time
Today, 19:06
Joined
Aug 15, 2010
Messages
954
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

Super Moderator
Staff member
Local time
Today, 17:06
Joined
Feb 19, 2013
Messages
16,553
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

Immoderate Moderator
Staff member
Local time
Today, 12:06
Joined
Feb 28, 2001
Messages
26,999
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

Registered User.
Local time
Today, 19:06
Joined
Aug 15, 2010
Messages
954
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

Super Moderator
Staff member
Local time
Today, 17:06
Joined
Feb 19, 2013
Messages
16,553
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

Registered User.
Local time
Today, 19:06
Joined
Aug 15, 2010
Messages
954
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

Super Moderator
Staff member
Local time
Today, 17:06
Joined
Feb 19, 2013
Messages
16,553
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
 

Users who are viewing this thread

Top Bottom