Solved

Query data from SQL Server or Access Database

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

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

829 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