[AccessD] Update Query - Need to Speed It Up

Compact?

:)

Rocky

  ----- Original Message ----- 
  From: Jim Dettman 
  To: accessd at databaseadvisors.com 
  Sent: Tuesday, June 03, 2003 7:32 AM
  Subject: RE: [AccessD] Update Query - Need to Speed It Up


  Rocky,

     That's weird.  When's the last time you compacted?  The stats must be way off.

  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: Tuesday, June 03, 2003 10:12 AM
    To: accessd at databaseadvisors.com
    Subject: Re: [AccessD] Update Query - Need to Speed It Up


    Curiously, the stored query took almost twice as long as the SQL in code.

    Rocky

      ----- Original Message ----- 
      From: Jim Dettman 
      To: accessd at databaseadvisors.com 
      Sent: Tuesday, June 03, 2003 5:55 AM
      Subject: RE: [AccessD] Update Query - Need to Speed It Up


      Rocky,

        I've see you've already made progress, but you might also want to try saving the SQL as a querydef, then setting the UseTransaction property to no.  Depending on the number of records that are being changed, this may or may not yield an improvement.

        Beyond that, if your still looking for more performance, you can use the SetOption method of DAO to modify some of the JET engine settings (i.e.. MaxBuffers or PageTimeout).

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


        A2K
          ----- Original Message ----- 
          From: Jim Dettman 
          To: accessd at databaseadvisors.com 
          Sent: Monday, June 02, 2003 1:56 PM
          Subject: 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



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


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