[AccessD] Update Query - Need to Speed It Up

John Colby jcolby at colbyconsulting.com
Mon Jun 2 22:43:46 CDT 2003


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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030602/660c292f/attachment-0001.html>


More information about the AccessD mailing list