Erwin Craps
Erwin.Craps at ithelps.be
Tue Jun 3 01:04:51 CDT 2003
I believe John is right, but it will depend on how many records will need updating.... If you only have two records to update, deleting and creating the indexes will take more time... No indexes will slow down the filter.. -----Oorspronkelijk bericht----- Van: Rocky Smolin - Beach Access Software [mailto:bchacc at san.rr.com] Verzonden: dinsdag 3 juni 2003 5:25 Aan: accessd at databaseadvisors.com Onderwerp: 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 - <mailto:bchacc at san.rr.com> Beach Access Software To: accessd at databaseadvisors.com <mailto: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 <mailto:jcolby at colbyconsulting.com> To: accessd at databaseadvisors.com <mailto: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 <mailto:harkins at iglou.com> Harkins To: accessd at databaseadvisors.com <mailto: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 -------------- next part -------------- An HTML attachment was scrubbed... URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030603/97c7cdc4/attachment-0001.html>