How do I make a DB faster?
March 26, 2010 1:00 PM   Subscribe

MS Access: How do I turn my database from a tortoise to a hare? (Or, if not a hare, a chipmunk?)

Here's what I've got:

MS Access DB split into front and back ends. Front sits on local machines (all XP), back sits on server (MS Server 2003). I have a consultant for the DB and a consultant for the network.

When there's one and only one user connected to the back end, the DB is lightning fast. UNBELIEVABLY fast. The minute that more than one user is connected, everything slows to a crawl. It's been this way for 2.5 years and it drives me nuts. The DB guy blames the network, the network guy blames the DB. Initially, I couldn't tell if it was the network or the DB. I'm now convinced it's the DB.

I've read past questions and googled.

It looks like Access falls to shit once it hits around 50 users. The most we ever have is 7. It doesn't get slower as more users come online: one user = fast, more than one = slow. It runs at the same speed for 7 users as it does for 2.

Is it possible to get the DB running faster or is this as good as it gets? If so, where/how do I start? Any suggestions welcome; I'm willing/wanting to learn so that I can work with my DB guy.
posted by Vavuzi to Computers & Internet (8 answers total) 1 user marked this as a favorite
Sounds like an issue with tables being locked. Not a DB guy, but definitely a network guy with a lot of experience supporting networking issues related to DBs.
posted by bfranklin at 1:04 PM on March 26, 2010

It certainly sounds like locking (whether it is at the OS file level or something internal to Access is another question). While Access isn't the greatest system in the world for this, it had multi-user capabilities designed into it so it shouldn't fall apart with just two users.

Now you are a bit unusual that you have a pair of databases that interact with each other. Perhaps your database guy can put a simple single file database out on your server for a few of your users to simultaneously use. If this works right, it might give you hints about where there problem is located.
posted by mmascolino at 1:15 PM on March 26, 2010

It's actually not unusual to have Access provide a front end for a SQL Server database. It keeps the database parts on a server that is optimized for that and the forms and what not in a relatively easy to program part.

There is simply no real reason to say the network is the bottleneck, especially if there is an extreme performance hit going from one user to two. I'd also consider that there's some transaction locking, but if you don't know what that is and your database guy is blaming the network, it may be best to look for a new database guy. This is pretty basic stuff.
posted by advicepig at 1:30 PM on March 26, 2010

It's also worth noting that Google tells you that Access is slow when you reach a certain number of users, and that's true if you are using Access as the database instead of the setup you have now, where SQL Server is the database and Access is the programming in front of it supplying the forms and maybe a little calculation here and there.

I'm no huge Microsoft fan, but your problem isn't Access or SQL Server, it's how your DB guy has everything written.
posted by advicepig at 1:33 PM on March 26, 2010

Nth to locking, though if it slows immediately it sounds as though there is a common table that is being called on user login in a poor way - maybe a dynamic recordset being opened immediately and not closed until logoff, or something similar.

It could also be a problem with bound forms - if the front end always opens on the same (bound) form, you could be causing contention from the first record.
posted by Pragmatica at 1:59 PM on March 26, 2010

It's the DB -- Access is terrible at managing network resources. Install something like DU Meter and watch network utilization while you use your application. Access sends tons of data back and forth over the network, as compared to a "real" database like SQL Server where you basically just send a SQL query over to the server and receive a dataset back.

In lieu of a complete rewrite, your best bet for improving performance drastically is to move the backend database to SQL Server, and link the SQL Server database to your Access frontend.
posted by reptile at 2:14 PM on March 26, 2010

One thing that the Jet engine powering Access has always had as a deficiency, that real RDBMS packages generally have as strong points, is a query optimizer. Access has a simple cost based query optimizer, but you can't specify how expensive queries will run, and it is totally dependent on "statistics" it collects during the last run of any given query. So, if you had a database concurrency problem (record locking, update/change/insert while query active, etc.) on a particular query run, or if it's just been awhile since you've run a particular query, added a new index, or updated an index, Access is pretty sure to use a non-optimal query access strategy. i.e. the worse a query ran last time, the even worse it will run next time. Heaven help you if your workload included any concurrent UPDATE, or INSERT statements.

Jet sucks donkey balls, badly. And it's now a deprecated MS technology.

Put a real SQL:2003 semi-compliant RDBMS, even a free one, like Postgres or DB2 on your server. Even MS SQL Server 2008 would be a better backend RDBMS, if you don't do a lot re-calc in your client Access front ends.

And sheesh, get a new DB guy.
posted by paulsc at 3:53 PM on March 26, 2010

Sweet! Thanks all. You've given me something to take to DB Guy. I'll post the results here once we figure out what the hell is going on.
posted by Vavuzi at 6:45 PM on March 26, 2010

« Older Cabins/cottages w/in 3-4 hr. drive of Detroit?   |   Where/when do I buy a car if I'm moving? Newer »
This thread is closed to new comments.