[AccessD] Update Query - Need to Speed It Up

Rocky,

Since it doesn't make sense to me to try to change the PPV and
Variance's of all InvoiceDetail records, why don't you have a Where
clause to select only those that need changing?  Presumably some Invoice
record changed, so only its InvoiceDetail records now need updating.


Charles Wortz 
Software Development Division 
Texas Education Agency 
1701 N. Congress Ave 
Austin, TX 78701-1494 
512-463-9493 
CWortz at tea.state.tx.us 
-----Original Message-----
From: Rocky Smolin - Beach Access Software [mailto:bchacc at san.rr.com] 
Sent: Monday 2003 Jun 02 23:05
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Update Query - Need to Speed It Up


Not sure I understand 'different data sets'.  The test I ran was run on
the same data - same conditions, etc.  of the 2.5 million records, there
were only a handful anyway in which the fields were not "" and 0.  So
it's mostly the filtered retrieval not the update that's being measured.
But in both cases, I'm updating the same records.

Rocky

----- Original Message ----- 
From: John Colby 
To: accessd at databaseadvisors.com 
Sent: Monday, June 02, 2003 8:43 PM
Subject: RE: [AccessD] Update Query - Need to Speed It Up


Yes, but are you updating different data sets?  With indexes are you
only updating those needing updating, without indexes updating all?
John W. Colby
www.colbyconsulting.com 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Rocky Smolin -
Beach Access Software
Sent: Monday, June 02, 2003 11:25 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Update Query - Need to Speed It Up


John:

1:44 without indexes; 1:17 with indexes.  Go figure

Rocky

----- Original Message ----- 
From: Rocky Smolin - Beach Access Software 
To: accessd at databaseadvisors.com 
Sent: Monday, June 02, 2003 5:06 PM
Subject: Re: [AccessD] Update Query - Need to Speed It Up


Well I'll try a benchmark with no indexes - see if it's faster.

Rocky

----- Original Message ----- 
From: John Colby 
To: accessd at databaseadvisors.com 
Sent: Monday, June 02, 2003 4:03 PM
Subject: RE: [AccessD] Update Query - Need to Speed It Up


Rocky,

Part of the problem is that the fields are indexed.  Using the indexes
to select only the fields that need updating is appropriate of course if
it's not all the records that need updating.  The other option is to
delete the index, update, then rebuild the index.  Apparently after each
update (each record) Access foes in and adjusts the index.  Removing the
index entirely, doing the updates, then rebuilding the index from
scratch is SUPPOSED to be faster.  never actually tried it tho.
John W. Colby
www.colbyconsulting.com 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Rocky Smolin -
Beach Access Software
Sent: Monday, June 02, 2003 5:59 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Update Query - Need to Speed It Up


8:10.  But with Erwin's filter 1:28.  Next stop, I guess is MSDE.

Rocky

----- Original Message ----- 
From: Susan Harkins 
To: accessd at databaseadvisors.com 
Sent: Monday, June 02, 2003 2:08 PM
Subject: Re: [AccessD] Update Query - Need to Speed It Up


How long is too long? With 2 million records, and Jet, just how fast do
you want it to be??? :) 

And about that blank -- do you really mean a zero-length string or did
you really want Null?

Susan H. 


Dear List:

I am running an update query on a table with 2,500,000 records and it's
taking just a bit too long for the my and the client's liking.  
The SQL is:

UPDATE tblInvoiceDetail SET tblInvoiceDetail.fldInvoiceDetailPPV = """",
tblInvoiceDetail.fldInvoiceDetailVariancePercent = 0;

It's setting one field to blank the other to 0.

Is there a way to make it run any faster?


MTIA,

Rocky Smolin
Beach Access Software