Ace.12.0 is very slow to kill me

Question

1.00/5 (1 vote)

See more:

I am working with openrowset using Microsoft.Oledb.Jet.4.0. It works fine on 32 bit systems. However I had updated system to 64 bit version of SQL Server 2014 cluster server with Windows Server 2012 R2.

Since there are no 64 bit version found for Jet.4.0 I have installed Ace.12.0 64 bit version. After that I found query execution time slow down dramatically. My DELL server configuration is 16 core processor with 128 GB of RAM 2 nodes. 9 TB usable storage with SAN switch.

When queries are executing I found 93-97% usage of RAM but processor usage is 2-3%.

Another thing is that I have updated my Access.mdb to Access.accdb. It takes more time to execute queries on Access.accdb than Access.mdb.

For same data containing Access.mdb and Access.accdb very 5 seconds execution time at least.I have to execute queries on 100 tables for each branch wherever I have 3000 branches. It means if each table takes 5 seconds more I have to waste 100*3000*5=25000 minutes. The same difference found between Ace 12.0 and Jet.4.0. It is killing me.

Please help me anybody.

What I have tried:

Another thing is that I have updated my Access.mdb to Access.accdb. It takes more time to execute queries on Access.accdb than Access.mdb.

Posted 4-Jun-16 20:56pm

Member 12360367

Updated 4-Jun-16 21:56pm

Richard MacCutchan

v2

Add a Solution

Comments

Richard MacCutchan 5-Jun-16 3:23am

Switch to SQL, Access was never designed for large scale databases.

Dave Kreskowiak 5-Jun-16 10:06am

You have this massive machine and you're still using a kiddie database engine? The problem isn't ACE or JET, it's that fact that you're using Access for a such a large database. Migrate that thing to SQL Server NOW. You're not going to fix this and still use Access. You outgrew using Access a long time ago.

Member 12360367 5-Jun-16 15:17pm

My main database is in SQL Server. Which is more than 2TB volume. I have to consolidate data comes from remote areas to this SQL server database. For this reason I use Access.mdb as a data container which contains maximum 500mb of data. And getting stack while consolidating.

Now what should I do?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2016-06-04T21:56:00

If you have an SQL Server cluster set up, then it is very odd to want to use Access directly for anything, let alone for a large scale DB. Access is basically a single user, single access, small system database - and it isn't very good at all when you try to expand beyond that.
If you have SQL Server then change your code and use SQL directly - drop the Access stuff completely and migrate the DB. It's a lot simpler and almost certainly faster, since if nothing else the DB file and the software processing it are on the same PC - the server. When you use Access, the DB engine runs on your PC even if the file is on your server - so the file detail has to be transfered over your network each time it's used. At the moment, your server with it's 16 cores and 128 GB of ram is being used as a glorified file store... :laugh: