Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Inconsistent Query Behavior (Access2000 SR-1)

    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 <img src=/S/bash.gif border=0 alt=bash width=35 height=39>

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Inconsistent Query Behavior (Access2000 SR-1)

    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.
    Charlotte

  3. #3
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inconsistent Query Behavior (Access2000 SR-1)

    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.

    <img src=/S/bash.gif border=0 alt=bash width=35 height=39> Tom Bushaw

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Inconsistent Query Behavior (Access2000 SR-1)

    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. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

Posting Permissions

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