Delete query - taking long time to complete

[TimGoff]

Hi

The following SQL takes an exceptionally long time to run (over 15 mins) - and is only deleting 500 rows. Has anyone any thoughts on how to improve it, or an alternative way of doing it?

strSQLString = "DELETE * FROM tbl_Bcst_T_srs WHERE tbl_Bcst_T_srs.Series_ID NOT IN (Select series_id from tbl_Bcst_T_prg)"

gcnnDB.Execute strSQLString, lngRecordsDeleted, adCmdText

Thanks in advance
Tim

 

[BobStubbs]

A few thoughts which might be of some use ...

1. Is Series_Id indexed in both tables?
2. Is Series_Id the same data type (e.g. integer) in both tables?
3. Does the process run any faster (or slower) if you run it via DoCmd.RunSQL?
4. What happens if you use a two-step process like this:

-- Assume tbl_Bcst_T_srs has an extra True/False field added, called DeleteFlag
-- Run an update query to set the flag to True, for records where Series_ID IS in tbl_Bcst_T_prg
-- Run a delete query, to delete records from tbl_Bcst_T_srs where the flag is 'False'

Maybe trying these ideas will give a clue about what is happening, and improve performance.

Bob Stubbs (London, UK)

 

[RoyVidar]

Another thing to try, is using a stored query in stead of dynamic sql, also adding the adexecutenorecords might help.

[tt]gcnnDB.Execute strSQLString, lngRecordsDeleted, adCmdText+adexecutenorecords

gcnnDB.Execute "queryname", lngRecordsDeleted, adcmdstoredproc+adexecutenorecords[/tt]

Roy-Vidar

 

[PHV]

Typed, not tested:
strSQLString = "DELETE S.* FROM tbl_Bcst_T_srs AS S LEFT JOIN tbl_Bcst_T_prg AS P ON S.Series_ID = P.series_id WHERE P.series_id Is Null"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TimGoff]

Thanks for all your posts guys..haven't had time to test them all yet, appreciate your comments