New Computer - Optimized for MS Access

[dwhitman]

I'll be buying a new computer soon, and (as I do every time) I'm agonizing over how to allocate my budget among various options. Over the years I've attempted to optimize my hardware choices based on whatever activity seems to be dominating my computer use at the time. So I've had a gaming system with a bleeding edge video card, a system set up for some simulation work where the extra money went to a fast CPU, etc.

These days, the thing my current system seems to be taxed with most is handling a fairly big stand-alone Access database (about 600,000 voter records; I do targeted phone and mailing lists for one of my county's political parties).

Of course, many queries I do can be optimized by judicious indexing, but really, I lot of my queries are one-off impromptu affairs where I'm not going to spend time tweaking them, I'm just going to brute force my way to the answer for the committee person that asked for a weird list.

So, what kind of a system should I put together to make Access shine? Faster hard disk? RAID? Faster CPU clock speed? More CPU cores? More memory? What will give me the most bang for the buck?

 

[KenHigg]

RAM. Get all the RAM you can afford.

 

[rustyCrVx]

I can't say for sure so don't hold me entirely to my word but I would guess that a fast hdd would be the main component to speed things up with a decent cpu and memory which is getting pretty cheap with all the development in future cpu's and the already available ddr3 ram. I always build my PCs with parts from Newegg.com, their customer reviews have never failed me and I have no complaints with their customer service.

 

[Minkey]

RAM. Get all the RAM you can afford.

Up to 4Gigs for 32bit O/S's of course

There no real answer to this question as the main factor is budget, any system can be balanced towards a particular task. For most applications and games a fast processor and RAM is important but of course you don't need an uber GPU for a 2D app machine for example.

I'd say the most common hardware for all tasks are RAM, CPU and motherboard all matched up.

My machine is excellent at all common tasks (gaming, 2D apps, video editing, multitasking) but if it was only running intensive 2D apps I would have upped the motherboard, RAM and CPU (not necessarily in amount but probably in speed) and dropped the GPU within my budget constraints. - spent £1200 on it.

Our new video edit machine at work is optimised for video editing and is specifically designed for 2D speed I could have based it on my machine with a few changes and it would be fine but I spent £5000 (because I can, hey it's not my money ) and is simply awesome at it's task - a 1 hour recode takes 4 mins, mine would probably take about 20 mins.

EDIT: Forgot to say because I had a big budget I was able to spec different types of RAM - fully buffered and HD - SAS (15,000 rpm) instead of IDE or SATA which significantly improved performance over the same size of 'normal' RAM. You need to bare that in mind.

So what you got to spend ?

 

[rustyCrVx]

Actually you can really only fully utilize 2 gigs of ram on most 32bit systems. On a 32bit Vista you can only make use of 3.12 gigs even if you physically have more. If you want to make full use of any more you'll have to have a 64bit system, and I think that's what you meant Minkey. In my opinion, getting more is not the way to go either. If you have a motherboard that can support faster ram it'd be best to go with a decent amount of the fastest your mobo will support than get more of poor quality. Quality > Quantity

Edit: And yes, knowing how much you have to spend would help for making suggestions.

 

[dkinley]

I'd say the most common hardware for all tasks are RAM, CPU and motherboard all matched up.

Just wanted to make this one stand out to point out BACKPLANE!

You could have the fastest CPU, RAM, etc, it won't matter if the motherboard backplane is the bottleneck. Imagine owning the fastest car in the world but having to drive it on a one-lane road with a buncha grand-mas (no offense Grams!) ... so ensure the backplane bandwidth is capable of the hardware devices you will be plugging into the motherboard.

Last note, although mostly for gaming is the video card. I don't know why, but most people skimp in this area but it is also a big source of bottle-
necking. I'd rather plunk down most of my cashola on the motherboard and video card because I know I can upgrade the rest later without an issue or too significant of a cost.


-dK

 

[rustyCrVx]

Just wanted to make this one stand out to point out BACKPLANE!

Why in the world would he need a backplane? And why should he bother with a video card unless he was somehow going to make some sort of use of a gpu with Access? A cheap one nowadays will be more than enough if he's going to be focused on Access and he can always just upgrade it if he needs it just like every other part... you made it sound like once it's there it's permanent unlike the rest.

 

[Brianwarnock]

Actually you can really only fully utilize 2 gigs of ram on most 32bit systems. .

What does this actuall mean, i.e. most 32bit systems.

Are saying that if I upgraded my old XP home Edition I should not go past 2 gigs as any more wont be used? I used Crucial's scan and they seem to thing 3 gigs would aid performance, is it a scam to sell more memory?

Brian

 

[dkinley]

A cheap one nowadays will be more than enough if he's going to be focused on Access ...

True. I was emphasizing a point about the backplane speed being consistent with the hardware devices not 'outrunning' what the motherboard bandwidth could handle. Especially because it is a database and there will probably be many fetches from the hard-drive to the ram (unless your incorporating some sort of super-fetch).

My last was just a note exemplified by the gamer bit which the user is already aware of to cast more light on the backplane to ensure the user was spending money wisely. Just an addendum to what others had posted.

For my own take on it, I've used systems that were dusty in a corner and upgraded the hardware just enough to put XP and Office '03 on it. If it worked on it, I knew it would be faster anywhere else it went in production.

-dK

 

[rustyCrVx]

Sounds like you'd be fine with the 3 gigs. The 2 gig limit is pretty old and I'm pretty sure that's been taken care of but I know for sure that no 32bit will make use of a full 4 gigs, it will cap at 3.12gb. I've even had some friends say they had performance drops when they tried using 4 gb in their 32bit XP systems. I'd do some research on your hardware and os to be absolutely sure, but I don't think a company like Crucial would scam anybody like that and they would/should be aware of the issue, it's nothing new.

 

[Brianwarnock]

Yeah I think Crucial made some comment about 3.12 so sounds like you are on the same wavelength.
Thanks

Brian

 

[dkinley]

It depends on what you mean by x-bit system. The buss or the cpu?

For instance, in a 32-bit system (referencing CPU registers) there are only a finite number of addresses that can be used .... 2^32 = 4 GB. The only way I know that this can be exceeded by the use of virtual memory or a masking technique.

-dK

EDIT: The only reason for the initial question is because I've seen some market systems as 64-bit meaning the buss size and throwing a 32-bit CPU in the box.

 

[rustyCrVx]

EDIT: The only reason for the initial question is because I've seen some market systems as 64-bit meaning the buss size and throwing a 32-bit CPU in the box.

Damn that's pretty lame. You had the right idea with the cpu. And like you said 2^32 = 4gigs or so but not to confuse anybody else, close to a gig of that is not accessible to the user but used by other system devices which is why even when you have 4 gigs in there that show up on the BIOS, you'll only find that 3.12 in Windows.

 

[dkinley]

... close to a gig of that is not accessible to the user but used by other system devices which is why even when you have 4 gigs in there that show up on the BIOS, you'll only find that 3.12 in Windows.

Very good point. I never realized it was that much until you just pointed it out.

-dK

 

[rustyCrVx]

Yeah it really is quite a bit but with a 64bit cpu and os that can make use of it there's a ridiculous limit of billions of gigs or something.

This seems to have gone a bit off topic lol.

 

[Minkey]

OK I'm not going to quote everyone that would take to long but hopefully these comments will address most comments:

A 32 bit O/S will support up to 4Gig of RAM (address space) by it's very nature but the reason you see a max of 3Gig (in the O/S) is because 1Gig is used by devices as a physical address layer the rest will be available as raw memory. There are ways of enabling more than 4Gig of RAM in 32 bit O/S's.

The backplane reference is (I believe in this context) has been used as a bit of an old school term - a motherboard is technically a backplane.

You can do numerous tweaks to a system to get the max performance out of it, changing virtual memory settings for example to utilise the RAM you actually have.

Just because an O/S can't see all your RAM doesn't mean it's not part of the performance of the system and this leads to..... installing more RAM slows the overall performance, don't assume more is better it depends on how your system is configured (not just O/S buit also the BIOS) and the type of RAM you have OK this is a bit nerdy but I'm not surprised most people sic Crucial say only 3Gig is needed on a 32 bit O/S because that's the most most people ever see and crucially (no pun intended) need.

Oh and a 64 bit O/S can theoretically address 16EB of RAM (that's 17.2 billion Gig )

And of course back on topic - show us the money, how much cash do you have

 

[rustyCrVx]

You just said everything we just went over... did I miss something?

 

[dwhitman]

Ha! Lots of good discussion here, thanks.

I think dkinley brings up a really important point, that a system needs to be balanced - doesn't do any good to have a really fast cpu if the motherboard can't keep up. No good having a really fast drive if a task is compute-bound on the CPU.

I guess what I'm looking for here is a better feel for what constrains Access performance. I just don't know enough about the internals of Access - are indices keep in memory, or do you need to do disk access to use them? If I'm doing queries with string criteria, is Access spending lots of time in the CPU doing string compares, or more time loading the strings from disk?