How to speed up "Retrieval" time?

[Meltdown]

Hi, I have a form which shows summary information about orders placed. There are a number of drop-down combos at the top of the form which allows user to filter the data. Beside each record in the summary is a "Details" button which allows the user to open a form and view the full info about the order.

The problem is Access seems very slow in bringing up the "Details" record. On my local machine it takes about 3 seconds, worse still, on the customers’ network it takes about 5 seconds. Is there any way to speed up the retrieval time?

It’s an Access 2000 database

Heres the code behind the click event for the Details button:

Private Sub cmdOpenDetails_Click()
On Error GoTo Err_cmdOpenDetails_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmOrderDetails"

stLinkCriteria = "[tblOrders.OrderNo]=" & Me![OrderNo]
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormEdit
Forms("frmOrderDetails").AllowAdditions = False

Exit_cmdOpenDetails_Click:
Exit Sub

Err_cmdOpenDetails_Click:
MsgBox Err.Description
Resume Exit_cmdOpenDetails_Click

End Sub

 

[Meltdown]

pono1, thanks for the ideas, I'm going of to give some of them a try. I've just had a terrible shock, the main order form on the switchboard is taking 35 seconds to open!. The database is normalised, split, and secured, is this normal??

Anybody else experience this problem?

Thanks for any help

 

[andrewf10]

I can relate to that alright!

Just make sure you tell your users to be patient - mine were clicking here, there and everywhere to speed it up but this was making it even slower. Some even used CTRL-ALT-Delete to cancel and try again - a no-no in any language.

On the positive side, I've also seen databases in work (not mine) take around 90 seconds to come back with forms so things could be worse.

 

[Meltdown]

It seems to have slowed down after I split and secured the database - is this normal?. There's no way the customer will accept a 35 second delay every time they put an order in. Fifty orders a morning X 35 seconds = nearly half an hour twiddling thumbs - help!

 

[The_Doc_Man]

Good news and bad news

The good news is that security probably didn't cause you a problem. The bad news is that Access does this to networks. Here is the straight skivvy:

You can do one of several things with Access (for the purposes of this discussion). You can
(1) let folks use the shared copy of Access with the entire app on a shared disk, or
(2) you can let folks use a local copy of Access on their local disk, pointing to a shared database file on a shared disk for all really important (or ALL) data, or
(3) you can use Access as a front-end to a program like SQL Server or ORACLE or one of the others that support this sort of thing, or
(4) you can let folks use local copies of Access on their local disk and try to reconcile all changes they make locally to your critical tables, performed on a regular basis.

Of these, method 1 is terribly slow because queries are executed on the local machine despite the fact that the data is elsewhere. You literally pull entire tables across the network this way. On the other hand, it is cheap and relatively quick to develop.

Method 2 is like a band-aid because you can at least put invariant stuff locally and not have to download it every time. Then you run into the problem of telling users when to pull down another copy of the front-end portion. But if your lookup tables and a few other things are local to the user's machine, you can get back a small portion of the speed you lose across your network. The cost between methods 1 and 2 is about the same because in either case you need an Access license for each potential client.

Method 4 gives you the best possible performance but terrible overhead. It is a lot of serious work to do reconciliation among multiple independent databases, and usually turns into a lot more trouble than some folks think it is worth. Money-wise, it is about the same cost for licenses as choices 1 & 2.

Method 3 is the only one that performs decently and yet cuts back on the work you have to do to keep things consistent across all users of you application. If the queries are done at the host and only the result set is returned over the network, you can get the best of both worlds. But here, the bugaboo is the cost of the software for your host system and possible software license fees for your client systems.

Hope this helps to put things in perspective for you.

 

[Rob.Mills]

I was having similar problems on my apps. The network was terribly congested and some forms were taking over 2 minutes to load. And in some cases things were so bad that the user couldn't even type in data after it opened.

I accomplished The Doc Man's 4th suggestion by replicating the backend. It took a little time to get everything straight but now that I have it works fantastic. Everything opens fast and network traffic has been improved because of it.

 

[Meltdown]

The_Doc_Man, thanks for your input on the subject, you've certainly given me some food-for-thought. Option 3 and 4 are out as this application is practically finished. This is a contract I'm working on so I won't be around to do maintenance or modification. Sure hope I get paid ;-)

I can't believe I havn't seen more posts on this subject as its so serious

Thanks again to everyone for the helpful replies

 

[mark curtis]

Meltdown,

Does the contract state the expected response times for loading a form? or are you offering any SLA with the app?

If not then there is scope to increase your earnings by offering to investigate the performance problems and offer resolutions?

 

[Meltdown]

Tim, thanks for the link, interesting read.

Mark, no the contract never mentioned form opening times because I'm new to this and wasn't even aware of the issue until a few days a go. I probably will be able to get some sort of a maintenance contract.

On a postive note, I've just discovered that turning off the "Name AutoCorrect" features in both the front end and the back end has given a HUGE increase in performance on my home PC, the main order form is now opening in 2 seconds(down from 12). Can anybody else verify this for me by testing on your own DB's?

Fingers crossed that it gives the same result on the customers network.

Thanks again everyone