RunSQL Delete is locking up/taking forever

[paulwilliamsonremove@removespamcop.net]

Hi,

Using Access 2003.

I am trying to delete records from one table/query
("qryHistoryPer sonIDs") if they don't exist in another table
("qryDonations" ). But the SQL syntax I came up with is taking forever
or locking up Access 2003.

What am I doing wrong?

The code is below. dFromDate_pw and dToDate_pw are set to values of
date fields on a form. qryHistoryPerso nIDs contains one key field:

DoCmd.RunSQL "Delete * From qryHistoryPerso nIDs Where
qryHistoryPerso nIDs.personID Not In (Select qryDonations.pe rsonid From
qryDonations Where tblDonations.[DonationDate] Not Between #" &
dFromDate_pw & "# And #" & dToDate_pw & "# )"

I'd rather not manually read through the table as it has 4,000 records
and is growing.

Any ideas?



-pw

use paulwilliamson at spamcop dot net for e-mail

[jimfortune@compumarc.com]

Re: RunSQL Delete is locking up/taking forever

paulwilliamsonr emove@removespa mcop.net wrote:[color=blue]
> Hi,
>
> Using Access 2003.
>
> I am trying to delete records from one table/query
> ("qryHistoryPer sonIDs") if they don't exist in another table
> ("qryDonations" ). But the SQL syntax I came up with is taking forever
> or locking up Access 2003.
>
> What am I doing wrong?
>
> The code is below. dFromDate_pw and dToDate_pw are set to values of
> date fields on a form. qryHistoryPerso nIDs contains one key field:
>
> DoCmd.RunSQL "Delete * From qryHistoryPerso nIDs Where
> qryHistoryPerso nIDs.personID Not In (Select qryDonations.pe rsonid From
> qryDonations Where tblDonations.[DonationDate] Not Between #" &
> dFromDate_pw & "# And #" & dToDate_pw & "# )"
>
> I'd rather not manually read through the table as it has 4,000 records
> and is growing.
>
> Any ideas?
>
>
>
> -pw
>
> use paulwilliamson at spamcop dot net for e-mail[/color]

The 'Not In (Select... syntax is known to be slow for larger tables.
If you use the wizard to create an unmatched query it will show you an
alternate syntax for returning the same records using a LEFT JOIN that
runs much faster.

James A. Fortune

[paulwilliamsonremove@removespamcop.net]

Re: RunSQL Delete is locking up/taking forever

On 16 Nov 2005 15:20:27 -0800, jimfortune@comp umarc.com wrote:
[color=blue]
>paulwilliamson remove@removesp amcop.net wrote:[color=green]
>> Hi,
>>
>> Using Access 2003.
>>
>> I am trying to delete records from one table/query
>> ("qryHistoryPer sonIDs") if they don't exist in another table
>> ("qryDonations" ). But the SQL syntax I came up with is taking forever
>> or locking up Access 2003.
>>
>> What am I doing wrong?
>>
>> The code is below. dFromDate_pw and dToDate_pw are set to values of
>> date fields on a form. qryHistoryPerso nIDs contains one key field:
>>
>> DoCmd.RunSQL "Delete * From qryHistoryPerso nIDs Where
>> qryHistoryPerso nIDs.personID Not In (Select qryDonations.pe rsonid From
>> qryDonations Where tblDonations.[DonationDate] Not Between #" &
>> dFromDate_pw & "# And #" & dToDate_pw & "# )"
>>
>> I'd rather not manually read through the table as it has 4,000 records
>> and is growing.
>>
>> Any ideas?
>>
>>
>>
>> -pw
>>
>> use paulwilliamson at spamcop dot net for e-mail[/color]
>
>The 'Not In (Select... syntax is known to be slow for larger tables.
>If you use the wizard to create an unmatched query it will show you an
>alternate syntax for returning the same records using a LEFT JOIN that
>runs much faster.
>
>James A. Fortune[/color]

Hi James.

I did as you suggested and converted this (from the wizard SQL
Syntax):

SELECT tblHistoryPerso nIDs.PersonID
FROM tblHistoryPerso nIDs LEFT JOIN tblDonations ON
tblHistoryPerso nIDs.PersonID = tblDonations.Pe rsonID
WHERE (((tblDonations .PersonID) Is Null));

To:

DoCmd.RunSQL "Delete * From tblHistoryPerso nIDs LEFT JOIN
tblDonations ON tblHistoryPerso nIDs.PersonID = tblDonations.Pe rsonID
WHERE tblDonations.[DonationDate] Not Between #" & dFromDate_pw & "#
And #" & dToDate_pw & "#"

And I am getting "Specify the table containing the records you want to
delete". Can't figure it out. Am getting sleepy ;-0

Any ideas?





-pw

use paulwilliamson at spamcop dot net for e-mail

[jimfortune@compumarc.com]

Re: RunSQL Delete is locking up/taking forever

paulwilliamsonr emove@removespa mcop.net wrote:[color=blue]
> On 16 Nov 2005 15:20:27 -0800, jimfortune@comp umarc.com wrote:
>[color=green]
> >paulwilliamson remove@removesp amcop.net wrote:[color=darkred]
> >> Hi,
> >>
> >> Using Access 2003.
> >>
> >> I am trying to delete records from one table/query
> >> ("qryHistoryPer sonIDs") if they don't exist in another table
> >> ("qryDonations" ). But the SQL syntax I came up with is taking forever
> >> or locking up Access 2003.
> >>
> >> What am I doing wrong?
> >>
> >> The code is below. dFromDate_pw and dToDate_pw are set to values of
> >> date fields on a form. qryHistoryPerso nIDs contains one key field:
> >>
> >> DoCmd.RunSQL "Delete * From qryHistoryPerso nIDs Where
> >> qryHistoryPerso nIDs.personID Not In (Select qryDonations.pe rsonid From
> >> qryDonations Where tblDonations.[DonationDate] Not Between #" &
> >> dFromDate_pw & "# And #" & dToDate_pw & "# )"
> >>
> >> I'd rather not manually read through the table as it has 4,000 records
> >> and is growing.
> >>
> >> Any ideas?
> >>
> >> -pw
> >>
> >> use paulwilliamson at spamcop dot net for e-mail[/color]
> >
> >The 'Not In (Select... syntax is known to be slow for larger tables.
> >If you use the wizard to create an unmatched query it will show you an
> >alternate syntax for returning the same records using a LEFT JOIN that
> >runs much faster.
> >
> >James A. Fortune[/color]
>
> Hi James.
>
> I did as you suggested and converted this (from the wizard SQL
> Syntax):
>
> SELECT tblHistoryPerso nIDs.PersonID
> FROM tblHistoryPerso nIDs LEFT JOIN tblDonations ON
> tblHistoryPerso nIDs.PersonID = tblDonations.Pe rsonID
> WHERE (((tblDonations .PersonID) Is Null));
>
> To:
>
> DoCmd.RunSQL "Delete * From tblHistoryPerso nIDs LEFT JOIN
> tblDonations ON tblHistoryPerso nIDs.PersonID = tblDonations.Pe rsonID
> WHERE tblDonations.[DonationDate] Not Between #" & dFromDate_pw & "#
> And #" & dToDate_pw & "#"
>
> And I am getting "Specify the table containing the records you want to
> delete". Can't figure it out. Am getting sleepy ;-0
>
> Any ideas?
>
> -pw
>
> use paulwilliamson at spamcop dot net for e-mail[/color]

I didn't check your unmatched query, but when you get the unmatched
records the 'Not In (SELECT...' switches to 'IN (SELECT
tblHistoryPerso nIDs.PersonID ...

That is, the unmatched query should return the ones that are 'Not In'
so they are now 'In.'

I explained this in:

http://groups.google.com/group/comp.databases.ms-access/msg/d663454c21472dc3

but I didn't do that great a job of explaining it there either. If
that doesn't help, post back and I'll organize my thoughts into
something a little more elegant and understandable. BTW, the ecommerce
code in that thread was from about six years ago and I cringed when I
saw it.

James A. Fortune

[paulwilliamsonremove@removespamcop.net]

Re: RunSQL Delete is locking up/taking forever

On 17 Nov 2005 00:02:35 -0800, jimfortune@comp umarc.com wrote:
[color=blue]
>paulwilliamson remove@removesp amcop.net wrote:[color=green]
>> On 16 Nov 2005 15:20:27 -0800, jimfortune@comp umarc.com wrote:
>>[color=darkred]
>> >paulwilliamson remove@removesp amcop.net wrote:
>> >> Hi,
>> >>
>> >> Using Access 2003.
>> >>
>> >> I am trying to delete records from one table/query
>> >> ("qryHistoryPer sonIDs") if they don't exist in another table
>> >> ("qryDonations" ). But the SQL syntax I came up with is taking forever
>> >> or locking up Access 2003.
>> >>
>> >> What am I doing wrong?
>> >>
>> >> The code is below. dFromDate_pw and dToDate_pw are set to values of
>> >> date fields on a form. qryHistoryPerso nIDs contains one key field:
>> >>
>> >> DoCmd.RunSQL "Delete * From qryHistoryPerso nIDs Where
>> >> qryHistoryPerso nIDs.personID Not In (Select qryDonations.pe rsonid From
>> >> qryDonations Where tblDonations.[DonationDate] Not Between #" &
>> >> dFromDate_pw & "# And #" & dToDate_pw & "# )"
>> >>
>> >> I'd rather not manually read through the table as it has 4,000 records
>> >> and is growing.
>> >>
>> >> Any ideas?
>> >>
>> >> -pw
>> >>
>> >> use paulwilliamson at spamcop dot net for e-mail
>> >
>> >The 'Not In (Select... syntax is known to be slow for larger tables.
>> >If you use the wizard to create an unmatched query it will show you an
>> >alternate syntax for returning the same records using a LEFT JOIN that
>> >runs much faster.
>> >
>> >James A. Fortune[/color]
>>
>> Hi James.
>>
>> I did as you suggested and converted this (from the wizard SQL
>> Syntax):
>>
>> SELECT tblHistoryPerso nIDs.PersonID
>> FROM tblHistoryPerso nIDs LEFT JOIN tblDonations ON
>> tblHistoryPerso nIDs.PersonID = tblDonations.Pe rsonID
>> WHERE (((tblDonations .PersonID) Is Null));
>>
>> To:
>>
>> DoCmd.RunSQL "Delete * From tblHistoryPerso nIDs LEFT JOIN
>> tblDonations ON tblHistoryPerso nIDs.PersonID = tblDonations.Pe rsonID
>> WHERE tblDonations.[DonationDate] Not Between #" & dFromDate_pw & "#
>> And #" & dToDate_pw & "#"
>>
>> And I am getting "Specify the table containing the records you want to
>> delete". Can't figure it out. Am getting sleepy ;-0
>>
>> Any ideas?
>>
>> -pw
>>
>> use paulwilliamson at spamcop dot net for e-mail[/color]
>
>I didn't check your unmatched query, but when you get the unmatched
>records the 'Not In (SELECT...' switches to 'IN (SELECT
>tblHistoryPers onIDs.PersonID ...
>
>That is, the unmatched query should return the ones that are 'Not In'
>so they are now 'In.'
>[/color]

That hurts! <g>
[color=blue]
>I explained this in:
>
>http://groups.google.com/group/comp....63454c21472dc3
>
>but I didn't do that great a job of explaining it there either. If
>that doesn't help, post back and I'll organize my thoughts into
>something a little more elegant and understandable.[/color]

Okay. Go ahead ;-)
[color=blue]
> BTW, the ecommerce
>code in that thread was from about six years ago and I cringed when I
>saw it.
>[/color]

Then I'll have to check it out for sure! :)

Thanks again,

-paul
[color=blue]
>James A. Fortune[/color]
-pw

use paulwilliamson at spamcop dot net for e-mail

[paulwilliamsonremove@removespamcop.net]

Re: RunSQL Delete is locking up/taking forever

>[color=blue]
>but I didn't do that great a job of explaining it there either. If
>that doesn't help, post back and I'll organize my thoughts into
>something a little more elegant and understandable.[/color]

Oops. I had a brain lapse and started a new thread instead of
replying to you. Oh well. Will be interesting to see if I get any
bites.

Thanks James,

-paul
-pw

use paulwilliamson at spamcop dot net for e-mail

[paulwilliamsonremove@removespamcop.net]

Re: RunSQL Delete is locking up/taking forever

On 17 Nov 2005 00:02:35 -0800, jimfortune@comp umarc.com wrote:
[color=blue]
>paulwilliamson remove@removesp amcop.net wrote:[color=green]
>> On 16 Nov 2005 15:20:27 -0800, jimfortune@comp umarc.com wrote:
>>[color=darkred]
>> >paulwilliamson remove@removesp amcop.net wrote:
>> >> Hi,
>> >>
>> >> Using Access 2003.
>> >>
>> >> I am trying to delete records from one table/query
>> >> ("qryHistoryPer sonIDs") if they don't exist in another table
>> >> ("qryDonations" ). But the SQL syntax I came up with is taking forever
>> >> or locking up Access 2003.
>> >>
>> >> What am I doing wrong?
>> >>
>> >> The code is below. dFromDate_pw and dToDate_pw are set to values of
>> >> date fields on a form. qryHistoryPerso nIDs contains one key field:
>> >>
>> >> DoCmd.RunSQL "Delete * From qryHistoryPerso nIDs Where
>> >> qryHistoryPerso nIDs.personID Not In (Select qryDonations.pe rsonid From
>> >> qryDonations Where tblDonations.[DonationDate] Not Between #" &
>> >> dFromDate_pw & "# And #" & dToDate_pw & "# )"
>> >>
>> >> I'd rather not manually read through the table as it has 4,000 records
>> >> and is growing.
>> >>
>> >> Any ideas?
>> >>
>> >> -pw
>> >>
>> >> use paulwilliamson at spamcop dot net for e-mail
>> >
>> >The 'Not In (Select... syntax is known to be slow for larger tables.
>> >If you use the wizard to create an unmatched query it will show you an
>> >alternate syntax for returning the same records using a LEFT JOIN that
>> >runs much faster.
>> >
>> >James A. Fortune[/color]
>>
>> Hi James.
>>
>> I did as you suggested and converted this (from the wizard SQL
>> Syntax):
>>
>> SELECT tblHistoryPerso nIDs.PersonID
>> FROM tblHistoryPerso nIDs LEFT JOIN tblDonations ON
>> tblHistoryPerso nIDs.PersonID = tblDonations.Pe rsonID
>> WHERE (((tblDonations .PersonID) Is Null));
>>
>> To:
>>
>> DoCmd.RunSQL "Delete * From tblHistoryPerso nIDs LEFT JOIN
>> tblDonations ON tblHistoryPerso nIDs.PersonID = tblDonations.Pe rsonID
>> WHERE tblDonations.[DonationDate] Not Between #" & dFromDate_pw & "#
>> And #" & dToDate_pw & "#"
>>
>> And I am getting "Specify the table containing the records you want to
>> delete". Can't figure it out. Am getting sleepy ;-0
>>
>> Any ideas?
>>
>> -pw
>>
>> use paulwilliamson at spamcop dot net for e-mail[/color]
>
>I didn't check your unmatched query, but when you get the unmatched
>records the 'Not In (SELECT...' switches to 'IN (SELECT
>tblHistoryPers onIDs.PersonID ...
>
>That is, the unmatched query should return the ones that are 'Not In'
>so they are now 'In.'
>
>I explained this in:
>
>http://groups.google.com/group/comp....63454c21472dc3
>
>but I didn't do that great a job of explaining it there either. If
>that doesn't help, post back and I'll organize my thoughts into
>something a little more elegant and understandable. BTW, the ecommerce
>code in that thread was from about six years ago and I cringed when I
>saw it.
>
>James A. Fortune[/color]

I got it James:


strSQL_pw = _
"DELETE * From tblHistoryPerso nIDs " & _
"WHERE NOT EXISTS " & _
"(SELECT * " & _
"FROM tblDonations " & _
"WHERE tblDonations.Pe rsonID =
tblHistoryPerso nIDs.PersonID " & _
"AND (DonationDate < #" & dFromDate_pw & "# " & _
"OR DonationDate > #" & dToDate_pw & "#))"

DoCmd.RunSQL strSQL_pw

And it ran quickly when I put in a date range to delete all the
records.

Phew! That was an important stumbling block because I have to do it
for a bunch of other combinations on this form.

Thanks!

-paul
-pw

use paulwilliamson at spamcop dot net for e-mail

[jimfortune@compumarc.com]

Re: RunSQL Delete is locking up/taking forever

paulwilliamsonr emove@removespa mcop.net wrote:[color=blue]
> On 17 Nov 2005 00:02:35 -0800, jimfortune@comp umarc.com wrote:[/color]
[color=blue]
> I got it James:
>
>
> strSQL_pw = _
> "DELETE * From tblHistoryPerso nIDs " & _
> "WHERE NOT EXISTS " & _
> "(SELECT * " & _
> "FROM tblDonations " & _
> "WHERE tblDonations.Pe rsonID =
> tblHistoryPerso nIDs.PersonID " & _
> "AND (DonationDate < #" & dFromDate_pw & "# " & _
> "OR DonationDate > #" & dToDate_pw & "#))"
>
> DoCmd.RunSQL strSQL_pw
>
> And it ran quickly when I put in a date range to delete all the
> records.
>
> Phew! That was an important stumbling block because I have to do it
> for a bunch of other combinations on this form.
>
> Thanks!
>
> -paul
> -pw
>
> use paulwilliamson at spamcop dot net for e-mail[/color]

I was dubious about the 'Not Between' also. I'm glad you found a way
to do it efficiently. The way I suggested should also have worked
quite well though.

James A. Fortune

[paulwilliamsonremove@removespamcop.net]

Re: RunSQL Delete is locking up/taking forever

On 17 Nov 2005 14:16:59 -0800, jimfortune@comp umarc.com wrote:
[color=blue]
>paulwilliamson remove@removesp amcop.net wrote:[color=green]
>> On 17 Nov 2005 00:02:35 -0800, jimfortune@comp umarc.com wrote:[/color]
>[color=green]
>> I got it James:
>>
>>
>> strSQL_pw = _
>> "DELETE * From tblHistoryPerso nIDs " & _
>> "WHERE NOT EXISTS " & _
>> "(SELECT * " & _
>> "FROM tblDonations " & _
>> "WHERE tblDonations.Pe rsonID =
>> tblHistoryPerso nIDs.PersonID " & _
>> "AND (DonationDate < #" & dFromDate_pw & "# " & _
>> "OR DonationDate > #" & dToDate_pw & "#))"
>>
>> DoCmd.RunSQL strSQL_pw
>>
>> And it ran quickly when I put in a date range to delete all the
>> records.
>>
>> Phew! That was an important stumbling block because I have to do it
>> for a bunch of other combinations on this form.
>>
>> Thanks!
>>
>> -paul
>> -pw
>>
>> use paulwilliamson at spamcop dot net for e-mail[/color]
>
>I was dubious about the 'Not Between' also. I'm glad you found a way
>to do it efficiently. The way I suggested should also have worked
>quite well though.
>
>James A. Fortune[/color]

Yeah, that Not Between was a killer. Thank you much for your help!

-pw

use paulwilliamson at spamcop dot net for e-mail