[AccessD] Update Query - Need to Speed It Up

Erwin Craps Erwin.Craps at ithelps.be
Tue Jun 3 00:58:14 CDT 2003


Had same prob, less records (700.000)
 
Keep the golden rule in mind.
Update/change as less as you can. Updating means locking and writing.
Writing is always slower than reading.
And its less dangerous in a multi user environment... 
 
-----Oorspronkelijk bericht-----
Van: Rocky Smolin - Beach Access Software [mailto:bchacc at san.rr.com] 
Verzonden: maandag 2 juni 2003 23:59
Aan: accessd at databaseadvisors.com
Onderwerp: Re: [AccessD] Update Query - Need to Speed It Up


Erwin:
 
That helped immensely.  Knocked the time down from 8:10 to 1:28. The fields
were already indexed.
 
Much obliged.
 
Rocky
 

----- Original Message ----- 
From: Erwin  <mailto:Erwin.Craps at ithelps.be> Craps 
To: 'accessd at databaseadvisors.com' <mailto:'accessd at databaseadvisors.com'>  
Sent: Monday, June 02, 2003 2:06 PM
Subject: RE: [AccessD] Update Query - Need to Speed It Up

put a filter on both fields for <> "" or <> 0 could speed thing up.
This for updating only the records that are not already OK.
This resulting that you will update less than 2,5M records.
if necesary put indexes on fields..
 
Updating is slower than filtering...
 
 
 
 
-----Oorspronkelijk bericht-----
Van: Jim Dettman [mailto:jimdettman at earthlink.net] 
Verzonden: maandag 2 juni 2003 22:56
Aan: accessd at databaseadvisors.com
Onderwerp: RE: [AccessD] Update Query - Need to Speed It Up


What version of Access?
 

Jim Dettman
President,
Online Computer Services of WNY, Inc.
(315) 699-3443
jimdettman at earthlink.net 

-----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 4:47 PM
To: AccessD at databaseadvisors.com
Subject: [AccessD] Update Query - Need to Speed It Up


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


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030603/9864fa89/attachment-0001.html>


More information about the AccessD mailing list