Solved

Query data from SQL Server or Access Database

Posted on 2013-01-03
4
528 Views
Last Modified: 2013-01-03
Hi,

I have a C# program that queries data from an access database. The database in question contains about 30 linked tables. Each table is stored in its own database as the tables all contain currency price data for each minute going back to January 2000 which is just under a 1GB. So I have linked all the databases into one 'main' database.

My question is about how best to query data from such a large database. I have the option of an Access database or SQL Server 2012 Standard edition. I thought it would obviously be SQL Server. However after reading on the internet apprently that would not be the case if networks are not involved. The program runs on my computer and will only be used by me. Below is one of the quotes I read. Can someone please tell me what is the best option for me and whether I should be using Access or SQL if speed is the main issue?

When no network and no server and single user then Access (JET) is often significantly faster than sql server. SQL server forces you to do everything though a huge extra layer that involves transactions and a socket based (network) connection. Jet just literally scraps data right off the disk with no huge framework between that reading of data from the disk. SQL server or Access (JET) does not change the speed of file reading from the disk drive. So, JET has significant less overhead then sql server and my testing shows JET is often 50% faster when no network is involved

Thanks,

M
0
Comment
Question by:mcs26
  • 2
4 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 38739612
Jet can definitely be faster on a single use, local setup, but if you're going to be working with large datasets (1 gb is a fair amount of data) then you may notice better performance with SQL Server. It's impossible to say which would be faster, and the only way to determine this would be to do some testing.

If you think your dataset will grow larger, note that Access has a 2 gb limit, and that (generally speaking) as the dataset gets bigger performance will decline.
0
 

Author Comment

by:mcs26
ID: 38739623
Hi LSMConsulting,

Thanks for the reply. My dataset will grow larger, although it would be sometime before it reached 2gb. Crudely calculating its approx 1GB and thats about 12 years of data so would expect it to be another 10 years or so. However I may add extra columns etc so that could change.

I'll test it later today. I was just surprised to hear that Jet would have better performance than SQL Server. Is there a limit to the amount of data where you think this would be the case? I.e anything under 1GB Jet would be better?

Lastly with my Access database have linked tables would this affect the performance in a noticable way?

Thanks again,
M
0
 
LVL 39

Expert Comment

by:als315
ID: 38739629
If you will have indexed tables on SQL and stored procedure will be used, it should be even faster then query many Access dbs
0
 
LVL 84
ID: 38739671
Lastly with my Access database have linked tables would this affect the performance in a noticable way?
Not if they're local tables (i.e. on the same machine as the frontend).

As als315 indicates, if you can use stored procedures on the SQL Server, often you'll find much better performance.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now