Slow connection from MS Access
-
We have recently had a 3rd party application migrate from an Access mdb database to an SQL Server 2005 db. No issues with this, and the application itself works fine.
After a bit of arm twisting, the vendor had given us ad-hoc, but read-only access to our data in the Access database, and have provided an equivalent read only login for the SQL Server DB. The queries and reports using this were built using Access, with local tables linked to the live DB tables. The intention with SQL server was simply to migrate the table links to the new database and everything should migrate with minimal pain.
After a few issues, I have had a play with a clean Access database, with a single table linked to a table within the SQL Server DB. The Access DB, and the user accessing this, are both on the same machine where the SQL Server DB server is running. While this works functionally, as soon as the table is opened, Access runs desperately slow. Simply opening the table (trivial - about 2000 records) takes about 10-15 seconds, and scrolling to the bottom of the table takes over 30. Even switching windows in Access, or accessing a menu, can take 10-15 seconds to get any response. While this is happening, the machine itself is idling - CPU is about 1% busy, and no sign of anything major being consumed (machine is running XP Pro, SP2).
Out of curiousity, I moved the Access DB over to another machine, and tried opening the same table (leaving the DB server as is). Performance in this situation is sparkling. Opening the table, or scrolling to the end of the table, is near enough to instantaneous. Unfortunately in our environment, we really need to be able to access the data from the DB server machine.
I haven't a clue where the problem originates, other than it is obviously some combination of SQL Server, Access and a local connection. Anyone have any ideas even where to start looking?
-
Do you have all the latest patches for XP, Access and mdac?
-
One thing to check is whether the Access database is an mdb or adp. The mdb file is a native Access file and does the processing through the Jet database engine. The adp file goes directly to the SQL database where the processing is done.
-
As you mention everything is running on the same machine, I would check the hardware specs on the machine. You mention it's a 'server', but that is a relatively loose term. The machine in question needs to have AT LEAST a gig of RAM. You are on one machine, so the harddrive is working overtime between SQL and then Access. When you link to another machine, each machine only has one task, so all resources are devoted to that one task, unlike your original situation.
Also verify that Access has been properly compiled. It doesn't sound like you have much code, but it's worth the effort. Over time, Access mdb files that are not compiled can also lead to unrecoverable corruptions of the mdb file.
-
Also, If you're using linked tables remember that the entire table is brought back to access before any join procedures can occur, so when you do it all on one machine, if that table is not already in memory, SQL server will need to read it all from disk etc... So when you break it out into 2 machines you don't have so many issues of resources being used by both access and SQL.
If you're not already i'd imagine that if you did the same thing, but were using pass through queries you'd see a sizable performance increase.
-Luke.
-
By chance, are you using both, Netware (IPX) and Windows (TCP/IP)?
-
I'm showing up late to the party - so most of the good suggestions are taken
. First and foremost - get the MDB off of the server, followed by the ADP idea (although I don't think they want you creating objects in their DB.)Even if you don't have an option to change to an ADP (which gets ugly since it will try to create objects, queries, etc.... in the very same DB you only have RO access to) - you can try using Pass-through queries in the MDB. These would be functionally the same as running ad-hoc SQL from Query analyzer, so the server would execute and pass the results back, which should at least help with things with a WHERE clause. The return from a pass-through is read-only (which would dovetail into what the vendor set up for you.
On the hardware side - assuming (like someone else already mentioned) that this is a "true server" (i.e. - there are multiple drives attached to multiple controllers), you might find that simply moving the Access MDB to another drive might help (some drive not getting hammered). If your MDF file and your MDB file are on the same disk/RAID disk set - each query causes the SQL table to be a. read off of the MDF file, and then copied back to the SAME disk drive into the MDB. This usually shows up with long periods of low CPU usage (where the disk interface is the bottleneck) - which you seemed to be reporting.
Finally - you may find that moving the MDB to another machine, and accessing it from the server over the network will be better. Of course - you might also hear about others on the network complaining of slowness accessing this server if you do that...
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply