SSD for Quickbooks Company File

[onthedownlow]

Hello all. Got a question that I couldn't answer myself after reading all the SSD reviews. I have a small business and run Quickbooks Premier 2007. The company file, which resides on a network computer continues to grow larger with every new input so over time it takes longer and longer to access it. For those of you who are familiar with QB, we don't want to start a new company file because we want all customer history to be on hand. So, the purpose of our query is to see if we can speed things up by using an SSD drive solely for the company file, or if we would be better served on a vraptor (or even a std 7200rpm drive for that matter). The company file is currently 139mb.
Now, we're not sure if the entire company file is rewritten when changes are made (i.e. new invoice created, deposits made), or just a portion of it, so I can't determine which benchmark test is more relevant to our situation. Also, we have 3 users constantly accessing and adding data to the company file all day. I'd say on any given day we could save new data to the file 30 to 50 times (affecting useable life of ssd?).

Money is not really an issue if the performance warrants it, but the performance gain needs to be significant for the money.

Thanks to all who take the time!

 

[dfedders]

Is this file being accessed from other computers over a network? If so, I would think that the network would also be a bottleneck. Some files do not work well over a network, and I believe that quickbooks is one, and another would be a Microsoft Access file. Test copying the file to a computer and running quickbooks directly from there before purchasing an SSD that may not help.

I've also seen people use a new file for every fiscal year. That would help as well, and you could always open previous years files to go back if you needed to. (Really how often do you need to go back and look at stuf from 2 years ago?)

 

[COPOHawk]

How many users do you have simultaneously?

Is the "server" an actual server or just a shared workstation?

 

[SolMiester]

Holy large QB files batman?...I would backup the file to say 2 yrs data then shrink it down...You could always open the the backup if you need to go back further than 2 yrs.....you could have both file open at the same time if you like!

 

[onthedownlow]

Wow, great, fast responses. Thank you! I'll try to answer all the posts at once.

The file is on a shared workstation (my computer), so I work with the file directly and my employees (2 to 3), access the file from other computers over a network. All computers are wired and all have gigabit nics. Router is gigabit too and I've run tests on each computer to verify the network performance.

I haven't actually clocked the response times over the network vs. at host (I'll do that tomorrow); however, performance is a bit better on the host, but it still sucks.

The problem with creating a new company file isn't so much the 2 years ago part, it's the days or months before the new company file was started. Most customers call and say "repeat the last order"; we also have many customers on Net terms so unpaid invoices would be on the old file... etc. If I could have a rolling 2 year file for example that would be great. I'm sure I need to look at doing something with the company file itself as well, but even if I optimize the company file I still want to optimize the hardware side.

I probably need to ask Intuit what chunk/size of the company file is rewritten when changes are made (i.e. new invoice created, deposits made). I think it's just a portion, otherwise it would likely take a hell of a lot longer to save invoices, etc. (like minutes vs. seconds). Under that assumption, the repetetive file savings are likely quite small, but we have say 50 of them per day.

Question 1) In our case, what is the best hard drive option? Would a SSD prove significantly faster than say a vraptor or even a std. 7200rpm drive? Possibly faster seek/read times, but slower writes?

Question 2) If an SSD would prove significantly faster, do I need to be concerned about the useful life of the drive with our usage or would it be enough to last say 5 years.

Thanks a bunch guys!

 

[punjabiplaya]

are you running qb in multi-user mode?

 

[RebateMonger]

If your local desktop is just as slow as the remote computers, then it's not the network.

What is slow about it? Opening up the company's database? Adding a new record or making a change? Creating a report? Everything?

What kind of disk performance do you have on your workstation? Is your Quickbooks database on the same disk(s) as your Windows system? Any disk-related errors in the System Event Logs?

When you perform a "slow" operation on your workstation, what do your CPU utilization, memory utilization, and %disk time and Current Disk Queue Length look like? Windows has a million performance monitors to show stuff like this and it will usually point to where the bottlenecks are.

How about at the remote workstations?

 

[LokutusofBorg]

Yeah, I'd suggest running Performance Monitor. Look at the counters for Physical Disk, and find stuff like sec/Read and sec/Write, etc. If you're seeing anything close to 50ms wait times on I/O operations, then you have a disk bottleneck. If you have a disk bottleneck, an SSD would likely help things out quite a bit. But you also need to consider how old your system is, how old your existing hard drive is, etc. Any of the newest generation 7200RPM SATA drives are actually quite fast.

 

[mcveigh]

I'd say put it on a small server or even a NAS storage box. remove you and your machine from the equation. I think that would be better than getting an SSD on your machine, in terms of performance increase.

and yes clean up that QB file!!!!!!!!!!!!!!!!!!
your accountant should be able to help you.

 

[RebateMonger]

Database programs (like Quickbooks) are disk-intensive by definition, writing either to transaction logs or to the main database itself. Assuming the workstation where the database is stored is reasonably "up-to-date" in terms of CPU and memory, and that there's nothing "wrong" with the workstation, you'll likely find that it spends a lot of time doing disk access. It's highly likely that a dedicated, faster disk will help.

 

[COPOHawk]

I have a few customers with similar situations...and large QB company files.

1. Cust #1 hosts the company files from a shared folder on the SBS 2003 server; file server (no email configured) and runs a RAID 1 with 7200 RPM drives. No problems.

2. Cust #2 hosts the company files from a shared folder on a minimally used workstation with a 7200 RPM drive..no problems because the workstation is usually unused.

The disk usage is the key. If there is no other option to store the company files on a shared folder on a seperate computer; push for the SSD. Every environment is different though.

HTH.

 

[Emulex]

if this is a sql server based product - more ram - schedule optimize indexes/statistics/rebuilds

if it is not - get something that is

sql server 2005 express out of the box probably is not anywhere near optimized but you should at least give it 1.7gb of ram for the database process alone

 

[onthedownlow]

All good feedback. Thanks guys. I think I have enough info to get going on it. I'll post again if I hit a snag, but I think I'll be able to get it from here. Thanks again!

 

[Denithor]

Something to try before spending money - download the trial version of Superspeed's RamDisk software (http://www.superspeed.com/desktop/ramdisk.php) and set up a 500MB ramdisk. Back up your QB file and then move the save file onto the ramdisk. Point QB to open the file from the ramdisk.

That should give you some serious improvements in speed. If it works you can buy the software or look around for a freebie ramdisk program (there are several out there).

 

[VirtualLarry]

Something to try before spending money - download the trial version of Superspeed's RamDisk software (http://www.superspeed.com/desktop/ramdisk.php) and set up a 500MB ramdisk. Back up your QB file and then move the save file onto the ramdisk. Point QB to open the file from the ramdisk.

That should give you some serious improvements in speed. If it works you can buy the software or look around for a freebie ramdisk program (there are several out there).

Yes, but what if the power goes out.

 

[DominionSeraph]

Something to try before spending money - download the trial version of Superspeed's RamDisk software (http://www.superspeed.com/desktop/ramdisk.php) and set up a 500MB ramdisk.

This is a good test of whether disk I/O is the bottleneck. Set up a ramdrive, and if performance is still unsatisfactory, it ain't a problem a SSD or velociraptor can fix.

If it is disk performance, I'd try running the company file from its own partition before shelling out cash on an upgrade, since fragmentation can be a killer.

 

[Denithor]

Yes, but what if the power goes out.

This is a corporate computer - I would hope it's on a solid battery backup system.

 

[RebateMonger]

This is a corporate computer - I would hope it's on a solid battery backup system.

Optimist? I don't see a lot of UPSes on small business computers. Maybe the boss has one.

Even then, nobody tests them, a lot have old batteries, and they do NOT behave nicely when the batteries get old. In my experience, a UPS with old batteries is worse than no UPS at all. Don't ask me to explain it, but I've seen "UPS-protected" computers go dead during a quick power glitch while neighboring "unprotected" PCs stayed running.

 

[Nothinman]

This is a corporate computer - I would hope it's on a solid battery backup system.

He also said that it's on his personal workstation, most places that I've seen don't spend money on UPSes for workstations.

 

[Voo]

He also said that it's on his personal workstation, most places that I've seen don't spend money on UPSes for workstations.

Well I'm rather sure that a UPS is cheaper than a Intel SSD But you're right that you have to maintain it, though that shouldn't be such a problem and if you can get the whole database into the RAM it's faster and cheaper than any SSD could ever be.

 

[robertk2012]

Use the ramdisk as a test if you want. It isnt a long term solution though.

 

[Voo]

Ouch - Voo

My apologies. I thought I was creating a new post, quoting you, but accidentally edited your post, instead. (I have that option because of my Moderator duties). I'M SORRY!!!! I don't have any way to get your original post back. Please accept my apology.

RebateMonger - AnandTech Moderator


[q]But other than that we're talking about a <200mb file, that fits easily into RAM and as we all know RAM is way faster than even the best SLC drive.. and way cheaper.[/q]

 

[Free4ever]

This thread is a little bit old, but I wanted to say my 2 cents. I'm the "computer guy" at our small business and I take care of all tech stuff related to Quickbooks Enterprise (we just upgraded to version 2010). If you think you've seen a big Quickbook file, just imagine a file that is 2.6GB IN SIZE! Yes, I'm not making this up, our file really is that big. I've seen it grow over the last 7 years, but its not surprising considering we have over 20,000 customers and over 100,000 transactions and a minimum of 100 new transactions every day. The business owner wants to retain all the customer history, so making a new file is not an option.

So how do I manager adequate performance from a 2.6GB file with about 20 concurrent users? I convinced my boss that the only way was to have a monster database server. And so every two years I've built a rig using the best available parts. The current server has two quad-core Nehalem Xeons, 16 gigs of ram and of course two Intel X25-E SSDs in RAID 1. The biggest jump in performance came when I upgraded from 4 Raptors in RAID 5, to the Intel SSDs when they were first released (at $750 a pop). Performance is blazing fast even with 20 users, saving, opening invoices, etc. The server IS overkill, but it is vital to the business. I was so impressed with the Intel SSDs, that I bought another pair when the price fell to $350, and built a backup server with similar specs, in case the primary database server failed.

I've tried to get the company to switch from Quickbooks, but the damn thing is so fricking easy to use, that I don't get any support from my boss. Every time I've tried to demo something different, its always the same thing, its never as intuitive and user friendly as Quickbooks. I've searched high and low for anything that matches Quickbooks in ease of use, but I'm pretty much stumped. If anyone has made a successful jump from Quickbooks to another app, I'd love to hear it.

 

[RebateMonger]

This thread is a little bit old, but I wanted to say my 2 cents.

Thanks for the information. There's VERY little information on SSD use with Quickbooks. I found zilch on Ituit's Support site.

Yeah, your server is probably a bit of overkill. But, hey, if it works and avoids user delays, it's worth it in time and frustration saved.

 

[Free4ever]

Quickbook's forums are very little help when it comes to using cutting edge hardware. Even with the full support plan from Quickbooks, I've had no help from tech support in optimizing performance. So I've pretty much relied upon my own experience and knowledge, because I suspect that there are very companies out there using Quickbooks as we do.

Using the superb Resource Monitor app in server 2008 R2, I've found that the bottleneck for the Quickbooks database file has always been the storage disk. The CPU barely breaks 7% and RAM 10% at peak use. When it comes to traditional hard drives, the biggest limitation in database performance has been the I/Os per second. This is where the SSDs really shine. I would have to build a very big SAN of traditional hard drives to match just two SSDs in a raid.

Quickbooks likes to do a lot of small reads and writes, so SSDs are well suited to the task. I haven't heard of anyone else using SSDs for Quickbooks, but we've had them for about a year now, and they've worked flawlessly. I was hesitant to switch over, until I read some reviews. Also Intel's reputation for releasing quality hardware was a big plus.