Solved

Query data from SQL Server or Access Database

Posted on 2013-01-03
4
533 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

770 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