Append query = watching grass grow (1 Viewer)

Greetings Access Commandos,

*** I posted this question in another forum (bytes.com), becuase it was there I found a possible solution. Reposting here for different perspectives although I am certain I am SOL.

The link:
https://bytes.com/topic/access/answers/971000-slow-append-query
***

While I was looking for a solution to my problem, I came across this thread:
https://bytes.com/topic/access/insights/907658-progress-indicator-access

And although I think giving your clients something to look at while the Access Gnomes are making sausage is ALWAYS a good idea, I still want to fix why my query, which used to run instantaneous (relatively anyway), is now like watching grass grow.

The setup is fairly straight forward but in the interest of full disclosure, I will provide some details.
We recently “upgraded” from Win7/Office 2010 to Win10/Office 2013 - more on that later.

My BE is on SQL server 2008 (soon to be 2014), however the two tables in question are not. And finally, I have been told that we will be upgrading our exchange server soon too - that bit may be the key.

The two tables in question: one is a linked Outlook Public Folder which holds our message traffic. The other is a local table which is almost a mirror image of the Public Folder. At the start of the process, I have an Append query that inserts the records from the Public Folder into the local table, once that is done, the sub steps through each record and parses the text into other tables and so on and so forth.

On any given session there may be about 50 records or so and from start to finish, it would take about 1 minute tops to complete.

That was then. Shortly, but not immediately, after the “upgrade” the process can now take up to 5 minutes and the only feedback the users get is “Access is Not Responding”... The users, convinced that something is wrong, would start pushing keys, clicking mouse buttons and last, but not least, the almighty CTL-ALT-DEL combo and then I would get to clean up the splendid mess that was made. Not really a bad thing, it did prompt me to put in safeguards against this but I am still at wits end.

I broke the Module into sections and was able to narrow down the bottle-neck which was the append query. Even when I get under the hood and try to open the linked table, it can take up to a full minute for the screen to render and records to show, and we are talking less than a 100 records - crazy.

Running the query within Access is even slower so I KNOW where the problem is. What I do not know is why there is latency and subsequently, what to do about it.

The local table does have a PK which I added in an attempt to speed things up. Point of clarification, i should say temp table becuase I purge it before I run the append query with no results. I have compacted and repaired the FE and even did a decompile in vain attempts to fix this issue.

If this is simply an issue with Win10 and our antiquated but soon to be upgraded exchange server, than I can wait it out and see what happens...but if there is something I am overlooking and have not thought of yet, I am ALL ears(eyes)...

Still going to implement the progress screen, simply of cool point value alone!

Have you tried to relink the table?
Or only for testing, import it?
And still only for testing, create a new database and do the link.

Good morning JHB,

JHB said:

Have you tried to relink the table?

Click to expand...

As a matter of fact, because there may be one of two Public Folders, I delete the link and based on the user’s selection, relink just before the Append Query. I have wondered if that may have been the key, but leaving it linked did not solve the latency.

JHB said:

Or only for testing, import it?
And still only for testing, create a new database and do the link.

Click to expand...

Hadn’t thought of that...but to quote a movie from my childhood: “Hell, I’d piss on a spark plug if I thought it would do any good!”

Unfortunately, I can’t test this until Monday so hopefully the suspense doesn’t get to any of us. Me, I’m used to watching grass grow so this should be easy...

I always use a progress bar for cases like this where a sequence of steps are used, some fairly slow.

If you are filtering records to be appended, have you indexed the fields used in the where clause?
Have you tried removing the filter and preventing duplicates using a unique field?
Or have you considered deleting existing records then appending all records as that can be quicker? That method of course risks data loss if something goes wrong before the append.
Or use a make table query instead. Faster than append and the database bloating effect is, surprisingly, about the same in each case

Otherwise, I think we would need to see the query SQL and possibly some mocked up data in order to suggest specific ways of speeding up your append query.

BTW the standard progress bar method can't be used where you just have one slow step to perform. In that case, use a progress bar which runs using a timer. I posted an example of that last year but can't find the link at the moment.
EDIT: Found it ... https://www.access-programmers.co.uk/forums/showpost.php?p=1537640&postcount=13

Or there is a very old thread by Chris O called 'indivisible process animation with an interesting twist https://www.access-programmers.co.uk/forums/showthread.php?t=206049&highlight=Lipstick
Worth looking at for the example database name alone - 'lipstickonapig.mdb'

Ciao Col,

If you are filtering records to be appended, have you indexed the fields used in the where clause?
Have you tried removing the filter and preventing duplicates using a unique field?

Click to expand...

Being that the table is linked AND a OL folder, I don't think I can build indexes, but I will sure look into that as well (see my spark plug quote for reference). Also no filtering is done before the Append, the linked folder is actually a, for lack of a better word, temp folder where all new messages get copied to. The master folder holds about 10k (and growing) records and I knew better to use that one. For that reason, they are deleted from the temp folder after they are appended using the timestamp OL assigns to all traffic.

Or use a make table query instead. Faster than append and the database bloating effect is, surprisingly, about the same in each case

Click to expand...

You may be on to something there, and the spark plug rule applies to this one as well, but so does the whole Monday thing. The application resides on a classified machine so I cant bring work home with me - wife would not allow it anyway. I am already getting disapproving looks from her now even though she has been fed and the dogs tended to.

The progress bar on a timer thing is intriguing, but will it fire while a query is running? I am under the impression that once Access has committed to a query, its all stop until it is done - even with the .execute method / DoEvents / DBEngine.Idle dbRefreshCache options used.

For the record, I ALWAYS use the .execute method with DBEngine.Idle dbRefreshCache.

Finally, good info on the bloat issue. My FE hovers about 12mb which shrinks down to about 9mb after a C&R which I do once a month. On the last working day of the year, I also do a decompile just to keep things tidy. Overkill?

One last question: Will England beat Sweden??

NauticalGent said:

...I am under the impression that once Access has committed to a query, its all stop until it is done - even with the .execute method / DoEvents / DBEngine.Idle dbRefreshCache options used.
...

Click to expand...

So far I remember I have had queries executed from code, where the code proceed to next code lines before the query had finish which cause me to use DoEvents, (to get the query finish first).

Regarding the wife, I think you have been too soft in the beginning and now you sit with the "hot potato"! I couldn't resist it!

JHB said:

So far I remember I have had queries executed from code, where the code proceed to next code lines before the query had finish which cause me to use DoEvents, (to get the query finish first).

Click to expand...

I have had similar issues, especially with action queries, there is even a thread I started way back when...l

(https://access-programmers.co.uk/forums/showthread.php?t=289980)

...which Doc introduced me to the DBEngine.Idle dbRefreshCache thingy. Not really sure when or why you would use it over DoEvents but it DID solve my problem.

While I was trouble shooting this issue, I used Col's balloon tip module to alert me as to when each step was done. For the record, there are three: The delete and re-link of the OL folder (less than 1 second); the Append query (1 to 5 minutes) and the the processing of the appended records (about 3 seconds, tops). It is the delay, even though I am using the .execute method, which prompts me to believe that Access is holding everything up until the query has completed.

Yes I also use DoEvents between each step for exactly that reason
In fact I build this into the UpdateProgressBar procedure to save repeating code

The progress bar on a timer thing is intriguing, but will it fire while a query is running?

Click to expand...

I wasn't suggesting it in your case as you have multiple steps
Good point though - probably not unless you can force it to deal with each record individually as in a recordset loop ... but that would slow everything down anyway defeating the point.

ChrisO's method does solve that ... but it may be overkill ... hence the 'lipstick on a pig'

DBEngine.Idle dbRefreshCache

Click to expand...

I've never used that method - what are its main benefits?

I am already getting disapproving looks from her now even though she has been fed and the dogs tended to.

Click to expand...

I know what you mean and just got exactly the same look whilst answering this on my tablet. I tell her spending time on forums is like her doing cryptic crosswords ..but I still get 'the look'

Compact/decompile etc? I do so far more often on some databases but then I have one FE of around 140MB with several BE files including one SQL BE of around 1.6GB

One last question: Will England beat Sweden??

Click to expand...

Ah ... you've got me there. I suspect the nation will be doing a collective decompile after another penalty shoot out

Logging off now

Regarding the wife, I think you have been too soft in the beginning and now you sit with the "hot potato"! I couldn't resist it!

Click to expand...

We have a saying in the US, real popular with the younger generation: "Start out like you can hold out"

Maybe they're not as dumb as I thought...

Or not! Looks like the handled Sweden quite handily...congrats and good luck in the next round. Croatia looked pretty good against Russia but I think you guys are up to the task...