• Inconsistent Query Behavior (Access2000 SR-1)

    Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » Inconsistent Query Behavior (Access2000 SR-1)

    Author
    Topic
    WSbushaw
    AskWoody Lounger
    July 23, 2001 at 8:31 pm #358325

    I copied my .mdb file to another computer that is configured similarly (memory-, processor-, and Office2K-wise). When I run a particular query on that computer, it runs about 40x SLOWER than on the other computer.

    Investigating further, I find that the subject query references another query that includes a VBA function call. The query that runs fast only calls this function twice. The query that runs slow seems to call it for every record of the query output (about 2600 times!). I suspect this is the source of the slowdown. But why are the two versions acting differently??? I simply copied the .mdb file from one machine to the other and I get this difference in behavior. Is there something in the Access Tools…Options that controls such things? Any help would be very much appreciated.

    Tom Bushaw bash

    Reply | Quote
    Viewing 0 reply threads
    Author
    Replies
    • WScharlotte
      AskWoody Lounger
      July 24, 2001 at 1:40 am #534391

      Copy it back to your computer and see whether there’s a difference in behavior between the two. Is the database entirely local to the machines or is there a back end or library on the network? Check the temp folder on the slow machine and also take a look at the virtual memory settings on both machines to see whether those compare.

      Reply | Quote
      • WSbushaw
        AskWoody Lounger
        July 27, 2001 at 2:37 pm #534957

        I tried your suggestions but, alas, to no avail. However, I have found the source of the problem, but can’t explain it.

        I created a bare-bones database that reproduced the behavior. The key seems to be: if you have a VBA function call in your query that is not record-contents-dependent (in principle, not a good coding practice anyway, it seems), this behavior seems to appear.

        On one computer the function is called once for every record in the table. On the other computer, the function is called only once (it appears to be “smart enough” to know that there is no by-record dependency). If the table is big and/or the function call eats up time, then the performance between the two can be VERY different. In my “real” database, I observed about a 40x difference. Both computers are running the same versions/builds of Access 2000 and VBA. What gives?

        Lo and behold, I discovered that one of the computers was running Windows 98 and the other — WIndows 98 SE. The SE computer was running the query “smartly” and fast. I got our IT folks to install SE on the other machine and they now both behave the same way.

        Is it plausible that the version of WIndows would affect the way Access processes queries? Seems odd to me…

        Here’s the database I used to check this behavior:

        tblTestTable:
        Simple 1-field (named lngValue) with, say, 1000 records. Structure, etc. of this table doesn’t really matter.

        Query1:
        SELECT tblTestTable.lngValue, blnLogic() AS Logic
        FROM tblTestTable;

        Module:
        Option Compare Database
        Option Explicit
        Option Base 1

        Public lngTestCount As Long

        Public Function blnLogic() As Boolean
        blnLogic = True
        lngTestCount = lngTestCount + 1
        End Function

        Public Function blnTimeQueryTest() As Boolean
        Dim rst As Recordset
        lngTestCount = 0
        Set rst = CurrentDb.OpenRecordset(“Query1”, dbOpenDynaset)
        MsgBox “blnLogic() called ” & lngTestCount & ” times.”, _
        vbInformation + vbOKOnly, “Query Timer”
        rst.Close
        blnTimeQueryTest = True
        End Funct

        Execute blnTimeQueryTest() from the VBA immediate window.

        Curiously, if the blnLogic() function is changed to return the long value of the counter, the output of the query shows “1” for every record, indicating the function was still called only once. To me, this is not intuitive behavior (even though it does run faster!). I would have expected the value to increment, record-by-record, as the query is evaluated.

        bash Tom Bushaw

        Reply | Quote
        • WScharlotte
          AskWoody Lounger
          July 27, 2001 at 4:20 pm #534980

          I know it seems weird, but yes, the version and even the service pack of Windows can make a huge difference in behavior.

          As an example, I had an Access 97 database that was supposed to look up a string in a table to determine “words” that should be in all caps, and I noticed that not all the strings were being found even though they existed in the table. Then I realized that the strings that were being skipped were the same as some file extensions, i.e., pdf (not a real example, but I can’t remember the real ones). We cured the problem by installing SP5 for NT 4.0. Once that was in place, all the strings were found, even those that coincidentally matched Windows-recognized file extensions. shrug

          Reply | Quote
    Viewing 0 reply threads
    Reply To: Inconsistent Query Behavior (Access2000 SR-1)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




DON'T MISS OUT!
Subscribe to the Free Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

View the Forum

  • Recent Replies
  • My Replies
  • My Active Topics
  • New Posts in the Last day
  • Private Messages
  • Knowledge Base
  • How to use the Forums
  • All Forums

    • Recent Topics

    My Profile

    March 2025
    S M T W T F S
     1
    2345678
    9101112131415
    16171819202122
    23242526272829
    3031  

    Remembering Woody

     

    Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.

    Mastodon profile for DefConPatch
    Mastodon profile for AskWoody

     

    Home • About • FAQ • Posts & Privacy • Forums • My Account
    Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts

    Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.