[AccessD] Update Query - Need to Speed It Up

Jim Dettman jimdettman at earthlink.net
Tue Jun 3 09:32:43 CDT 2003


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


More information about the AccessD mailing list