Access Project/SQL Express runs slow

Question

Access Project/SQL Express runs slow

klymster

Say Hey Kid

Points: 704
More actions
November 26, 2008 at 2:00 pm

#77141

I recently migrated a large Access database to SQL Server Express. The Access application included menus, forms, and associated visual basic code. I convereted it to an Access project file linked to SQL Server Express. I painstakingly converted the vba code from DAO to ADO so it would work with SQL Server. Now that the conversion is complete the application runs considerably slower than Access. I've timed it and it is much slower when looping through recordsets and adding new rows. This is dissappointing to say the least and embarassing as I told my client that converting to SQL Server would improve performance. Has anyone else noticed this? Is there anything I can do to improve performance so it as least runs as fast as it used to in Access? I'm worried that it will be even slower when connecting to the database over a network.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

The biggest gains you get out of converting would be to move your queries to executing from the server, and not from the local Access instance. If you simply shipped the data to SQL Server, and are now just linking to the raw tables, then your queries are STILL executing locally in Access. Except now, they have to download the data from the server (all rows from all tables involved in a query) and then parse them locally.

Depending on works better - look at implementing views on the server (with built-in, static criteria filtering your data), or pass-through queries (to dynamically ask the server to run a query for you), This way, when a query would only qualify 10 rows out of 1 Million, the server finds those 10 rows for you, and pass just those back (instead of passing the entire 1 Million rows back to you if you are still using traditional Access queries).

Also - looping through rows one at a time in ADO code (in any language for that matter) is going to be slow. Look at doing something set-based instead (will be MUCH faster).

----------------------------------------------------------------------------------
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?

klymster Say Hey Kid Points: 704 More actions · Answer 2

Hi Matt,

Thanks for the tips. Here is how I set it up: I used the upsizing wizard to transfer my tables and queries to SQL Server Express. So my former Access queries are now Views in SQL Server. I can see them through my Access project file, but I'm sure they are on the server.

I inherited the visual basic code which I updated to use ADO for connecting to SQL Server Express. There are many short embedded SQL select statements in the vba that return values to recordsets. Is it worth my time converting these to stored procedures? I know that it is a good practice but the sql is so short and basic I can't see that making a big difference in terms of speed.

I'm suspicious of a recordset I have that grows into > 1,000,000 rows during processing. I'm getting a 'out of memory' errors after several hours of processing. Not sure if this happening on the server or in Access. In the code, this large recordset gets passed ByRef to some functions. Is it possible this is eating up the memory?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

Start with the basics: make sure to spend some time in SQL Express, getting some good indexing going on the tables, so as to "help" whatever views and/or queries you use a lot. Be sure to take a pek (after you've used the app for a while in SQL Server at some of the system views which tell you what indexes might be needed. I wouldn't necessarily implement each and every single one of them (it tends to go a little overboard with putting a lot of indexes), but still - it usually has some decent advice on some of the bigger indexes to pick. (look at the metrics on how many times something gets used and what the saving are - should give you some ideas what might help you most.

Otherwise - I would be very leery of any object remaining in Access with > 1M rows in it. That is definitively hogging up your memory. I never was a big fan of persistent objects like that, but that downright scares me. Not knowing your code - i would just say - don't let it get that big before disposing of it.

----------------------------------------------------------------------------------
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?

klymster Say Hey Kid Points: 704 More actions · Answer 4

> Otherwise - I would be very leery of any object remaining in Access with > 1M rows in it. That is definitively hogging up > your memory. I never was a big fan of persistent objects like that, but that downright scares me. Not knowing your

> code - i would just say - don't let it get that big before disposing of it.

Thanks! Your comment got me to look closely at this large recordset. It turns out that I don't need to open it and carry it in memory because I'm only adding new records. I changed rs.AddNew to a direct insert statement. This took care of the memory leak issue.

The application still runs slower in SQL Server than it did in Access. I've done some searching and it looks like I have to live with a reduction in speed when switching from DAO to ADO:

http://p2p.wrox.com/topic.asp?TOPIC_ID=13005

Thanks for pointing me in the right direction.