Solved

Query data from SQL Server or Access Database

Posted on 2013-01-03
4
558 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 85

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 85
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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…

617 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