Supporting Clients with Large Files on Local Area Networks

This document covers the tips, tricks, and best practices for increasing QuickBooks performance, especially in multi-user environments, and for increasing file stability when hosting QuickBooks on local area networks.

This manual is copyright protected and is the sole property of Woodard Consulting Group, Inc. Woodard Consulting licensed CoralTree Inc., developers of Qbox, to distribute this material to accountants and bookkeepers for use as a resource in the support of clients who use QuickBooks software. Any unauthorized distribution of this content is strictly prohibited.

Increasing QuickBooks Performance for Large Data Files and/or in Multi-User Environments

As your data file grows larger, you will find that many reports and processes in QuickBooks begin to run slower. This performance degradation is especially noticeable when more than 3 people are using the data file simultaneously. You can do some or all of the following to make QuickBooks operate more efficiently:

• Set QuickBooks So the Home Page Does Not Show When You Open the File. To change the default setting for each user, you must log in to QuickBooks using each user’s name and password, access the User Desktop View preferences, and uncheck the box that reads, “Show Home page when opening a company file.” Running the Home page uses system resources each and every time a user opens and closes either the program or each time a user switches from one data file to another. The more access the user has to financial information, the more resources QuickBooks uses to display the Home Page.
• Set User Preferences to Clear All Open Windows Each Time You Exit QuickBooks. To change the default setting for each user, you must log in to QuickBooks using each user’s name and password, access the User Desktop View Preferences, and select the radio button that reads, “Don’t save the desktop.” If you save the desktop, QuickBooks will reopen every window you used in your previous session, including reports that require a tremendous amount of QuickBooks resources to generate.
• Turn off Reminders. Unless it is essential for you to view reminders each time you open QuickBooks, edit Reminders User Preferences if necessary to uncheck the box that reads, “Show Reminders list when opening a Company file.” See the first bullet above about the Home Page. The issue is the same. By default, this box is unchecked.
• Set Reporting Preferences So You See the Modify Report Window before QuickBooks Displays the Report. Edit the Reports & Graphs User Preferences to check the box that reads, “Prompt me to modify report options before opening a report.” With this box checked you will be able to narrow the date range for a report and to set other filters to limit the amount of data QuickBooks searches when you create the report. You will you get the specific report information you need faster because you don’t have to wait for QuickBooks to run the report once, edit the settings, and then run the report again. You will also free up system-wide resources that other users on the network can use. This suggestion is most effective for data files that are 100MB or larger, though you will still see some benefit for smaller files.
• Set QuickBooks Preferences So Reports Do Not Auto Refresh. Edit Reports & Graphs User Preferences to select the radio button that reads, “Don’t refresh” reports or “Prompt me to refresh” reports. If you have numerous reports open and the user setting is to refresh the report automatically, QuickBooks will use system resources to recalculate each report you have open on your screen if you simply click the report. Even with this Reports & Graphs setting, you should close all reports you no longer need to view. See the bullet below for more information.
• As You Use QuickBooks, Close Windows You No Longer Need. When using QuickBooks, it is easy to open window after window as you move through the program and forget to close the windows behind you. Each open window uses system resources. Ideally, you should have no more than three open windows at a time. Certain tasks may require more than three, but most do not. To help facilitate this “window rationing” you should set your desktop preferences to show “multiple windows.” Also, you can quickly close all open windows by selecting the Window drop-down menu and then selecting Close All. It is best to select this option when you complete a task in QuickBooks (e.g., payroll processing, bank reconciliation, paying Bills, etc.) You can show all of the open windows by selecting the Window drop-down menu and then selecting Cascade. You can then close any windows you don’t need.
• While Working in QuickBooks, Turn Off Any Programs That You Do Not Need. QuickBooks is a powerful program that uses tremendous system resources on your workstation. Though running unnecessary programs won’t affect other users on the network (unless the data file is stored on your computer), it will slow the performance of your workstation.
• In Windows, Turn off As Many Systems Processes as You Can. System processes run under the surface, so to speak. They are not software applications (like Word, Excel, or QuickBooks), but you can unknowingly add these processes to your computer when you install applications. To view a list of active processes, open the Windows Task Manager and click the Processes tab. You may or may not be able to identify the process by its name. If you have no open applications and your computer is running more than 40-60 processes, consult your IT professional about turning off as many processes as possible.
• Don’t be Cheap with Hardware. For the best performance, each computer on your network should not only meet the QuickBooks minimum hardware requirements but should also exceed the minimum hardware requirements. It is best to purchase at or near the top of the market when purchasing new workstations and then to replace or upgrade workstations every 3 years.
• In a Multi-User Environment, Use A Dedicated Computer to Host the QuickBooks Data File. This dedicated computer should be running Windows server operating system software. This operating system will allow other computers to connect to the workstation and access the QuickBooks file. If instead, you host the QuickBooks data file on a QuickBooks user’s workstation computer, you are using that computer’s resources to both run QuickBooks (and all other applications) as well as manage the flow of data to and from the QuickBooks data file, reducing that workstation’s performance including access to the QuickBooks file.
• Consider Using Terminal Services. If your data file is over 100MB and you use QuickBooks Pro or Premier, or if your file is over 500MB and you use QuickBooks Enterprise Solutions, you may experience a significant increase in QuickBooks performance if each user on the network accesses QuickBooks via Remote Desktop Server (formerly known as Terminal Services.) This increase in performance is especially noticeable if 3 or more users are logged into QuickBooks simultaneously. In short, Remote Desktop Server allows each workstation to share the server’s desktop – much like Desktop Sharing only much faster. When you use Remote Desktop Server, you still need a QuickBooks license for each unique QuickBooks user, but you do not install QuickBooks on each workstation. Instead, you install one license of QuickBooks on the server, and you store the data file on the server as well. Then, all QuickBooks users on the network run the QuickBooks application stored on the server – up to 30 users running a single installation of QuickBooks. This networking model is very different from the standard network configuration where QuickBooks resides on each workstation. Because the data file and the application are on the same hard drive, QuickBooks will perform significantly better. In a Remote Desktop Server environment, the workstations do not need to be as powerful, but you will need to purchase a server with bigger resources. You might also prefer to use a separate server just for the QuickBooks program. You can visit the Microsoft Website or contact your IT professional for more information about Remote Desktop Server.
• Take Necessary Steps to Reduce the Size of Your QuickBooks File. You have several options when reducing the size of your file.
  • Use the “Condense” Utility. When condensing, QuickBooks removes many of the transactions dated on or before the date you condense/clean up the data in an attempt to decrease the size of the file and increase the performance of the file. What is the downside? This utility is not very effective if you use inventory items on purchase forms and/or sales forms. Other data conditions can impact the effectiveness of the tool as well. Also, in our experience, 1 out of 10 condensed data files experience some type of data file corruption. Common corruptions include excess data in the Open Invoices and Unpaid Bills Detail reports that are impossible to remove and that cause these reports to no longer agree to the Balance Sheet. In some cases, the Balance Sheet no longer balances (Assets do not equal Liabilities + Equity).
  • Removing All Transactions from the File. This utility is an alternate selection when you "Condense," but this option produces a very different result - removing all transactions from the file. This is a powerful utility that we often use to radically reduce the data file size or to give clients a fresh start when their historical financial information is grossly incorrect. What is the downside? This tool has one weakness - you can't retain any transactions. Only list information, user access privileges, preferences, memorized transactions, memorized reports, form templates, budgets, and timesheets remain in the data file. Since you cannot choose to retain the most recent 1 to 2 years of data, you do not benefit from comparative reporting until you have used your purged data file for several months (or in some cases more than a year). Of course, you can save a copy of your data file before purging the transactions and you can use the information in the pre-purge copy for comparative reporting. However, doing so is much more tedious and ineffective because you can only compare information by printing reports from two separate QuickBooks data files, exporting reports to Excel from two separate QuickBooks data files, or using 3rd-party reporting tools. There is another very practical and negative impact as well. You cannot retain open invoices, unpaid bills, open Sales Orders, open Estimates, or open Purchase Orders - transactions you need to manage your relationships with your customers and vendors.
  • Using a Portable Company File to Re-index Your Data File. QuickBooks 2006 and later allow you to create a Portable Company File – a highly compressed copy of your data file that reduces most QuickBooks data files by 85-90%. To create this highly compressed copy of the file, QuickBooks removes all of the indexing from the file. When you restore a Portable Company File, QuickBooks re-indexes the data file as well as removes most of the database fragmentation, which is a major cause of database performance issues. This re-indexing process will often reduce the size of your data file by as much as 50% the first time you create and open a portable company file.
  • Using a Third-Party Application to Archive Your QuickBooks Data File. Since the Condense feature is not the best option for reducing QuickBooks file size – for reasons stated above – and because removing all transactions from the data file is probably too invasive a procedure – in that you retain no historical information – it may be necessary to find an alternative for very large QuickBooks data files. Moving from QuickBooks Pro/Premier to QuickBooks Enterprise Solutions will provide some increase in QuickBooks performance, but once the file size exceeds 800MB-1GB even QuickBooks Enterprise Solutions could begin slowing down significantly. The ability of QuickBooks Enterprise Solutions to manage large data files varies depending on the type of data in the file and the number of users who log into the file simultaneously. (All of the bullets in this text are other factors as well.) When you have done everything you can do to maximize performance and QuickBooks still performs slowly, you need to find a way to reduce the size of the file while preserving the essential historical information needed to manage the company going forward. For example, many companies need at least 6 months of Sales Order history in the QuickBooks file so their salespeople can refer to the sales history for a customer before providing a quote. So…you can use a 3rd-party product called Data File Transfer Utility (www.q2q.us) to create an alternative to both condensing the file and removing all the transactions from the file. You start by removing all the transactions from a copy of your file. This creates a “shell” copy with all your setup information and list information but with no transactions. Then you use the Data File Transfer utility to transfer the transactions you want to keep from your existing QuickBooks file to this “shell” copy. The result is an archive of only the data you want to remove from your file. For example, if you perform this archive process in June 2021, you can remove all data from the file dated December 31, 2020, and prior and then transfer all the year-to-date transactions for 2021. You will have a full year of data to create a year-to-date Profit & Loss and to provide the tax preparer with the information he/she needs to prepare a 2021 corporate income tax return. If you prefer, you could keep everything dated January 2020 and later so you can create comparative reports that compare your 2021 income and expenses with your 2020 income and expenses.
• Minimize Programs and Processes Running on the Server. Since every computer on the network connects to the server to access the QuickBooks data file, it is important for the server to have as many available system resources as possible. One of the biggest resource hogs can be Antivirus and Spyware Applications. Though you need to use these programs to protect the server from Internet threats, make sure that the server is not running multiple antivirus and Spyware applications simultaneously. Also, many antivirus and Spyware programs have settings to scan the hard drive and RAM throughout the day to detect any threats. Though these settings ensure the highest level of protection, you must decide on the correct balance between hyper-protecting your network from Internet threats and increasing QuickBooks performance throughout your company.

Reducing File Risk with Larger Data Files, Especially When Hosting in Local Area Networks

The QuickBooks database is very stable and is field-proven and time-tested as a reliable way to track your critical financial data. That said, you still need to take precautions to make sure the database is as stable as possible. You can do the following to reduce your clients' propensity for data file corruptions or other types of data integrity issues.

Regulatory Reference the Product Information Window

The QuickBooks Product Information window shows valuable information about the QuickBooks program (e.g., the release level for the version you are using) and the data file you have open at the time (e.g., the number of customers, vendors, and items in the file as well as the file size). When analyzing your clients’ QuickBooks files this information is extremely important. You need to ensure your clients installed the most recent QuickBooks update, that the size of the QuickBooks file is reasonable and that the client’s lists are not nearing the 10,000 limitation for Items and the 14,500 limitation for customers, vendors, employees, and other names (QuickBooks Pro/Premier).

To access the Product Information window shown below, open the QuickBooks data file and then press F2.

Closely Monitor File Size

The size of the file is a major factor in corruption prevention. Though larger files do not necessarily become corrupted, the larger the file the greater the risk. In our experience (and there are many variables) this increase in risk escalates quickly as the file size reaches about 100MB for QuickBooks Pro and Premier and about 1GB for QuickBooks Enterprise Solutions. You should monitor the size of your clients' QuickBooks files and keep the sizes at or below these levels if possible.

There are numerous ways you can reduce the size of your client's data files. As one quick (and strongly recommended) option you can use the Portable Company File process to reduce file size. See the sections below for more information.

Closely Monitor Database Fragments

The Product Information window also shows you the number of database fragments for any given file. If at all possible, your clients should have no more than 9 database fragments in the file. If the client has 10 or more database fragments you can do perform the following steps to reduce the fragments:

Step 1 - Resort the lists in the file. Use the "Resort List" selection in the list menu to resort the lists. Resorting the lists using the list headers does not have the same reorganization impact on the file. Note: Some lists do not have a "Resort List" selection.
Step 2 - Rebuild the File. Select the File menu, select Utilities, and then select "Rebuild Data." You can also use the Rebuild Data utility to repair file corruptions, but the focus in this context is on the reduction of database fragments to reduce the risk of corruption.
Step 3 - Create a Portable Company File and then restore the Portable Company File. This process re-indexes the database and is one of the most effective ways to reduce database fragments. You can also use the Portable Company File utility to repair some data file corruptions, but the focus in this context is on the reduction of database fragments to reduce the risk of corruption.
Step 4 - Run the Windows Defragmentation utility on the drive on which the QuickBooks data file is stored (e.g., the server). The fragmentation level of the computer's hard drive can impact the fragmentation level of the QuickBooks database.

Deploy Best Practices with Local Area Networks

The condition of the client's Local Area Network (LAN) can have a significant impact on the propensity for QuickBooks data file corruption. Consider the following:

Wireless Networks

Wireless networks are the "arch-nemesis" of the QuickBooks data file. The QuickBooks file is sensitive to unexpected disconnections and wireless networks have a much greater propensity to have connection interruptions than connections via Ethernet cable. Strongly caution your clients not to use network QuickBooks using wireless connections. If the network has a wireless router at all, consider that some computers may have both Ethernet and wireless network adapters. To ensure the computer uses the Ethernet connection and not the wireless connection, the client should either turn off the wireless signal completely or take steps to prevent the computers with wireless network adapters from connecting to the wireless router.

General Network Maintenance

Substandard, obsolete, or poorly configured routers can impact both the performance of QuickBooks in a multiuser environment and can increase the risk of file corruption. The concept here is similar to wireless connections. If the configuration of the server or the condition of the hardware causes periodic interruptions in a user's connection, this interruption can cause a QuickBooks file corruption. Examples include connection issues with the network router, hardware or driver problems on workstation network adapters, operating system/resource issues on the server that cause QuickBooks or Windows to enter a non-responding mode, etc.

Remote Desktop

In short, Remote Desktop Server (formerly known as Terminal Services) allows each workstation to share the server’s desktop – much like Desktop Sharing only much faster. When you use Terminal Services, you still need a QuickBooks license for each unique QuickBooks user, but you do not install QuickBooks on each workstation. Instead, you install one license of QuickBooks on the server, and you store the data file on the server as well. Then, all QuickBooks users on the network run the QuickBooks application stored on the server – up to 30 users running a single installation of QuickBooks (for QuickBooks Enterprise Solutions). This networking model is very different from the standard network configuration where QuickBooks resides on each workstation. Because the data file and the application are on the same hard drive, QuickBooks will perform significantly better and each QuickBooks user's connection to the QuickBooks data file is much more stable. In a Remote Desktop Server environment, the workstations do not need to be as powerful, but you will need to purchase a higher-end server. You may also prefer to use a separate server just for the QuickBooks program. You can visit the Microsoft Website or contact your local IT professional for more information about Remote Desktop Server.