[AccessD] Update Query - Need to Speed It Up

Don:

I thought about doing it for the two fields I'm updating.  But then the report would have to do a one-to-one join on the table with the two fields instead of just printing the two bound fields on the report.  Not sure I'd save anything that way.

Right now the UPDATE time is tolerable.  There's another chunk of code after that which compares the detail records to find the variances which can take several minutes to run.  That's where I'm going to look for a different technique.

For the moment - client is happy!!

Thanks to all who gave me input on this problem.

Regards,

Rocky Smolin
Beach Access Software

  ----- Original Message ----- 
  From: Don Elliker 
  To: accessd at databaseadvisors.com 
  Sent: Tuesday, June 03, 2003 6:18 AM
  Subject: Re: [AccessD] Update Query - Need to Speed It Up


  whew...2.5million records- there's your problem! Have you tried a maketable (create a new one delete the old) setting those fields when you do it? WAG..I know. Other than that how about setting some temporary indices (indexes) on those fields...might help.
  _D






  "Things are only free to the extent that you don't pay for them." 
  >From: "Rocky Smolin - Beach Access Software" 
  >Reply-To: accessd at databaseadvisors.com 
  >To: 
  >Subject: [AccessD] Update Query - Need to Speed It Up 
  >Date: Mon, 2 Jun 2003 13:47:27 -0700 
  > 
  >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 


------------------------------------------------------------------------------
  MSN 8 with e-mail virus protection service: 2 months FREE* 


------------------------------------------------------------------------------


  _______________________________________________
  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/cdb4e8f2/attachment-0001.html>