NauticalGent
Ignore List Poster Boy
- Local time
- Yesterday, 21:03
- Joined
- Apr 27, 2015
- Messages
- 6,527
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!
*** 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!