Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How To Speed Up Query Code? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    I have the following code in Form AfterUpdate that returns the next highest unused inventory serial number and it works OK

    Within a session, the first time code is run it takes a (lifetime) 10 to 15 seconds to return a strTagID value

    The second and subsequence times it is run it takes 4 to 8 seconds to return a strTagID value

    What can I do to speed up this lookup?

    Thanks, John

    All criteria fields are indexed

    <pre>strTagID = DMin("strSerialNumber", _
    "qry_InventoryNextInputDateAndTagID", _
    "strInvCode = '" & strTagType & "'" & _
    " And DetailActive = -1")
    </pre>


    All qry_InventoryNextInputDateAndTagID connecting and criteria fields are indexed

    <pre>SELECT [tbl_InventoryDetail.dtmInputdate] & " "
    & [tbl_InventoryDetail.strSerialNumber] AS NextSerialNumber,
    tbl_InventoryDetail.strInvCode AS strInvCode,
    tbl_InventoryDetail.dtmInputdate,
    tbl_InventoryDetail.strSerialNumber,
    tbl_InventoryDetail.lngInvDetailID AS ID,
    tbl_RevTran_Detail.strRefNo,
    tbl_InventoryMaster.ysnActive,
    tbl_InventoryDetail.ysnActive AS DetailActive
    FROM (tbl_InventoryDetail LEFT JOIN tbl_RevTran_Detail
    ON tbl_InventoryDetail.strSerialNumber = tbl_RevTran_Detail.strRefNo)
    LEFT JOIN tbl_InventoryMaster
    ON tbl_InventoryDetail.lngInvMasterID = tbl_InventoryMaster.lngInvMasterID
    WHERE (((tbl_InventoryDetail.strInvCode)<>"BP")
    AND ((tbl_RevTran_Detail.strRefNo) Is Null)
    AND ((tbl_InventoryMaster.ysnActive)=Yes))
    ORDER BY tbl_InventoryDetail.strInvCode,
    tbl_InventoryDetail.dtmInputdate,
    tbl_InventoryDetail.strSerialNumber;
    </pre>


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: How To Speed Up Query Code? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    1) Have you compacted the database recently?

    2) The query qry_InventoryNextInputDateAndTagID computes more than is needed for the DMin. You could create a new query qryTest especially for the DMin:

    SELECT tbl_InventoryDetail.strSerialNumber,
    tbl_InventoryDetail.strInvCode AS strInvCode
    FROM (tbl_InventoryDetail LEFT JOIN tbl_RevTran_Detail
    ON tbl_InventoryDetail.strSerialNumber = tbl_RevTran_Detail.strRefNo)
    LEFT JOIN tbl_InventoryMaster
    ON tbl_InventoryDetail.lngInvMasterID = tbl_InventoryMaster.lngInvMasterID
    WHERE tbl_RevTran_Detail.strRefNo Is Null
    AND tbl_InventoryMaster.ysnActive=True
    AND tbl_InventoryDetail.ysnActive=True

    The instruction for strTagID would become

    strTagID = DMin("strSerialNumber", _
    "qryTest", "strInvCode = '" & strTagType & "'")

    3) Is your database split into a front end and back end? If so, try creating a persistent connection between them. See for example <post:=539,786>post 539,786</post:> and the thread starting at <post:=356,901>post 356,901</post:>.

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How To Speed Up Query Code? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Step 2 fixed the problem

    She

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: How To Speed Up Query Code? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    > The second and subsequence times it is runs faster than the first time, any reason.

    The Jet engine performs some kind of optimization the first time a query is run, so that subsequent runs are faster. It might also have to do with suggestion 3 from my previous reply.

  5. #5
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How To Speed Up Query Code? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    For Information

    Jet uses something called Rushmore optimization to work out the most efficient way to run a query. More information can be found here
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •