Advertise here    

QuickBooks Forums      

Go Back   QuickBooks Forums > Other QuickBooks Forums > Suggestion Box

Reply
 
Thread Tools Rating: Thread Rating: 9 votes, 5.00 average. Display Modes
  #1  
Old 10-15-2013, 05:44 AM
BrentNewland BrentNewland is offline
Registered User
 
Join Date: Dec 2010
Posts: 5
Lightbulb Splitting QB databases into sub-databases by year

I suggested this here (vote if you think it's worthy): http://intuit.uservoice.com/forums/9...in-sub-databas

Quote:
I work as a computer repair technician. We have quite a few clients that use Quickbooks, and a moderate percentage have the same issue: large databases (approaching and exceeding 1GB in size).

These customers, for various reasons, either cannot or will not archive previous years data, resulting in these large file sizes.

Some of them cannot upgrade due to having government contracts that require long term data retention. Some need to have quick and immediate access to all previous data from a customer. Heck, one of our customers bought Quickbooks Enterprise with a full support contract because of their file size, thinking it would help (it didn't).

The solution is quite simple. Quickbooks databases currently make use of subfolders. Instead of keeping all the data in one database, the actual data for each year could be kept inside of yearly databases, in these subfolders.

The primary database (the qbw file) would switch to being a databse for cross-year data, such as financial reports, customer and vendor lists, and program settings.

When data (such as an invoice) from a previous year needs to be accessed, the program can just display a "Retrieving archived data" message, open the sub-database for the year that has the information, and then display it for the user. When they close the item opened, the connection to the sub-database is closed.

Sub-databases for the current and previous years would be opened at all times.

You could even go further and have a quarterly-split option.

This would all also have the benefit of increasing data protection - one corrupt file now only affects a year of data instead of all of it. It increases performance by limiting the size of the database that needs to be opened. It reduces the possibility of corruption of individual databases due to a simpler file structure (and small files are less likely to experience saving problems partway through saving).
Reply With Quote
  #2  
Old 10-15-2013, 07:39 AM
RobJoy RobJoy is offline
Registered User
 
Join Date: Mar 2003
Location: UK
Posts: 3,858
Speaking as an ex-programmer, I don't think this is remotely feasible.
__________________
Joyce Beck
joyce@pc-firstaid.com
------------------------------------------------------------------------------
Accounting and bookkeeping support, QuickBooks Pro Advisor
Home and small business computer services in Northampton, UK
Reply With Quote
  #3  
Old 10-16-2013, 12:30 AM
cojhl2 cojhl2 is offline
Registered User
 
Join Date: Jan 2010
Location: Colorado, SE Washington
Posts: 1,354
I agree w/ Joyce. It is possible but not feasible. I have worked on systems like this (Multiple Systems Coupling) but it is no place for a PC to venture.

Here is what I would have them do: first backup (always backup!) the database. Do a systems condense back to a date which would keep most of the detail data they need to have online.

If for some odd reason they need to retrieve detail data from prior period that could be done in a pseudo offline basis.

I worked with a govt contractor in the 70's who could go back and redo complete transactions from the 50's, but it was done by request only and then it was run overnight. And today they can still go back and find that same data.

The reason this works is because the user agrees to the longer window of service.
__________________
Colorado Springs and Eastern Washington
QB Pro 2016 used for two farms and personal
Reply With Quote
  #4  
Old 02-24-2014, 01:24 PM
BrentNewland BrentNewland is offline
Registered User
 
Join Date: Dec 2010
Posts: 5
Speaking as a programmer myself, this would be trivial. In MySQL, it would be as simple as creating a new database, copying the yearly data to it, and deleting the data from the primary database.

I'm sure Quickbooks can figure out how to have the software access secondary database files.
Reply With Quote
  #5  
Old 07-03-2014, 01:44 PM
peanutt724 peanutt724 is offline
Registered User
 
Join Date: Jul 2013
Posts: 4
As a Quickbooks user of 20+ years, and former employee of Intuit. I think that would be the most awesome thing Intuit could do.
Reply With Quote
  #6  
Old 07-03-2014, 05:15 PM
cojhl2 cojhl2 is offline
Registered User
 
Join Date: Jan 2010
Location: Colorado, SE Washington
Posts: 1,354
Quote:
Originally Posted by BrentNewland View Post
Speaking as a programmer myself, this would be trivial. In MySQL, it would be as simple as creating a new database, copying the yearly data to it, and deleting the data from the primary database.

I'm sure Quickbooks can figure out how to have the software access secondary database files.
It is not the mechanics of moving and deleting rows of data, it is the data results that must be maintained.

Look back to manual bookkeeping, what did we do? We closed the books periodically built "Balance Carried Forward" and started new books with "Balance Brought Forward".

It seems to me the same thing is accomplished (or should be)with "Condense Data".

It is not an issue with relational databases, it is the data itself and it's useful integrity.
__________________
Colorado Springs and Eastern Washington
QB Pro 2016 used for two farms and personal
Reply With Quote
  #7  
Old 07-03-2014, 06:11 PM
BrentNewland BrentNewland is offline
Registered User
 
Join Date: Dec 2010
Posts: 5
Quote:
It is not the mechanics of moving and deleting rows of data, it is the data results that must be maintained.

Look back to manual bookkeeping, what did we do? We closed the books periodically built "Balance Carried Forward" and started new books with "Balance Brought Forward".

It seems to me the same thing is accomplished (or should be)with "Condense Data".

It is not an issue with relational databases, it is the data itself and it's useful integrity.
I'm not sure what you're trying to get at. In any case, your information is not what we're trying to accomplish.

Condensing data and closing books and carrying the balance forward removes information from the database and makes it inaccessible, unless you have the old version of Quickbooks and the old databases.

We want to actually be able to ACCESS this old information.

The reply you quoted was in response to this statement from you:

Quote:
It is possible but not feasible.
Which is completely, utterly wrong.

Not only is it possible, it's EASY.

Quickbooks databases will choke when they get up over 900MB in size, which is very easy for a several year old company with lots of activity. Companies that size want to be able to access all this information.

The fact that Intuit's solution to this is "Throw out your old data and go through hoops to access anything in the past" is a cop-out, and downright LAZY.

Quote:
It is not an issue with relational databases, it is the data itself and it's useful integrity.
No, it IS an issue with Quickbooks' databases, since they get corrupted and slow at that size. Other databases like MySQL simply do not have that problem.

Since Quickbooks won't let you use a 3rd party database server, the only reasonable option is to keep each year's data in separate databases, which will keep the size of each database far down.


Quote:
I have worked on systems like this (Multiple Systems Coupling) but it is no place for a PC to venture.

The reason this works is because the user agrees to the longer window of service.
"No place for a PC to venture"? What does that even mean? Are you saying a computer can't run multiple databases? A home computer could run a million databases.

How exactly does a "longer window of service" affect how your data is stored? If you pay more money it's easier to access your data?

=============================


Sorry if I sound rude, but I've already explained this to you, and I don't like repeating myself. You are wrong, I am right, you are clearly discussing something that's out of your area of expertise.

From what you've said, I'm guessing you're quite an older person who used to work with databases decades ago. Your knowledge is no longer applicable.
Reply With Quote
  #8  
Old 07-05-2014, 12:07 PM
cojhl2 cojhl2 is offline
Registered User
 
Join Date: Jan 2010
Location: Colorado, SE Washington
Posts: 1,354
BrentNewLand, Yes you were rude. To that we agree. Apparently, I stoked your anger by what I posted earlier and if I appeared to be insensitive I do apologize, degrading otherís opinions on a personal basis is not what I mean to do.

Qualifying me for adding my 02 to this discussion is my almost 40 yrs in the DP industry and being there when Dr Codd of IBM first developed the Relational model which I am sure QB either with their own code or with purchased code follow. Before DB2 and Oracle, the prototypes of Dr Coddís model, I worked extensively with IMSDB/DC where we had the same performance issues we fight today.

I worked with Large Systems and Large Storage Systems, both in the Communications industry and Manufacturing. These systems especially in communications had to handle thousands of transactions a second. As far as manufacturing is concerned, my experience is entirely in IMSDB/DC. I still do not understand how a relational system handles gizinta!

Splitting up a company to multiple data management files by date or any criteria for that matter, would violate one of the rules of the Relational model. The Relational model is clear that the user will not see the data in as a distributed source. QB application data crosses date boundaries without regard.

So, following the model, QB DB would have to implement some type of Distributed Database which then introduces the complexity of integrity and 2phase commit.

Assuming that the Database is split into multiple datasets the performance problem still exists. The Select clause causes the DBMS to scan data from the beginning unless there is available an index providing a shortcut. So, if one wishes to have all the data available all the time then the access systems would merely go from one dataset to the next taking up the same time it does now.

One solution to this of course is to have large cache systems either in main storage or in the storage devices.

Data Management in a PC and Windows architecture is quite archaic as compared to Large Systems so I dont know if creating large cache in the data path is possible.

I still think the solution to this issue is to have a condense process where the database is sized down and the Carry Forward logic of the old days is implemented.

My 02 anyway!!
__________________
Colorado Springs and Eastern Washington
QB Pro 2016 used for two farms and personal
Reply With Quote
  #9  
Old 07-05-2014, 12:33 PM
BrentNewland BrentNewland is offline
Registered User
 
Join Date: Dec 2010
Posts: 5
Angry

Quote:
Splitting up a company to multiple data management files by date or any criteria for that matter, would violate one of the rules of the Relational model.
And that matters... not one bit. This is what I mean when I say your knowledge is not relevant. You seem to think that a database model developed 50 years ago is... scripture or something.

Quote:
The Relational model is clear that the user will not see the data in as a distributed source. QB application data crosses date boundaries without regard.
So?

Quote:
So, following the model, QB DB would have to implement some type of Distributed Database which then introduces the complexity of integrity and 2phase commit.
Oh, nice, try and flood the topic with rarely used technical terms in order to make your argument look flashy so people will not question what you post.

"Complexity"? Minimal. "2phase commit"? Unimportant.

Quote:
Assuming that the Database is split into multiple datasets the performance problem still exists. The Select clause causes the DBMS to scan data from the beginning unless there is available an index providing a shortcut.
And here's where you fail completely.

You see, you would have an "index table".

GASP! SHOCK! AWE! A single table which contains critical information, such as customer information and a list of invoices/receipts/etc.? Who could have thought of such a thing?

Oh, that's right, Google, and pretty much every company that deals with search.

There is almost no need to search through every separate databases. Have you even thought about what information is year specific? The content of invoices, purchase orders, quotes, receipts, etc. This content is already unsearchable.

Quote:
So, if one wishes to have all the data available all the time then the access systems would merely go from one dataset to the next taking up the same time it does now.
No. Incorrect. Wrong.

You access your index database to determine in which specific sub-database the full data is in. Two database accesses. That's it. On a personal computer? Even with 20 people accessing the database? Minimal impact.

Quote:
Data Management in a PC and Windows architecture is quite archaic
And that's where we get to the root of YOUR issue.

You're obviously not familiar with personal databases. Everything you've said so far might apply to BILLION+ row databases that are TERABYTES in size, but mean jack squat in a personal or low-usage environment.

Quote:
I still think the solution to this issue is to have a condense process where the database is sized down and the Carry Forward logic of the old days is implemented.
You mean, throw out data until the database isn't slow.


What I've proposed is SIMPLE, it would have ALMOST NO PERFORMANCE IMPACT, it would IMPROVE DATABASE RELIABILITY, and it would be FAR SUPERIOR to your only suggestion, which has been "Throw away old data".


I'm done with you. You've come into this thread with my useful suggestion, crapped all over it with bad ideas, bad logic, and bad reasoning, and keep spouting the same unrelated and outdated ideas.

You might as well not reply, because I will vehemently oppose any suggestion you might make.
Reply With Quote
  #10  
Old 07-05-2014, 01:37 PM
cojhl2 cojhl2 is offline
Registered User
 
Join Date: Jan 2010
Location: Colorado, SE Washington
Posts: 1,354
OK, I'm History.
__________________
Colorado Springs and Eastern Washington
QB Pro 2016 used for two farms and personal
Reply With Quote
Reply

Bookmarks
Bookmark and Share
Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump


All times are GMT -5. The time now is 03:25 AM.


 

Powered by vBulletin® Version 3.8.4
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
All contents copyright (c) by AccountingUsers Inc.
You Rated this Thread: