Slow append query

[Nauticalgent]

Greetings Access Commandos,

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!

[PhilOfWalton]

Don't know about the speed problem, but I think you have a progress meter.
See

https://bytes.com/topic/access/insights/970224-better-progress-bar

I use it in countless situations, but a very similar one to yours is downloading Web Pages of shares, extracting the prices, and inserting into a table.

If you need any help with that aspect of the problem, let me know

Phil

[Nauticalgent]

Thanks Phil and you are right, I do have your meter and it’s a fine piece of work. However, unless I am mistaken (quite possible), it can only update once a process, such as a query, has completed.

I am looking for something that assures the user that something is going on while that God awful slow qurry is running. I don’t think it’s possible for Access/VBA to do two things at once (ASSynchonous) but I do not mind being wrong...

[PhilOfWalton]

Absolutely wrong. This is a dynamic process as records / emails / web pages / anything where the total number to be processed is known and a counter can be incremented as each item is processed.

Initially you can program it to show the total number of EMails to be processed.

As each email is read, the progress meter will move along, changing colour as required, the number of records read and if you want, the header or sender of each email.
I would add that in the published version, the detail (header or sender of each email) is not available. On the version I use, that information shows up.

This is what it looks like after reading 33 of the 41 Web Pages


If you are still interested, I will update the zip file.

Phil

[twinnyfo]

Hey Phil,

Will your meter progress while the DB is delaying during the long append query? I know Nauticalgent mentioned that was also part of the delay.

[PhilOfWalton]

It updates as as record are processed.

I should have pointed out that it does not work with an Append Query, but does update on an "Add New, Update" type loop in VBA

Phil

[Nauticalgent]

I have come to that conclusion. I have considered changing over to a loop paradigm, for reasons other than this, but I am still on the fence.

Reading records and manipulating the information to populate other tables gets tricky when you’re dealing with a dynamic object such as an Outlook Public Folder. I can’t test it now but I am wondering if using a select query as my RS and looping through it instead would be my solution...at worst I could then use a progress meter to keep everyone entertained while the grass grows...

[PhilOfWalton]

That is exactly the method I use. Much easier to monitor where the delay is taking place.

I will update the progress meter to the latest version, and post that on Bytes over the weekend.

Phil

[zmbd]

It sucks that VBA doesn't seem to have an asynchronous call.

I've ran into this issue with Outlook/Access pulling from our corporate servers. The main bottleneck was the bandwidth between our local shadowing of the corporate server and the main server. This lag increased exponentially once we started rolling into Window7(64bit)-Enterprise while there were still older OS clients on the network. Things improved greatly once we finally updated the exchange server and all of the clients had moved to the newer OS; however, it's still monstrously slow!

Keep in mind... the outlook public folder has several dozen, or more users depending our your company size, accessing that data all at the same time and there's a lot of handshaking going on behind the scenes to allow all of these people to access that data.

Hopefully, the upgrades to your network will improve things.

You might consider one of my workarounds which was to open a form in dialog+modal with a message indicating that windows may show Access to be non-responsive while updating the records and that it may take up to 10 minutes - this forced the user to either ctrl-tab to the button or left-click to close the form before the query started. When I did this it eliminated the ctrl+alt+del / task-manager calls to end Access - your mileage may vary :)

[Nauticalgent]

Thanks for the feedback zmbd and your experience sounds very much like my own. I cross posted this in another forum this morning and some of their more prolific members are chiming in with some good advice, which I just may try.

At any rate, once Phil supplieshis latest and greatest, I will implement it in just for GP. But, by chance I am able to crack the latency nut, I will definitely post that solution here.

Really appreciate the feedback from all who have posted.

John

[PhilOfWalton]

Hi John

Your wish is my command.

See FormSimple. Probably you will need to set the Smoothness back to 200.

Phil

[Nauticalgent]

Thanks Phill, I don’t care what the others say about you, you’re aces with me!

[PhilOfWalton]

Whatever they say, it's malicious lies.

Phil

[Nauticalgent]

...uttered from the lips of craven curs!

[Rabbit]

@zmbd, actually, there are asynchronous queries.

Option Explicit
Private WithEvents AsyncConnection As ADODB.Connection

Private Sub Command1_Click()
    Const ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Data\Access\database1.mdb;Persist Security Info=False"
    
    Set AsyncConnection = New ADODB.Connection
    
    AsyncConnection.ConnectionString = ConnectionString
    AsyncConnection.Open
    
    Const sql As String = "SELECT * FROM table1"
    
    Call AsyncConnection.Execute(sql, , CommandTypeEnum.adCmdText Or ExecuteOptionEnum.adAsyncExecute)
End Sub

Private Sub AsyncConnection_ExecuteComplete(ByVal RecordsAffected As Long, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pCommand As ADODB.Command, ByVal pRecordset As ADODB.Recordset, ByVal pConnection As ADODB.Connection)
    If (adStatus = adStatusOK) Then
        Me.List2.RowSource = pRecordset.Source
    End If
    
    If (pConnection.State = ObjectStateEnum.adStateOpen) Then
        pConnection.Close
    End If
End Sub