Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 577
  • Last Modified:

Query data from SQL Server or Access Database

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
mcs26
Asked:
mcs26
  • 2
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
mcs26Author Commented:
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
 
als315Commented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now