All appears to work OK, John, and EXISTS seems a lot faster that my trying to use IN with so many records being returned by the subquery. I'll return if I run into more issues.
Thanks for the help!
——
James
www.james-mc.com
Words To Live By
From: James McAlister <kc5qeg@gmail.com>
Reply: James McAlister <kc5qeg@gmail.com>>
Date: August 5, 2014 at 10:48:08 AM
To: ms_access_professionals@yahoogroups.com <ms_access_professionals@yahoogroups.com>>
Subject: 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 slowlyJames-
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
Posted by: John Viescas <johnv@msn.com>Visit Your Group
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (2)
• Privacy • Unsubscribe • Terms of Use
.
__._,_.___
Posted by: James McAlister <kc5qeg@gmail.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (1) |
Yahoo Groups
New feature! Create Photo Albums in Groups Effortlessly
Now, whenever you share photos with your group, a new album is automatically created in the Group. It's so simple! Try it now!
.
__,_._,___
Tidak ada komentar:
Posting Komentar