[AccessD] Update Query - Need to Speed It Up

BerichtThat might actually slow things down depending on the cardinality
(measure of the uniqueness) of the index.  If there are only a few values
(like a yes/no field), it's faster to scan the table.

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 Erwin Craps
  Sent: Monday, June 02, 2003 5:07 PM
  To: 'accessd at databaseadvisors.com'
  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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030602/83733d44/attachment-0001.html>