Access fronted and MySQL or MS SQL Speed

[mase2hot]

Hi,

we need to connect 4 offices to the same database. We have gone for a hosted dedicated server costing £300 a month. The server is quick and we get download speeds of around 800KB/s which is better than other hosts that we were getting 180KB/s.

Thr problem is out database takes 14 seconds to open a customer record an improvement over 30 seconds with other hosts. The speed is constant at 14 seconds everytime we open a record. But we need to be it under 10 seconds really, as when the database was on the local lan it took around 6 seconds to open a record.

The size of the database is 303MB 98% of this is a table which contains all postcodes for the UK.

How can we speed this up? Currently all queires are on the front end, would this slow it down? We have also tried it with Microsoft SQL Server and get around the same times. Would it be better to have some type of replication having a server at each site? All offices need to have live data so that errors do not occur.

Problem I can see with this is that the other sites just have BT standard business broadband so get around 7MB/s download and 400KB/s upload so I don't think this would be suitable as their broadband is also used for normal web day to day browsing as well.


Thanks

 

[Guus2005]

The postcode database is almost 300MB? Perhaps you can strip it and use only those fields you really use: PC, Streetname, HousenumberRange, City.
Put indexes on the fields you search.
The postcode database is quite stationary. It is changed once every month for updates?
You can give each office a local database containing the postcode only. Access, MySql or MS SQL. The remainder of the database is about 6MB. This is a very small database. Noneed to use MySQL or MS SQL for this purpose.

Try to work stateless. It prevents the database from unnecessary locking.
Don't use bound forms!

Share and Enjoy!

 

[Banana]

Guus has given you some advices and if the postcodes does not change frequently, I also have to wonder why you feel you need a centralized backend. Maybe it's needed for the last 2% of your database so all offices can see each other edits. If that is the case, I'd take Guus' advice to just keep a local copy of postcodes and leave the last 2% on a backend.

I don't really agree that bound forms shouldn't be used in that context - I think it can be used to good effect as long one follow good practices.

Typically, I try to do the following:

1) Move all lookup tables to local front-ends. No reason why you should be filling the network with queries to pull down data that doesn't change. It's easy enough to write the code to update the lookup tables once per while.

2) Bind forms to queries with a good restriction. This depends more on business process but few possible examples would be a) no more than 90 days older, b) hard limit of 100 rows, c) active items only.... you get the idea. Offer a separate button to search the archive.

3) Give a search form... essentially asking the user what they want to see first before pulling anything down.

4) Be sure to choose the right recordset/query for the job. For example, if you have a form that's mainly used as an index form for fast browsing and is non-editable, use a snapshot. Snapshot and Dynaset (or, Static vs. Keyset for ADO) has their sets of benefits and tradeoffs. Use accordingly.

5) Make sure your Access queries are something that can be sent back to the server. Do not force Jet to pull the whole table to perform local evaluation. One easy way to do that is to put down an Access function in the WHERE clause. Use SQL functions only. Example:

WHERE IsNull(a_Col) = True;

will be much slower than this:

WHERE a_Col IS NULL;

There's more but that's the gist. For details and even more links to other good articles... Beginner's Guide to ODBC.


HTH.

 

[mase2hot]

Thanks for the replys. The reason for the current database being so small is that it only contains around 3 test clients. We need to add 300 current clients along with around 100 extra a week.

This is the reason why we have gone for a mysql/mssql backend.

 

[dfenton]

Why are you expecting it to be fast?

 

[boblarson]

I think you had better read this about using Access over a WAN.

You will not have speeds near what you have on a LAN. For one, you are talking a normal LAN you probably have 100Mbps. So, when you are over a WAN you are likely going to have data speeds at much less than that. Heck, I pay extra just to get a 25Mbps connection at home and that is way faster than here at work.

 

[HiTechCoach]

You might find this discussion helpful HERE

 

[mase2hot]

We get 800KB/s download speeds from the new server and to open a record its only a few hundred KB max. So yes I do expect it to be fast but obviosuly our gigabit internal network will always be quicker.

I tested from my laptop at home and opens a record between 6-8 seconds which would be fine. I took my laptop to work and it opened in around 10 seconds, (with no-one else in the office). My work pc takes 14 seconds to open a record. My home broadband is Virgin 10MB/s Whiile works is BT (copper wire rubbish) 6.5MB/s.

Now the only difference from my work PC to my laptop is that my laptop has office 2007 where my pc has office 2003. Is that the reason? Is there a massive difference in access for performance in 2007? Spec wise my PC is a quad core 2.8ghz where my laptop is a Dual core 2Ghz so my PC is easily better than my laptop.....

Thanks

 

[mase2hot]

Installed office 2007 on my work PC and this made no difference. All I can put it down to now is my version of windows. PC is XP Pro and laptop is Windows 7.......

 

[Banana]

1) I do not think changing version of Office or even OS is going to make any difference. As has been pointed out, it's inherent limitation of a WAN connectivity.

2) I wonder if you may have missed that 800 KB/s actually means 800 kilobit per second... So a record only few hundred kilobytes ... let's say 300 kilobytes would translate into 2400 kilobits would require 3 seconds to download, assuming perfect conditions (which is impossible. More practical estimate would be half that, actually)

3) I have to say that a record that's few hundred KB is awfully big. Are you storing images or something that?

4) What your form is actually doing? What is the query you're running when you open a record?