[AccessD] Update Query - Need to Speed It Up

John Colby jcolby at colbyconsulting.com
Tue Jun 3 06:40:22 CDT 2003


What I'm trying to say is that if you just needed to update ALL the records
in the dataset (add 5% to a cost for example), then dropping the index,
updating, then adding the index back would probably be faster.  In your
case, if you have a filter that says "only those not 0" (for example) and
you drop the index, then it takes LONGER to find those records that are "not
0" and thus dropping the index SLOWS the thing down (as you observed).

If in one case (with an index) you say "update all records <> 0" and in the
other case you just drop the index on the column that might be 0 and then
just update ALL the records to be zero... then which is faster?
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: Tuesday, June 03, 2003 12:05 AM
  To: accessd at databaseadvisors.com
  Subject: Re: [AccessD] Update Query - Need to Speed It Up


  Not sure I understand 'different data sets'.  The test I ran was run on
the same data - same conditions, etc.  of the 2.5 million records, there
were only a handful anyway in which the fields were not "" and 0.  So it's
mostly the filtered retrieval not the update that's being measured.  But in
both cases, I'm updating the same records.

  Rocky

    ----- Original Message -----
    From: John Colby
    To: accessd at databaseadvisors.com
    Sent: Monday, June 02, 2003 8:43 PM
    Subject: RE: [AccessD] Update Query - Need to Speed It Up


    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



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


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


More information about the AccessD mailing list