Rocky Smolin - Beach Access Software
bchacc at san.rr.com
Tue Jun 3 08:40:12 CDT 2003
Updating all the records without the index would be slow. I was originally doing it that way, then added the indexes to see if the update would run faster. It didn't appreciably. But updating all records without the index would probably be faster. OTOH there seems to be a bit of time consumed by creating the indexes as well.
Rocky
----- Original Message -----
From: John Colby
To: accessd at databaseadvisors.com
Sent: Tuesday, June 03, 2003 4:40 AM
Subject: RE: [AccessD] Update Query - Need to Speed It Up
What I'm trying to say is that if you just needed to update ALL the records in the dataset (add 5% to a cost for example), then dropping the index, updating, then adding the index back would probably be faster. In your case, if you have a filter that says "only those not 0" (for example) and you drop the index, then it takes LONGER to find those records that are "not 0" and thus dropping the index SLOWS the thing down (as you observed).
If in one case (with an index) you say "update all records <> 0" and in the other case you just drop the index on the column that might be 0 and then just update ALL the records to be zero... then which is faster?
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: Tuesday, June 03, 2003 12:05 AM
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
--------------------------------------------------------------
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
----------------------------------------------------------------
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--------------------------------------------------------------------
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
----------------------------------------------------------------------
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--------------------------------------------------------------------------
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
------------------------------------------------------------------------------
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030603/2109ac84/attachment-0001.html>