James-
Yes. The "benchmark" for performing an update of this sort on a full-blown
database server (like SQL Server or Oracle) is 1,000 transactions a second. I'm
actually surprised that a simple desktop database like Access can do 300 a
second! Keep in mind that the way you have structured the query, you are asking
Access to update every row in the existing table. It might be faster to load
the new data and then append any not found in the old data.
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of James McAlister
Sent: Wednesday, November 30, 2011 2:54 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Why won't this query run?
John,
Well, if YOU think it's pretty speedy, I'll not touch it! Having little
experience with what it ought to do, I just thought it "felt" slow. But
your analysis puts it into perspective!
Thanks!
James
www.james-mc.com
Words To Live By
On Wed, Nov 30, 2011 at 1:31 AM, John Viescas <john@viescas.com> wrote:
> **
>
>
> James-
>
> Even if it took 90 minutes, that's pretty speedy! Assuming it took 90
> minutes,
> that's 300 rows being updated PER SECOND. That time sounds OK to me.
>
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of James
> McAlister
> Sent: Tuesday, November 29, 2011 11:27 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] Why won't this query run?
>
> John, both fields do have indexes (no duplicates). The next time I have to
> do this I may try doing the update part without the outer join to see if
> that makes any difference in speed. I left the query running and ran some
> errands and am guessing that it took 60 to 90 minutes.
>
> James
> www.james-mc.com
> Words To Live By
>
> On Tue, Nov 29, 2011 at 2:33 PM, John Viescas <john@viescas.com> wrote:
>
> > **
> >
> >
> > James-
> >
> > If there's an index on Registrations.TEXT_REGISTRANT_ID and on
> > Import.VoterID
> > (especially if one or both indexes is unique) it should run pretty
> quickly.
> >
> >
> > John Viescas, author
> > Microsoft Office Access 2010 Inside Out
> > Microsoft Office Access 2007 Inside Out
> > Building Microsoft Access Applications
> > Microsoft Office Access 2003 Inside Out
> > SQL Queries for Mere Mortals
> > http://www.viescas.com/
> > (Paris, France)
> >
> > -----Original Message-----
> > From: MS_Access_Professionals@yahoogroups.com
> > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of James
> > McAlister
> > Sent: Tuesday, November 29, 2011 9:23 PM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: Re: [MS_AccessPros] Why won't this query run?
> >
> > This worked quite well with my small (100 record) Import table. Fully
> > populated, however, the Import table has about 1.6 million records, as
> does
> > the Registrations table. The query is running exceedingly slow (looks
> like
> > it may take hours), and I'm wondering if that is because of the left
> outer
> > join? Would it be more efficient (faster) to do this in two steps: 1)
> > append new records and 2) then run a regular update query?
> >
> > This is Access 2010 on Windows 7 Pro with 4GB RAM, so I was expecting it
> to
> > move right along. I've rebooted and have had a look at the CPU usage in
> > Task Manager thinking it would be maxed out, but it's 5% or less.
> >
> > Thanks for any ideas!
> >
> > James
> > www.james-mc.com
> > Words To Live By
> >
> > On Tue, Nov 29, 2011 at 11:02 AM, James McAlister <forums01@james-mc.com
> > >wrote:
> >
> > > I'll bet that's it also. If not, I'll be back!
> > >
> > >
> > > Thanks!!
> > >
> > > James
> > > www.james-mc.com
> > > Words To Live By
> > >
> > >
> > > On Tue, Nov 29, 2011 at 10:58 AM, John Viescas <john@viescas.com>
> wrote:
> > >
> > >> **
> >
> > >>
> > >>
> > >> James-
> > >>
> > >> If TEXT_REGISTRANT_ID is the Primary Key of Registrations, then you
> MUST
> > >> set it
> > >> in the SET clause. If there are records in Import that have a VoterID
> > >> that's
> > >> not found in TEXT_REGISTRANT_ID, Access will attempt to add the
> records
> > >> for you,
> > >> but only if you set ALL the required fields in Registrations. I
> suspect
> > >> that's
> > >> the problem.
> > >>
> > >> John Viescas, author
> > >> Microsoft Office Access 2010 Inside Out
> > >> Microsoft Office Access 2007 Inside Out
> > >> Building Microsoft Access Applications
> > >> Microsoft Office Access 2003 Inside Out
> > >> SQL Queries for Mere Mortals
> > >> http://www.viescas.com/
> > >> (Paris, France)
> > >>
> > >>
> > >> -----Original Message-----
> > >> From: MS_Access_Professionals@yahoogroups.com
> > >> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of James
> > >> McAlister
> > >> Sent: Tuesday, November 29, 2011 5:30 PM
> > >> To: MS_Access_Professionals@yahoogroups.com
> > >> Subject: [MS_AccessPros] Why won't this query run?
> > >>
> > >> In trying to execute the following query, I get an error: "Microsoft
> > >> Access can't update all records in the update query." Then follows a
> > >> statement that 16 records can't be updated due to key violations.
> > >>
> > >> At first I thought that there might be nulls in either Import.VoterID
> > >> or Registrations.TEXT_REGISTRANT_ID, but that's not the case. I do
> > >> have a field Import.TEXT_REGISTRANT_ID, however, which has the same
> > >> number of nulls as the number of records that won't update--and they
> > >> are the same records that are missing when I view the query in
> > >> datasheet view.
> > >>
> > >> So how is this happening since Import.TEXT_REGISTRANT_ID doesn't
> > >> factor into the query? Am I missing a table setting somewhere?
> > >>
> > >> Just for background, my past work with these voter files has always
> > >> utilized Import.TEXT_REGISTRANT_ID and
> > >> Registrations.TEXT_REGISTRANT_ID as the join fields (as they
> > >> consistently contained the unique voter ID) in my queries. This time,
> > >> however, whoever prepared the files I need to import at the secretary
> > >> of state's office added a new field, VoterID, with the proper voter ID
> > >> number in it but left the old TEXT_REGISTRANT_ID that had nulls for
> > >> voters with no actual vote history. But why should that be a problem
> > >> if I'm not using Import.TEXT_REGISTRANT_ID in the query?
> > >>
> > >> By the way, Import is a linked table in another Access file into which
> > >> I have imported the voter registration data. I'm testing this on a
> > >> small file of 100 imported records (16 of which don't import), but the
> > >> real file has about 1.5 million records with about 300,000 that don't
> > >> import.
> > >>
> > >> I hope this is clear. There's something basic that I'm missing or
> > >> don't understand and would appreciate any insight.
> > >>
> > >> UPDATE Import AS I LEFT JOIN Registrations AS R ON I.VoterID =
> > >> R.TEXT_REGISTRANT_ID SET R.County = [I].[County],
> > >> R.CDE_REGISTRANT_STATUS = [I].[CDE_REGISTRANT_STATUS],
> > >> R.CDE_REGISTRANT_REASON = [I].[CDE_REGISTRANT_REASON], R.date_of_birth
> > >> = [I].[date_of_birth], R.date_of_registration =
> > >> [I].[date_of_registration], R.TEXT_NAME_LAST = [I].[TEXT_NAME_LAST],
> > >> R.TEXT_NAME_FIRST = [I].[TEXT_NAME_FIRST], R.TEXT_NAME_MIDDLE =
> > >> [I].[TEXT_NAME_MIDDLE], R.CDE_NAME_SUFFIX = [I].[CDE_NAME_SUFFIX],
> > >> R.TEXT_RES_ADDRESS_NBR = [I].[TEXT_RES_ADDRESS_NBR],
> > >> R.TEXT_RES_ADDRESS_NBR_SUFFIX = [I].[TEXT_RES_ADDRESS_NBR_SUFFIX],
> > >> R.CDE_STREET_DIR_PREFIX = [I].[CDE_STREET_DIR_PREFIX],
> > >> R.TEXT_STREET_NAME = [I].[TEXT_STREET_NAME], R.DESC_STREET_TYPE =
> > >> [I].[DESC_STREET_TYPE], R.CDE_STREET_DIR_SUFFIX =
> > >> [I].[CDE_STREET_DIR_SUFFIX], R.DESC_UNIT_TYPE = [I].[DESC_UNIT_TYPE],
> > >> R.TEXT_RES_CITY = [I].[TEXT_RES_CITY], R.CDE_RES_STATE =
> > >> [I].[CDE_RES_STATE], R.TEXT_RES_ZIP5 = [I].[TEXT_RES_ZIP5],
> > >> R.TEXT_RES_ZIP4 = [I].[TEXT_RES_ZIP4], R.IND_MAIL = [I].[IND_MAIL],
> > >> R.PrecinctName = [I].[PrecinctName], R.PrecinctDesignationCode =
> > >> [I].[PrecinctDesignationCode], R.PrecinctPartName =
> > >> [I].[PrecinctPartName], R.PrecinctPartDesignationCode =
> > >> [I].[PrecinctPartDesignationCode], R.CongressionalDistrict =
> > >> [I].[CongressionalDistrict], R.StateSenateDistrict =
> > >> [I].[StateSenateDistrict], R.StateRepresentativeDistrict =
> > >> [I].[StateRepresentativeDistrict], R.CourtOfAppealsDistrict =
> > >> [I].[CourtOfAppealsDistrict], R.JudicialDistrict =
> > >> [I].[JudicialDistrict], R.DistrictCourtDistrict =
> > >> [I].[DistrictCourtDistrict], R.TownshipDistrict =
> > >> [I].[TownshipDistrict], R.JusticeOfThePeaceDistrict =
> > >> [I].[JusticeOfThePeaceDistrict], R.SchoolDistrict =
> > >> [I].[SchoolDistrict], R.SchoolDistrictZone = [I].[SchoolDistrictZone],
> > >> R.MunicipalityDistrict = [I].[MunicipalityDistrict], R.WardDistrict =
> > >> [I].[WardDistrict], R.DateLastVoted = [I].[DateLastVoted],
> > >> R.Primary2002 = [I].[Primary2002], R.Primary2002CountyVotedIn =
> > >> [I].[Primary2002CountyVotedIn], R.Primary2002PartyVoted =
> > >> [I].[Primary2002PartyVoted], R.Primary2002HowVoted =
> > >> [I].[Primary2002HowVoted], R.PrimaryRunoff2002 =
> > >> [I].[PrimaryRunoff2002], R.PrimaryRunoff2002CountyVotedIn =
> > >> [I].[PrimaryRunoff2002CountyVotedIn], R.PrimaryRunoff2002PartyVoted =
> > >> [I].[PrimaryRunoff2002PartyVoted], R.PrimaryRunoff2002HowVoted =
> > >> [I].[PrimaryRunoff2002HowVoted], R.General2002 = [I].[General2002],
> > >> R.General2002CountyVotedIn = [I].[General2002CountyVotedIn],
> > >> R.General2002PartyVoted = [I].[General2002PartyVoted],
> > >> R.General2002HowVoted = [I].[General2002HowVoted], R.Primary2004 =
> > >> [I].[Primary2004], R.Primary2004CountyVotedIn =
> > >> [I].[Primary2004CountyVotedIn], R.Primary2004PartyVoted =
> > >> [I].[Primary2004PartyVoted], R.Primary2004HowVoted =
> > >> [I].[Primary2004HowVoted], R.PrimaryRunoff2004 =
> > >> [I].[PrimaryRunoff2004], R.PrimaryRunoff2004CountyVotedIn =
> > >> [I].[PrimaryRunoff2004CountyVotedIn], R.PrimaryRunoff2004PartyVoted =
> > >> [I].[PrimaryRunoff2004PartyVoted], R.PrimaryRunoff2004HowVoted =
> > >> [I].[PrimaryRunoff2004HowVoted], R.General2004 = [I].[General2004],
> > >> R.General2004CountyVotedIn = [I].[General2004CountyVotedIn],
> > >> R.General2004PartyVoted = [I].[General2004PartyVoted],
> > >> R.General2004HowVoted = [I].[General2004HowVoted], R.Primary2006 =
> > >> [I].[Primary2006], R.Primary2006CountyVotedIn =
> > >> [I].[Primary2006CountyVotedIn], R.Primary2006PartyVoted =
> > >> [I].[Primary2006PartyVoted], R.Primary2006HowVoted =
> > >> [I].[Primary2006HowVoted], R.PrimaryRunoff2006 =
> > >> [I].[PrimaryRunoff2006], R.PrimaryRunoff2006CountyVotedIn =
> > >> [I].[PrimaryRunoff2006CountyVotedIn], R.PrimaryRunoff2006PartyVoted =
> > >> [I].[PrimaryRunoff2006PartyVoted], R.PrimaryRunoff2006HowVoted =
> > >> [I].[PrimaryRunoff2006HowVoted], R.General2006 = [I].[General2006],
> > >> R.General2006CountyVotedIn = [I].[General2006CountyVotedIn],
> > >> R.General2006PartyVoted = [I].[General2006PartyVoted],
> > >> R.General2006HowVoted = [I].[General2006HowVoted], R.PresPrimary2008 =
> > >> [I].[PresPrimary2008], R.PresPrimary2008CountyVotedIn =
> > >> [I].[PresPrimary2008CountyVotedIn], R.PresPrimary2008PartyVoted =
> > >> [I].[PresPrimary2008PartyVoted], R.PresPrimary2008HowVoted =
> > >> [I].[PresPrimary2008HowVoted], R.Primary2008 = [I].[Primary2008],
> > >> R.Primary2008CountyVotedIn = [I].[Primary2008CountyVotedIn],
> > >> R.Primary2008PartyVoted = [I].[Primary2008PartyVoted],
> > >> R.Primary2008HowVoted = [I].[Primary2008HowVoted],
> > >> R.General2008CountyVotedIn = [I].[General2008CountyVotedIn],
> > >> R.General2008PartyVoted = [I].[General2008PartyVoted],
> > >> R.General2008HowVoted = [I].[General2008HowVoted], R.Primary2010 =
> > >> [I].[Primary2010], R.Primary2010CountyVotedIn =
> > >> [I].[Primary2010CountyVotedIn], R.Primary2010PartyVoted =
> > >> [I].[Primary2010PartyVoted], R.Primary2010HowVoted =
> > >> [I].[Primary2010HowVoted], R.PrimaryRunoff2010 =
> > >> [I].[PrimaryRunoff2010], R.PrimaryRunoff2010CountyVotedIn =
> > >> [I].[PrimaryRunoff2010CountyVotedIn], R.PrimaryRunoff2010PartyVoted =
> > >> [I].[PrimaryRunoff2010PartyVoted], R.PrimaryRunoff2010HowVoted =
> > >> [I].[PrimaryRunoff2010HowVoted], R.General2010 = [I].[General2010],
> > >> R.General2010CountyVotedIn = [I].[General2010CountyVotedIn],
> > >> R.General2010PartyVoted = [I].[General2010PartyVoted],
> > >> R.General2010HowVoted = [I].[General2010HowVoted];
> > >>
> > >> Thanks!!
> > >>
> > >> James
> > >> www.james-mc.com
> > >> Words To Live By
> > >>
> > >> ------------------------------------
> > >>
> > >> Yahoo! Groups Links
> > >>
> > >>
> > >>
> > >
> > >
> >
> > [Non-text portions of this message have been removed]
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
> >
> >
>
> [Non-text portions of this message have been removed]
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar