At what stage should you upgrade from Access to SQL Server?? 1

[jgoodman00]

Our current datasource (Access 97) is approx 15MB in size. It has approx 15,000 records in it, & consists of approx 40 tables. However, it is required to be used by several users at once, with the datasource being stored on a PIII 600 & an UDMA 2 10GB hard disk. We have also tried hosting it on a PIII 800 with a 30GB UDMA 4 HDD. Our problem is fairly simple. A query run on the host machine will complete in approx 15secs, whereas a query run from another computer through the network takes at least 1 minute. Our network is a peer-to-peer network, through a 100Mb Switch.

The database is fully optimized to the best of my knowledge, & access can make no suggestions to improve performance.

So, does anyone have any suggestions on how we can improve performance? I was thinking that upsizing it to SQL Server would significantly improve query time because of the difference in the way data is queried (Server side processing or something?). Is this a reasonable assumption, or should we be looking elsewhere to improve performance???


James Goodman
j.goodman00@btinternet.com

 

[tlbroadbent]

Your Access database is not large by any standard. We have some access databases that are a few hundred MB is size with dozens of concurrent users. Performance is adequate. I say adequate because performance would likely improve if the data were moved to SQL Server.

One of the advantages of SQL Server over Access is server side processing. When a user opens an Access database on the desktop machine, the database engine (Jet) must read the database file from the network file server. When processing queries, Jet reads all data in the selected tables to determine which records to select. This requires the movement of a lot of data from network server to desktop machines. The process places a heavy burden on networks and client PC's as well as the file server that hosts the database file.

In SQL Server, the database engine runs on the server rather than the client or desktop machine. When a query is submitted, the database engine on the server selects records that are requested and returns only those records and columns necessary to satisfy the query. The network load is greatly reduced, as is the load on the client.

Another possibility you may want to evaluate is upgrading to Access 2000 and using MSDE for the data engine. MSDE is a free, stripped down version of SQL Server that you can use to build true client/server databases with Access 2000's new ADP project file type. Check the following links.

http://msdn.microsoft.com/library/en-us/dnvbpj99/html/rd1099.asp

http://www.microsoft.com/technet/prodtechnol/office/evaluate/featfunc/dataops.asp

http://msdn.microsoft.com/library/en-us/dnovba99/html/07movaccess.asp

http://msdn.microsoft.com/library/en-us/dnoxpta/html/odc_modmsde.asp

If you are using the access database as a data source for web pages, VB programs, etc. rather than the using Access for programming and data storage you can ignore what follows.

Some ideas for improving Access performance.

Split the Access database if you haven't already done so. Access performs better if the program code (forms, queries, reports, macros and modules for you application) are in a "front-end" MDB (or MDE) and the data is in a "back-end" MDB containing only tables.

Convert the Front-End database to an MDE. The .mde file is a copy of an .mdb file, with all plain text representations of code stripped out. It is designed to be distributed to users who have no need to modify their solutions. It is smaller than the .mdb, because the source code has been removed. As a side-benefit, users cannot view or tamper with a developer's code modules.

You could even load the "front-end" on individual PC's to improve performance more.
Terry Broadbent

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[jgoodman00]

We have already split the database into a front & back-end. We have also got a local copy of the front-end on each computer, which is why I think the database is about as performance tuned as readily possible.

We have tried the MDE approach as well, but this also does not significantly improve performance.....


D'Oh! James Goodman
j.goodman00@btinternet.com