Thursday, July 21, 2016

Workbook Versus Database

The first step towards a database is usually one's first Excel sheet. In time one's sheets get collected in workbooks, then shared within a team or distributed to a number of persons.

Finally when the mess runs out of control, some IT guys do save the business by creating a database and processes for updating it.

Most of the time users spend a number of years with their growing sheets, and sometimes they are investing in additional hardware for being able to continue using some "cool" sheet, which includes hundred thousands of formulas.

Excel is employing so-called "tight loops" for getting through the calculations as quickly as possible, and these tight loops are big resource consumers.

Sooner or later a sloppy sheet with too many formulas is going to challenge too much the operating system's resource management capabilities. In other words workbooks are not scalable data containers.

Theoretically it's possible to remove all the formulas from a sheet and to use VBA or other scripts for calculations.

In VBA tight loops can be mitigated by enforcing them to output some value in a cell from time to time (external interrupts give way to the garbage collector to do its job).

The real problem is that beyond a certain level of complexity implementing one's business logic in VBA (or other script) and workbooks would lead to more expensive and less reliable software than opting for a scalable database solution. 

No comments:

Post a Comment