Go Back   Access World Forums > Apps and Windows > Hardware Questions and Answers

 
Reply
 
Thread Tools Rating: Thread Rating: 4 votes, 5.00 average. Display Modes
Old 11-15-2010, 05:21 AM   #1
phinix
Newly Registered User
 
Join Date: Jun 2010
Posts: 122
Thanks: 9
Thanked 0 Times in 0 Posts
phinix is on a distinguished road
Best HDD/SSD for busy Access database?

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?

phinix is offline   Reply With Quote
Old 11-15-2010, 07:17 AM   #2
namliam
The Mailman - AWF VIP
 
Join Date: Aug 2003
Location: Amsterdam/The Netherlands
Posts: 11,415
Thanks: 0
Thanked 805 Times in 790 Posts
namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light
Re: Best HDD/SSD for busy Access database?

maybe a mysql or some bigger database engine? Sounds to me like if your waiting ages to get results.... your due for an upgrade...
__________________
ruoY yppah namliaM si syawla yppah ot pleh
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.



To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Remember to use the scales (Bottom left) or the thumb (bottom right)

Always looking for new challenges (Amsterdam Area)
namliam is offline   Reply With Quote
Old 11-15-2010, 07:45 AM   #3
phinix
Newly Registered User
 
Join Date: Jun 2010
Posts: 122
Thanks: 9
Thanked 0 Times in 0 Posts
phinix is on a distinguished road
Re: Best HDD/SSD for busy Access database?

Quote:
Originally Posted by namliam View Post
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?

phinix is offline   Reply With Quote
Old 11-15-2010, 08:04 AM   #4
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,841 Times in 1,587 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: Best HDD/SSD for busy Access database?

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.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 11-15-2010, 09:15 AM   #5
namliam
The Mailman - AWF VIP
 
Join Date: Aug 2003
Location: Amsterdam/The Netherlands
Posts: 11,415
Thanks: 0
Thanked 805 Times in 790 Posts
namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light
Re: Best HDD/SSD for busy Access database?

I would expect to get a considerable performance increase using SQL Server over an Access database in these quantity of data
__________________
ruoY yppah namliaM si syawla yppah ot pleh
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.



To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Remember to use the scales (Bottom left) or the thumb (bottom right)

Always looking for new challenges (Amsterdam Area)
namliam is offline   Reply With Quote
Old 11-15-2010, 09:24 AM   #6
phinix
Newly Registered User
 
Join Date: Jun 2010
Posts: 122
Thanks: 9
Thanked 0 Times in 0 Posts
phinix is on a distinguished road
Re: Best HDD/SSD for busy Access database?

Quote:
Originally Posted by namliam View Post
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?
phinix is offline   Reply With Quote
Old 11-15-2010, 09:29 AM   #7
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,841 Times in 1,587 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: Best HDD/SSD for busy Access database?

Whatever you can do with SQL Server it is almost guaranteed to do it faster than in Access.

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 11-15-2010, 09:57 AM   #8
phinix
Newly Registered User
 
Join Date: Jun 2010
Posts: 122
Thanks: 9
Thanked 0 Times in 0 Posts
phinix is on a distinguished road
Re: Best HDD/SSD for busy Access database?

Quote:
Originally Posted by boblarson View Post
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...
phinix is offline   Reply With Quote
Old 11-16-2010, 02:08 AM   #9
namliam
The Mailman - AWF VIP
 
Join Date: Aug 2003
Location: Amsterdam/The Netherlands
Posts: 11,415
Thanks: 0
Thanked 805 Times in 790 Posts
namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light
Re: Best HDD/SSD for busy Access database?

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.
__________________
ruoY yppah namliaM si syawla yppah ot pleh
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.



To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Remember to use the scales (Bottom left) or the thumb (bottom right)

Always looking for new challenges (Amsterdam Area)
namliam is offline   Reply With Quote
Old 11-16-2010, 03:07 AM   #10
phinix
Newly Registered User
 
Join Date: Jun 2010
Posts: 122
Thanks: 9
Thanked 0 Times in 0 Posts
phinix is on a distinguished road
Re: Best HDD/SSD for busy Access database?

Quote:
Originally Posted by namliam View Post
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?
phinix is offline   Reply With Quote
Old 11-16-2010, 07:00 AM   #11
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,841 Times in 1,587 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: Best HDD/SSD for busy Access database?

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.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 11-16-2010, 07:06 AM   #12
phinix
Newly Registered User
 
Join Date: Jun 2010
Posts: 122
Thanks: 9
Thanked 0 Times in 0 Posts
phinix is on a distinguished road
Re: Best HDD/SSD for busy Access database?

Quote:
Originally Posted by boblarson View Post
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...
phinix is offline   Reply With Quote
Old 11-16-2010, 08:06 AM   #13
namliam
The Mailman - AWF VIP
 
Join Date: Aug 2003
Location: Amsterdam/The Netherlands
Posts: 11,415
Thanks: 0
Thanked 805 Times in 790 Posts
namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light
Re: Best HDD/SSD for busy Access database?

Why can you not use the server? It is there to be used... ???
__________________
ruoY yppah namliaM si syawla yppah ot pleh
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.



To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Remember to use the scales (Bottom left) or the thumb (bottom right)

Always looking for new challenges (Amsterdam Area)
namliam is offline   Reply With Quote
Old 11-17-2010, 12:40 AM   #14
phinix
Newly Registered User
 
Join Date: Jun 2010
Posts: 122
Thanks: 9
Thanked 0 Times in 0 Posts
phinix is on a distinguished road
Re: Best HDD/SSD for busy Access database?

Quote:
Originally Posted by namliam View Post
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 by phinix; 11-17-2010 at 01:03 AM.
phinix is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Beginner's Guide to ODBC Banana Access FAQs 1 06-18-2009 08:10 PM
Simulating Access Database as .Exe NigelShaw Theory and practice of database design 9 04-30-2009 01:11 AM
Access database response times Aenathras Theory and practice of database design 6 04-18-2008 02:10 AM
Mouse Scroll Xenix Forms 2 10-08-2006 08:12 PM




All times are GMT -8. The time now is 01:46 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World