Performance Issue

[Swi]

I am calling this SQL statement on my Access database using VB. However It is extremely SLOW.

DELETE *
FROM tblMain
WHERE tblMain.[Phone Number] Not In (Select tblSub.[Phone Number] from tblSub)

Any ideas on how to speed things up?

Thanks.

Swi

 

[dhookom]

Are both Phone Number fields indexed? Are either primary keys?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

Do you have a large number of records in [tt]tblMain[/tt]? [tt]tblSub[/tt]?
By 'large', I mean - millions?


---- Andy

There is a great need for a sarcasm font.

 

[Swi]

Hi, yes, both indexed and a few hundred thousand in tblMain and a few million in tblSub.

Thanks.

Swi

 

[dhookom]

Is the [Phone Number] unique in tblSub? If not, I would try run a make table query with unique phone numbers and make the phone number primary. Then use the new table rather than tblSub.

Otherwise since there are so many records, I would maybe try batches of records or a LEFT JOIN.

Millions of records in Access will take some time.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016