ACCESS FEED: Re: [MS_AccessPros] Update query runs too slowly

Selasa, 05 Agustus 2014

Re: [MS_AccessPros] Update query runs too slowly

Thanks, John. I will look at this after I return this afternoon. It looks good, and I'll also check the indexes. I think I have them both set but am not sure. I do appreciate the help!!

——

James

www.james-mc.com

Words To Live By

From: John Viescas JohnV@msn.com [MS_Access_Professionals] <ms_access_professionals@yahoogroups.com>
Reply: ms_access_professionals@yahoogroups.com <ms_access_professionals@yahoogroups.com>>
Date: August 5, 2014 at 10:44:10 AM
To: ms_access_professionals@yahoogroups.com <ms_access_professionals@yahoogroups.com>>
Subject: Re: [MS_AccessPros] Update query runs too slowly

James-

Is there an index on the PhoneNumber and StateRepresentativeDistrict?

Note that the query as you have constructed it will only flag records where there are multiples for the same PhoneNumber AND district. If you just want to flag records that have more than one record for the same phone number, remove the WHERE clause from the subquery.

Per our previous discussion, you really want to flag only the records where the phone number appears more than once but the districts are different. Your query as stated won't do that. Removing the WHERE clause will cause it to run much faster, but it will flag all records where the phone number appears in more than one record. You could subsequently make another pass to "unflag" the records that all have the same district.

Query1:

UPDATE Registrations SET Registrations.xDupPhoneDistrict = 1

WHERE (((Registrations.PhoneNumber) In (SELECT [PhoneNumber] FROM [Registrations] As Tmp2

GROUP BY [PhoneNumber] HAVING Count(*)>1 )));

Query2:

UPDATE Registrations SET Registrations.xDupPhoneDistrict = 0

WHERE Registrations.xDupPhoneDistrict = 1

AND NOT EXISTS

(SELECT * FROM Registrations As T2

WHERE T2.PhoneNumber = Registrations.PhoneNumber

AND T2.StateRepresentativeDistrict <> Registrations.StateRepresentativeDistrict)

The first query flags all records where the same phone number exists in another record.

The second query unflags any flagged record where no other record exists that has a district different from the district in the current record.

That should leave you with duplicate phone number records flagged where the districts aren't the same.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Aug 5, 2014, at 10:15 AM, James McAlister kc5qeg@gmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I'm trying to run this update query on a 1.6 million record table, but it executes way too slowly. I let it run overnight for about 7 hours, and this morning the progress bar indicated that it was only about 25% done. I know that subqueries are slow, but this just won't work. I'm trying to flag records where phone numbers AND legislative district are duplicated.

Is there any other construct or approach I can use to update the field in a reasonable time? Using Access 2010 on Windows 7 Pro.

Thanks!!!

——

UPDATE Registrations SET Registrations.xDupPhoneDistrict = 1

WHERE (((Registrations.PhoneNumber) In (SELECT [PhoneNumber] FROM [Registrations] As Tmp2 WHERE Tmp2.PhoneNumber = Registrations.PhoneNumber AND Tmp2.StateRepresentativeDistrict = Registrations.StateRepresentativeDistrict GROUP BY [PhoneNumber] HAVING Count(*)>1 )));

——

James

www.james-mc.com

Words To Live By