Best HDD/SSD for busy Access database? (1 Viewer)

phinix

Registered User.
Local time
Today, 00:01
Joined
Jun 17, 2010
Messages
130
I have a question to you guys: what drive would be the best choice for database?
I work with Access databases every day, building, merging, copying data between big files, millions records, etc. Some queries take ages to get results, so I thought I could use some SSD drives to make it work faster. But which one?
Thing is I do not work on one database, I work on 5-10 2GB files with millions of records, run hundreds of queries every day. That is why I think RAID won't be good, cause drive would have to clean itself when its idle (TRIM).

So maybe Velociraptors in RAID0? Or any other HHD in RAID0? Hybrids?
 

namliam

The Mailman - AWF VIP
Local time
Today, 01:01
Joined
Aug 11, 2003
Messages
11,696
maybe a mysql or some bigger database engine? Sounds to me like if your waiting ages to get results.... your due for an upgrade...
 

phinix

Registered User.
Local time
Today, 00:01
Joined
Jun 17, 2010
Messages
130
maybe a mysql or some bigger database engine? Sounds to me like if your waiting ages to get results.... your due for an upgrade...

Well, yes, I know what you mean.

See, we have SQL server I could use, but I'm not sure if that would help.

What I do is: I get 2GB database every week, 2M records. I work on this by importing to Access. Then I compare to other Access files, update records, append tables, etc. I run lots of queries, that take 2,3,10 minutes to get results, and I do that many times.

My question is - how an I make this run faster?
get multi HDDs in RAID? Buy SSDs? Or always add those new incoming databases to SQL server and work from there?
 

boblarson

Smeghead
Local time
Yesterday, 17:01
Joined
Jan 12, 2001
Messages
32,059
Well, for one -

Access has a 2 Gb limit (including system tables).

So, you're taxing it right there. It has no space to maneuver.

So, by going to SQL Server or SQL Server Express, you are going to have resources that Access just can't give you.

As for the hard drives - forget about it. Those aren't your problem. If you want faster processing load up on RAM (up to 3.5 Gb if you are on a 32 bit system and way more if you are on a 64 bit system). RAM can speed up things because fewer writes to the hard drive for caching data while working with it will make it work much faster than trying to find a faster hard drive.

I don't think you'd notice much difference in the database processing between an SSHD and a 7,200 rpm standard hard drive.
 

namliam

The Mailman - AWF VIP
Local time
Today, 01:01
Joined
Aug 11, 2003
Messages
11,696
I would expect to get a considerable performance increase using SQL Server over an Access database in these quantity of data
 

phinix

Registered User.
Local time
Today, 00:01
Joined
Jun 17, 2010
Messages
130
I would expect to get a considerable performance increase using SQL Server over an Access database in these quantity of data

OK. I can try that...

So you guy suggesting to get SQL server, import tables and run queires from the server? Or for example link those table from server to access and run queries from those linked tables?
Would it be better to work on server directly or from linked tables, on pc in the network?
 

boblarson

Smeghead
Local time
Yesterday, 17:01
Joined
Jan 12, 2001
Messages
32,059
Whatever you can do with SQL Server it is almost guaranteed to do it faster than in Access.
 

phinix

Registered User.
Local time
Today, 00:01
Joined
Jun 17, 2010
Messages
130
Whatever you can do with SQL Server it is almost guaranteed to do it faster than in Access.


What if I build pc with HDDs in RAID to make it faster and install SQl server on it and work on this system - would it make even faster?
I just want to optimise my work in some not expensive way...
I could buy some SSDs (MLC), but these have low write cycles so I could kill them after few months.. I guess...
 

namliam

The Mailman - AWF VIP
Local time
Today, 01:01
Joined
Aug 11, 2003
Messages
11,696
The power of SQL server is hiding in the fact it is a SERVER, anything you do to your desktop... it is never going to be a multi cpu, multi gigabyte, multy threading server.
 

phinix

Registered User.
Local time
Today, 00:01
Joined
Jun 17, 2010
Messages
130
The power of SQL server is hiding in the fact it is a SERVER, anything you do to your desktop... it is never going to be a multi cpu, multi gigabyte, multy threading server.

Yes, I know, but I can't use my SERVER server, I could install SQL server on my pc, which I will build in near future.

So, I understand, unless I use fully blown multi-cpu server with loads of ram, I won't get it run faster on regular pc? Even if I get 4 core, 12GB ram, super fast drives in RAID...etc?
 

boblarson

Smeghead
Local time
Yesterday, 17:01
Joined
Jan 12, 2001
Messages
32,059
RAID isn't going to make anything faster. If anything it will make it SLOWER. RAID is a backup solution which writes either partitions (strips of data) to each drive or duplicates the data on each drive. It won't make things faster it only serves to ensure you don't lose data if one drive crashes.
 

phinix

Registered User.
Local time
Today, 00:01
Joined
Jun 17, 2010
Messages
130
RAID isn't going to make anything faster. If anything it will make it SLOWER. RAID is a backup solution which writes either partitions (strips of data) to each drive or duplicates the data on each drive. It won't make things faster it only serves to ensure you don't lose data if one drive crashes.

Not exactly - I meant RAID0. For SSD it almost doubles the performance. For HDD it takes more drives to get same performance.
I noticed my SSD runs some queries on 1.3million records database about 2-3 times faster than regular hdd - that is why I wanted to know if you guys tried something like that or similar...
 

namliam

The Mailman - AWF VIP
Local time
Today, 01:01
Joined
Aug 11, 2003
Messages
11,696
Why can you not use the server? It is there to be used... ???
 

phinix

Registered User.
Local time
Today, 00:01
Joined
Jun 17, 2010
Messages
130
Why can you not use the server? It is there to be used... ???

Someone installed SQL with Outlook on domain server and basically using SQL will slow down everything else, so I rather not to do that. I'm not in charge of server, so that is why I wanted to build separated fast c (not dedicated server) to use it.

I have tested one of my databases at home on my pc: i5, 4GB RAM, SSD Vertex 2E. Result was incredible: one of the queries I ran took 70 seconds to get results. I will test it at work today to see how long does it take on my pc I have now - Core2Duo 3GHz, 4GB RAM, regular hdd... I actually tried this query yesterday and had to stop it before got results cause it was taking too long time (more than 5 minuts!).. but I will keep it running today to measure it... will get abck with result...

EDIT: now I'm just worried about wearout on SSD.
How can I measure how many MBs I write during the day? is it possible to catch?
 
Last edited:

Users who are viewing this thread

Top Bottom