Click here to Skip to main content
15,562,223 members
Please Sign up or sign in to vote.
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
Updated 4-Jun-16 22:56pm
v2
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

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:
 
Share this answer
 
Comments
Member 12360367 5-Jun-16 15:16pm    
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?

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900