Drastic Increase in Response Time (1 Viewer)

[Bodisathva]

I have an 18.8MB Access application consisting of the following:
140 tables
410 Queries
67 Forms
5 Macros
26 Modules


It's not a lot of data storage, but is very computationally and mathematically intensive. As we continue to develop and expand the application, I noticed last week that we suddenly experienced a massive falloff in performance. Access is taking an incredibly long amount of time to traverse the querydefs collection, find objects, etc. It seems that the object collections themselves are not indexed, meaning that it takes a much longer amount of time to run a compiled and saved query than it does to simply build the SQL string and execute from within code. I continue to hunt for coding bottlenecks and any other efficiency problems I can find. Has anyone else experienced this? Is there a "critical mass" for the object containers?

 

[Rich]

Have you compilled and saved all modules, compacted the DB etc.?

 

[Bodisathva]

Yes, that is part of the shutdown routine...code does a compile and save and the db is set to compact and repair on shutdown.

 

[WayneRyan]

Bod,

What are the stats?

I really don't think that Access is stumbling over finding 1 of your 410 queries.

How bad is bad?

Are you sure that it's not an indexing problem? If a "massive falloff in performance"
occurred within a week, it's probably not the number of objects that Access traverses.

What else has changed?

Wayne

 

[Bodisathva]

Access isn't stumbling over finding one specific query, but it is indeed stumbling over opening any one stored query. Nothing has changed structurally in the past several months. We have probably added 2 or 3 forms and 2K lines of code, though.

There are several queries that were being accessed through the Set qryDef=currentdb.querydefs() method, and each one, instead of being nearly transparent, now takes roughly 8-10 seconds to complete the assignment statement...the execution is still fine, it's the assignment that creates the problem. Removing the compiled query logic and replacing with a strSQL="SELECT..." and a DoCmd.RunSQL(strSQL), restores the expected performance time. (And before the code is challenged, I can make the assignment statement the only line in a module and still have the same problem )

I have a custom built poke method to work with DAO recordsets that also started to suffer performance problems. The problem was in the traversal of the fields() and tabledefs() collections in order to verify proper input, destination, selection criteria, and data types prior to attempting the poke. When I removed the references to the collection objects, the performance returned. The downside is that I can now only return error values based upon the Access error codes thrown and lost the ability to specifically check all parameters. Since the method is only made to be accessed in code, I suppose I can deal with it, but that doesn't mean I have to like it

I have one other programmer on the team and neither of us has seen anything like this...but we have confirmed it. For now, we're working around the issue but would appreciate anyone being able to point us to another solution.

 

[Bodisathva]

more stats

I created a module just to see how long it takes to access the objects in the database and count them all. The tables and queries, I can access through the *defs.count method, I am actually stepping through the allforms, allmacros, and allmodules collections.

Tables:     140 4062MS
Queries:    410 4063MS
Forms:      67  0MS
Macros:     5   0MS
Modules:    26  0MS
Processing Time: 8125MS

So it takes no time at all to step through the collections, but to access the containers it takes just a little over 4 seconds. Add to that, the fact that it only takes 1MS longer to count four times as much data leads me to believe that the problem is, as assumed, in the containers.