Print This Post

QuickBooks Advanced Inventory Database Problems

September 6, 2012 | By | 16 Replies More

A couple of years ago, Intuit introduced a new subscription based add-on to QuickBooks Enterprise Solutions, Advanced Inventory. In its initial year this new feature provided “multi-site” capabilities, allowing users to track inventory quantities for each item by specific physical locations. This gave users the ability to create up to 200 Inventory Sites such as Warehouse locations, Shipping/Receiving facilities and Drop-ship. All of this sounds very good if you need “multi-site tracking”, right?

Update 10/25/2012: A fix for the data corruption issue outlined here has been found, see our followup article at http://www.sleeter.com/blog/2012/10/quickbooks-advanced-inventory-error-identified-repair-now-possible/

As more users have ‘turned-on’ this feature, we have seen significant changes in the database that appear to be overburdening the capabilities of the QuickBooks database, and which may pose additional risks of database corruption. Let’s look at some of the problems that I’m seeing.

With the release of QuickBooks Enterprise 2012, users of Advanced Inventory were treated to a group of additional features they could turn on. One of these was the ability to track serial or lot numbers. Intuit advertises this feature as

“Enter serial numbers and lots at the time of purchase, transfer, or sale for more reliable tracking. You can track defective lots through assemblies and subassemblies, in stock on shelves, and customer purchases with new reports.”

If you need to track “serial or lot numbers” it sounds like the perfect option to preclude you from needing to add a 3rd-party inventory management solution.

While not specifically an Advanced Inventory function, another new capability released in QuickBooks Enterprise for 2012 was Enhanced Inventory Receiving. This effectively separates the receiving (voucher) function from the vendor bill function. The result is creation of both a voucher and a bill, rather than the voucher simply being converted to the bill by use of a feature flag as in other QuickBooks versions.

All of these new features share several RISKS in common.

  1. They significantly expand the size of the QuickBooks database.
  2. They pose, and in fact have caused, a variety of new forms of data corruption associated with their complex data structure.
  3. They stress the abilities of the QuickBooks Database Server to perform within the RAM limitations Intuit has posed.

As future releases of Advanced Inventory features continue to expand capabilities, these problems can only become worse if not addressed by Intuit.

Understanding the QuickBooks Database Engine

Everyone is aware that the performance of QuickBooks becomes worse as the database increases in size. Usually Pro/Premier files show significant declines as file size approaches 250MB, and Enterprise shows degradation starting around 500MB. All of the various table sizes, including the internal link table, impact this performance, but the built-in RAM limitations of QuickBooks also contribute greatly to this.

The more new features, tables and data created within the database, the greater the potential for performance degradation due to the limitations Intuit has posed in the use of RAM. In order to understand the effects of these Advanced Inventory features on your QuickBooks, we need to take a closer look at how Intuit’s RAM limitations impact this.

Databases such as Microsoft SQL or Oracle, and even Sybase, allow for reallocation of RAM by increasing cache size as the database grows. Even though Intuit uses the Sybase database for QuickBooks they have not chosen not to take this approach; rather they limit the use of RAM by setting specific cache sizes for their two levels of product, Pro/Premier and Enterprise. Settings within the products limit how much of the computer’s RAM is used. For Pro/Premier the maximum is 256MB, and for Enterprise the maximum is 512MB. While these numbers are ‘the maximums’ by default there are situations in which QuickBooks will make use of significantly less RAM.

In order to make your data rapidly accessible, each computer application uses “cache”, which stores the data temporarily in the computer’s RAM memory. In the case of Enterprise the database server will begin loading file data into cache until it reaches the maximum permitted, when data needs to be accessed that is not already in cache, cached data must be written back to the disk, and data on the disk must then be loaded into cache. The more a QuickBooks file exceeds the RAM use limitations, the greater the effect of these limitations upon performance.

Because of the extra amount of data created by all these new Advanced Inventory features and the RAM limitations of QuickBooks, we can start to see a significant impact on performance as you start to use the features.

While some ProAdvisors and Computer Professionals have learned ways to adjust system settings so that QuickBooks can use more RAM than the limit imposed by Intuit’s restrictions, these “tricks of the trade” would not be necessary if QuickBooks simply allocated RAM on the basis of total file size.

How Advanced Inventory Affects Database Size

Let’s examine the multi-site feature of Advanced Inventory and how this feature may be impacting your QuickBooks database.

Adding a few fields to a database generally does not represent a significant increase in size, but when features are added that create entirely new tables, especially tables that act as multipliers of existing tables like the Item Site table, the potential for phenomenal growth of the database becomes a distinct probability.

When multi-site is enabled you enter your various “sites” into a new table, the Inventory Site table. Data such as site name, site location, and other generic information is captured. Currently (up through Enterprise V12) there is a limitation of 200 sites that Enterprise supports, so this is not a significant change in the database by itself.

An additional table, the Item Site table, records the allocation of your inventory items to every site. Every quantity transaction for every item for every site is recorded to a site. Not only is the transaction recorded to the appropriate transaction but it is also written to the linked record within the Item Site table, and the Item Site table is linked to the appropriate item in the Item table.

To illustrate this, I set-up a brand new company file and created 10 inventory items, as shown below.

QuickBooks Item List

I enabled Advanced Inventory and set-up an “Initial” site to which the feature assigned all of the existing inventory. This feature also by default creates a “Drop Ship” site. I then added 4 sites (Sites A through D), making a total of 6 sites in the Inventory Site list, shown below.

QuickBooks Inventory Site List

The following Quantity on Hand by Site report shows all of my Inventory items assigned to the “Initial site” since I hadn’t yet transferred any inventory to any other site.

Initial Inventory Balances

If you examine the List Information window in the Product Information screen (press F2), the Inventory Sites and Item Site entries are listed, as shown below. Note that there are 6 Inventory Sites and 10 Item Site listings already.

List Information

I then entered a single ‘inventory transfer’ to take 1/5th the total quantity for each of the ten items and allocate that quantity to “Site-A” as shown.

Transfering Inventory

If we again look at the List Information window, you can now see that the number of entries in the Item Site list has grown from 10 to 20.

Larger number of sites

To continue this process I entered three additional inventory transfers to allocate 1/5 of the total inventory to each of the other three sites I had set up (Site-B, C and D). The Quantity on Hand by Site report below shows the results.

Transfering to additional sites

So let’s examine the effect upon the Inventory Site table shown in the List Information. There are 50 site entries. Fifty records have been entered in this Advanced Inventory table to allocate 10 items to each of 5 sites.

Even more sites

Now instead of my extremely limited example, imagine if you had 1,000 inventory items. As the Multi-site feature begins to work, it immediately posts 1,000 entries to the Item Site Table allocating existing item inventory to your default site location. If you then transfer that inventory to each of your sites, so that items are recorded to just 20 (of the 200 possible) sites, the number of records in the Item Site table has grown to 20,000 records. Just 20 Inventory transfer transactions have created 20,000 records within a single database table.

If we take this a step further and assume you have 10,000 inventory items, and just 50 sites, you are talking about 500,000 records created within this table. Of course on the ‘back-end’ the database server is also creating data-objects to track the relationships between the various site, inventory site and item tables as well as the transactions related to each. All of this means that the Advanced Inventory feature is expanding you QuickBooks file size very quickly, so that less and less of the file can be loaded in cache because of the RAM limitations Intuit has established.

Multi-site Related Corruption

As if the growth in file size created by the Advanced Inventory Item Site table is not enough of a compromise on the ability of QuickBooks to perform, a whole new set of database corruptions have come to light associated with the Multi-site feature. Below is an excerpt from a QBWin.log file showing several examples of one such error.

QuickBooks database corruption

The error mixed sites in targets which verify/rebuild cannot fix is exclusively associated with this Advanced Inventory feature. These errors compromise the Verify utility, and prevent QuickBooks from backing-up unless you don’t select full verification.

What is worse, from my perspective, these errors seem to be getting more common. In my own QuickBooks ProAdvisor practice I have encountered this error six or more times in the last 20 months since the Advanced Inventory feature was first offered. Twice in the 2011 version of Enterprise; and several more times in the 2012 version. Neither myself, nor Shannon Tucker (of AccountingUsers, Inc.) were able to resolve these issues.

Even though this type of error clearly reads “Please contact support”, I am aware of at least one client who subsequently sent their file to Intuit only to be told days later that there was no fix for this type of error. They were advised that the best solution would be to start a new file with an inception date subsequent to the error. It therefore appears that some Advanced Inventory corruptions cannot be resolved even by Intuit.

Editors Note: We are working with Intuit staff to investigate this error further.

How Other Tables are Over-Growing Your QuickBooks

Serial Numbers

The Advanced Inventory function also offers the ability to track serial numbers for your inventory items. This feature adds a significant number of new tables. Each type of transaction you would expect to see serial numbers recorded on (invoices, vendor bills, inventory transfers, assembly builds, etc.) has new companion tables to track the serial numbers. Since the serial numbers are stored in fields within each of these new tables, rather than the actual transaction tables, a field recording the ‘target’ (related) transaction is also posted to this table, so that the serial numbers can be linked back to the record with which they are associated.

If you are adding hundreds of serial numbers for each of thousands of inventory items, which are used on purchasing, inventory control and sales type transactions, the number of records will grow substantially over time. That growth will significantly increase the overall size of your QuickBooks file and tax the ability of the database server to provide timely access.

You can implement lot numbers instead of serial numbers, and that will have a similar impact (although the volume may be smaller).

Enhanced Inventory Receiving

Prior to this feature, QuickBooks recorded Item receipts and Vendor bills for those items using one set of forms, the “Bill Header” [the Master Record] and the “Bill Detail” [the Target Record(s)] transactions. Essentially a flag within the record differentiated it as either an item receipt or a vendor bill. Because they are one transaction a number of issues were common with respect to inventory items recorded in this manner.

For years, many problems associated with QuickBooks average cost have been tracked back to negative inventory resulting from sales occurring between the time that inventory was received and when the bill was subsequently posted. Upon receipt of inventory and the posting of an item receipt the inventory quantity was increased as of the receiving date. Later, when the bill was posted, the date of the item receipt (now a bill) changed and the previously received inventory was post-dated. If intervening sales relied on the previously received inventory to have positive inventory on hand, those inventory sales were subsequently turned into transactions which caused inventory to ‘go negative’.

In order to give users, with significant inventory, the option of segregating the receiving function from the vendor bill function, QuickBooks introduced the Enhanced Inventory Receiving (EIR) feature. Now the receiving voucher is recorded as an entirely unique transaction impacting on-hand inventory, separate from the bill. In order to accomplish this two new tables are added to the database in order to record the header and details of each of these transactions. This in effect doubles the total number of records required to record both receiving voucher and vendor bill.

An additional “behind the scenes” issue should be mentioned here. Since either the Purchase Order and Receiving Voucher, or Receiving Voucher and Vendor Bill, are ‘linked’ to each other, the database server appends this information to the QuickBooks link table. This is one of the largest tables in the database. Because most QuickBooks users with “large files” were advised to not to turn on the Enhanced Inventory Receiving feature unless they started new Company files, I have not been able to determine how significantly this feature maybe increasing the size of the link-table.

Lastly, because both site information (if that option is turned on) and serial or lot numbers information (if that option has been chosen) are recorded on inventory item transactions, the QuickBooks file can grow quickly as a result of the extra records created with the EIR feature.

There have been several articles about the drawbacks of Enhanced Inventory Receiving in this blog. One in particular relates to the problems I’m outlining here, QuickBooks Enhanced Inventory Receiving – Bill Linking Problems. To summarize, in cases where purchase orders have been created, those purchase orders are subsequently “linked” to the receiving voucher. However, in those cases the receiving voucher is NOT linked to the vendor bill when posted. In transactions not involving purchase orders, links are created between the receiving voucher and vendor bill. This approach can cause confusion at the very least.

What about the Future of Advanced Inventory?

There are many third party add-on products that provide features like multiple-preferred vendors and prices, duplicate parts (differentiated by class or some other characteristics or attribute), barcode enabling scanner interfaces, rack/shelf/bin locations for pick/pack/ship control, and many more. Because of the growing popularity of the Advanced Inventory features, there is every reason to believe that Intuit will be expanding this to include similar features and options.

While there are advantages to having an “all-in-one” solution, as opposed to having to use a companion product that takes over parts of what QuickBooks normally does, there can be substantial disadvantages as well. When a 3rd party product is performing the complex tasks for inventory, an entirely separate database and database server is being used. The developers of those products have designed their applications to maximize available RAM for greater performance, particularly when compared to how Intuit has implemented the QuickBooks database.

When everything for effective inventory control is dumped into the QuickBooks company file database it significantly adds to the number of tables, substantially growing those tables as data is appended to each. This greatly increases the size of the overall QuickBooks database. Given the RAM limitations Intuit has imposed on the Enterprise product, and the new forms of database corruption arising out of these new features, one can only ask, “Is Advanced Inventory Crippling QuickBooks?”

Tags: , , , , ,

Category: Manufacturing and Inventory, QuickBooks Tips/Tricks, Technical QuickBooks

About the Author ()

William “Bill” Murphy is an Advanced Certified QuickBooks ProAdvisor with over 30 years of financial and teaching experience. His consulting company, RRR, Ltd. has been helping businesses get the most from their Intuit software since 1989. His practice focuses on system set-up, product integration, problem resolution, data file analytics and repair. He holds both Bachelors and Masters Degrees from the University of Central Oklahoma, and is an Adjunct Instructor for Oklahoma City Community College-Corporate Learning. He also served as Technical Editor for Business Analysis with QuickBooks by Wiley Publishing in 2009.

Comments (16)

Trackback URL | Comments RSS Feed

  1. Lyn Barr says:

    Great article, very useful. Thanks, Bill!

  2. Steve says:

    Bill

    Great article. It explains why Intuit technical support is so big on creating new files when they can not understand why something is happening. Thanks for sharing about the RAM limitations as well. Now when I am trying to manage customer expatiations on what QuickBooks can and cannot do as well as advising on what version to purchase, I can back it up with some facts as well as what I have seen in the field.

    Thanks
    Steve

  3. Sendy says:

    Quick Books 2012 R9
    Company file size over 3.9 GB
    22 users
    Gigabit Network

    Since we paid a QB professional to setup QB to use 3.7 GB of RAM the speed of QB has increased tenfold, we are in the process of testing QB to use 32 GB of RAM. For better speed we moved the company file to run on a Solid State Hard Drive RAID 1.

    Reports that used to take 8-12 minutes are now complete in about 2.5 minutes. Saving an invoice used to take about 30 seconds now it takes about 2 seconds.

    Thanks
    Sendy

    • Sendy, you mention a lot of changes, hard to say which has the most impact. It isn’t clear if you moved up to a Gigabit network from something slower, for instance.

      You can improve performance by doing many things. Faster network architecture. Changing the QB database server to a faster system, or to its own system. Using a faster drive. All those improve speed. RAM? That depends on what ELSE is going on the system that has the database server as well, so it is hard to pin that down. More RAM can make a system run faster if it has a lot of programs or services running.

      • Graeme Leo says:

        I would think the SSD drive would be making the biggest difference in Sendy’s situation with SQL being most intensive on disk read/write rather than network speed or RAM.
        Great article Bill, quite revealing.
        As developers integrating accounting products we work with both small and medium size accounting products and customers who work with them. Typically firms will hold onto the ‘old faithful’ for far too long, they resist spending on more capable mid-range accounting products and the break point is often multi-warehouse requirements that pushes small scale accounting systems beyond their limit or the daily quantity of invoices that are expected of them. Trying to squeeze the maximum from QuickBooks or any other similar product I don’t think ultimately makes sense. Yes their are great add-ons that will off-load the work, but it adds complexity and more points of potential failure, upgrade complexity etc.
        Interesting that their is such a limit on cache size. I do wonder if there might be some commercial arrangement where Intuit are licensed to use Sybase within cache constraints. Why wouldn’t they just lift the cache constraint, Intuit don’t need to restrict functionality to protect any other product in their accounting software range.

  4. Lloyd says:

    Interesting to hear that QB can be made to access more RAM. How do we go about doing that?

    • Lloyd, I assume that you are in New Zealand, given your address. I’m not sure how much of this applies to the “Reckon” version of QB that you are probably running.

      The tweak that Murph refers to is essentially a registry tweak, and I don’t like to publish those kinds of things in a blog like this. Too easy for someone to do incorrectly. In any case, it most likely has to be re-set every time you upgrade to a new revision of the program. It is something that Mario Nowogrodzki talked about in a session at The Sleeter Group’s Accounting Solutions Conference a couple of years ago – and when I asked Mario about it recently, he said that he didn’t usually implement it because it didn’t “stick” in the installations.

  5. Murph, nice analysis. Beyond the couple of cases you mentioned where we worked together, we’ve had another couple of other instances of ES 12 database corruption in which the only way we could repair the files successfully was to turn of advanced inventory…the database worked OK after repairs and AI turn-off. The mess in AI was unfixable.

    Your discussion of how AI would greatly ramp up the database size and use of system resources rings true.

    Thanks for the details — good job.

    • Willliam Murphy says:

      Thanks Buddy! Hope the trout are biting there. I’m wishin I was fishin rather than fighting off the West Nile mosquitoes in the 140 heat.

      Murph

  6. Willliam Murphy says:

    Thanks to everyone for your positive comments. They are most appreciated.

    Murph

  7. William Murphy says:

    If you have any A.I. issues you would like to share with me, as I continue to explore these areas of concern; or if you want more specific information on any of the issues discussed in this article, feel free to contact me ‘off line’ via the information contained in my profile linked from the About the Author section above.

    Murph

  8. Nice to see somebody tackle the specifics of the taxing effects of additional features on the QuickBooks database.

    The effects of cache size can be debated because there are multiple correlations, e.g. threads, page size, connections, buffer and other items that people smarter than I am get paid for. We have worked with Sybase on these effects in our own QQube technology.

    The effects of a higher platter speed SAS drive, which are designed to queue multiple requests (as opposed to SATA drives), and more actual memory will increase performance significantly – along with a more modern processor. Time is truly money here.

    However I will agree strongly with you, that features are being added that make performance and usability with Enterprise a problem. One of your commentors made the remark about expectations – that is a necessity.

    And no matter what hardware, cache you throw at this, there are limitations.

    Think about other areas where space was ‘considered': balance sheet by class is calculated – not stored; lots and serial numbers in memo fields; there are no item based links; etc. etc.

    Intuit is forced to live with cramming in new features based upon two decades + of old technology that they worked into a true database. And I get tired of hearing that ‘it is not a true database’. Having worked in it when I was contracting for Intuit – it IS a true database. It just has limitations because of history – and there are some very smart engineers over there.

    When Enterprise first arrived on the scene Mario and I screamed loudly about the famous ‘you can load one million items’ marketing notice a few years ago. The marketing fluff vs the reality were hard to live with – and still can be. This is what keeps people like Matt Clark in business when he creates new files / shrinks existing ones for customers.

    It still is a cheaper alternative than higher end systems, and some people will live with the hassle. Problem is Intuit Sales sells the fantasy, and people like us have to deal with reality. But than can work to our advantage too.

    Our rule of thumb continues to be (after thousands of files): if you approach to 1.2 Gig go to Matt; if you get to 2 Gig you surely will have corruption.

    Keep up the good writing.

  9. William Murphy says:

    A brief update commentary to the article……

    Now that the new products are being released, we can also see that the new list limits in QBES 2013 will contribute to overloading of the database.

    For example, the Inventory Site List for the Advanced Inventory feature has been expanded from 200 Sites to 1,000,000 sites. We can easily see how such an expansion will significantly impact the “Item Site” table’s exponential growth (when compared to the examples in the article above.)

    Many other tables are also being expanded for 2013; I am not sure how many ‘typical users’ will ever reach these kinds of limits, but the overall ‘structure’ to deal with the expansions pose the potential for ‘taxing’ the database to the limits.

    Tables such as the Chart of Accounts, expanding from 10,000 to 100,000; and the Class list, expanding from 10,000 to 100,000 and now having ‘associations’ with other lists by default (such as Items) mean more complexity to the database.

    I don’t even want to get started on the fact that the Memorized Transaction list is being expanded from 29,000 to 50,000; almost everyone recognizes that Memorized Transactions are a significant source of corruption.

    Murph

    • Cheryl says:

      In your article you state “Enterprise shows degradation starting around 500 MB”. Our file size is currently 390,836K. We converted to QB Enterprise in 2009, so we have about 3 1/2 years of history — and are currently using the Advanced Inventory, but not the enhanced receiving feature. What steps do we need to take, how soon, and how do we reduce our file size?

  10. Excellent article. Thank you for this information.

Leave a Reply