Results 1 to 4 of 4
  1. #1
    Jjt is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2018
    Posts
    2

    VB6 query hangs at random times

    I have a one table MS Jet database that has 6 text fields (25 char size for each), that is created and managed by a VB6 application using DAO 2.5/3.51 Compatibility Library. Over the years, this database has increased in the record count (25MD database with 420,000 at this time) and is expected to continue to grow for some time. All was well until recently, where the very simple query (SELECT * FROM mytable WHERE myfieldx = 'the data', normally taking about 250 millisec or less to run, now has random periods of time where it take seconds (in some cases minutes) to run and hangs the VB app during that time. I have found that the delay occurs at the db.openrecordset(sql) statement in the VB app. I can't understand what is causing the query to sometimes become delayed.




    I put together a small test app to repeat the process over 500 loops against a copy of the database where it does not change. With the same exact process, open the database, call the same db.openrecordset(sql), loop through all the resulting records (same expected records are always returned), close the table and database, set database to nothing, and then repeat this 500 times, I usually end up with a delay on several of the loops, where all others were less than 250ms. The delay is always occurring at the db.openrecordset(sql) call. If I reduce the database to 12MB with 210,000 records in the table, I never see get any delays.


    This seems to occur on Windows 7, XP, Windows 2003 Server, and in test conditions, where there are no other apps running on the PC and where Anti-virus is disabled, there are no delays in 99.5% of the time, but it's this 0.5% that is troubling me. Is the Windows OS or MS Jet doing something on occasion to interfere where some registry setting may be involved?


    I looked for other forum threads but found none having similar situations and I'm not sure if this is a normal Jet behavior. My next step will be to break this database into multiple smaller databases, or convert to SQL Server, unless there are some further suggestions as to what I might look into. I can post my test loop code if needed.

    Regards,
    jjt

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    Just some suggestions

    inappropriate or non existent indexing.

    WHERE myfieldx = 'the data'
    WHERE myfieldx Like 'the data*'

    will make use of indexing if available, otherwise the db will do a sequential search

    Windows 2003 Server is pretty old now, it may well not have the capabilities of more modern versions. Also if being used for other things, those may take precedence of your activity, slowing your activity down.

    Jet is also long in the teeth - superceded with ACE in 2007, tho not sure of the relevance between an Access JET database and SQL Server

  3. #3
    Jjt is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2018
    Posts
    2
    Thanks for the suggestions.

    I do not have any indexing in this database, so I'll try that as well, but I did run the test using

    WHERE myfieldx Like 'the data*'

    and it takes slightly longer to run (but still very fast) and no more random delays! I don't understand this at all, but it seems to be a very solid, where-as using the equal in the query sends it off into some varied process that I have no idea what it is doing.

    I need to run a similar test with the newer Windows Server versions and see what those results are, and it would be nice to know why the behavior difference exists with what I am seeing thus far, but for now, I think you have solve my immediate problem and I do thank you!

    Regards,
    Jjt

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Are you closing the rs and setting to nothing in between those loops?
    You might glean something from here http://allenbrowne.com/QueryPerfIssue.html which may be relevant to your situation. 420K records doesn't sound to onerous to me; at least not with any architecture that's circa 2007 or later. Something that runs slow 0.5% of the time sounds like a network issue. Perhaps something is going on when you try to use the resources. Suggest you document the time/date for a few occasions and see if the server admin can look into the demand on the system at those times (if that data is saved or can be back-tracked) or find out if it can be looked at when the query runs. I once dealt with an admin after investigating as much as possible at my end, only to find that someone else there had throttled back resources so that his stuff ran faster.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 05-29-2015, 11:16 AM
  2. Replies: 2
    Last Post: 02-25-2015, 05:02 PM
  3. Query hangs
    By zur in forum Programming
    Replies: 3
    Last Post: 12-23-2014, 12:16 PM
  4. Query hangs when trying to run
    By scorpion99 in forum Access
    Replies: 1
    Last Post: 09-12-2014, 08:32 AM
  5. Random Query
    By TrackStar in forum Queries
    Replies: 0
    Last Post: 06-20-2012, 09:10 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums