The Document Cursor
IniCAD was one of the world's largest purveyors of CAD software. In the earlier days of this industry, they used more "industrial" sales models: rarely did you buy a license from IniCAD directly, but instead bought through a dealer who was technically a third party, but essentially was IniCAD's representative.
If you build designs in CAD, at some point you need to turn these into drawings. In the industry, the drawings are 2D, static images that represent a canonical representation of your picture of the object/building/machine being designed. They're one of the primary tools for making sure that all of the various teams working on a large scale construction or fabrication project can all communicate accurately and precisely. Nothing is worse than having a building's electrician working from one drawing as they plan their wiring, and having the framers working from another drawing, and putting their walls in different places than the engineer expects.
So IniCAD tried to solve this problem, by implementing their own document management solution, IniDOC. They implemented it in house, called it "good enough", and then shoved it off to the dealers to sell.
Judi worked for one of those dealers as a TSSR, and was expected to demo this product. There was just one problem: it didn't work.
IniDOC was a pile of VB code (IniCAD's flagship product was scriptable in VBA) that somehow managed to contain its own little copy of DLL Hell in its own dependencies. The first time Judi tried to install it, it borked her computer so badly she needed to do a fresh install of Windows. The second time, she got it to install, but couldn't store documents in it. With each glitch or problem, she went up the chain to complain to IniCAD, and each time IniCAD escalated her issue.
By the time Judi was ready to start doing demos for clients, she had a team of IniCAD developers on call for the entire demo window. The code she was running for her demo was a hodgepodge of non-version-controlled quick hacks and duct-tape. Certainly, it wasn't the "gold master" or "release candidate" or what the customer would actually get.
The demos went terribly, and Judi spent about six months touring Europe looking like an idiot shilling a dumpster-fire of a product. It was soul crushing, and at the end of that period, the sales team had one sale for the entire region to her credit. A few months after that, her one sale demanded a refund: a glitch caused the system to crash and destroy all the documents they were storing in their document management system.
IniCAD realized that they had made a terrible mistake, trying to branch away from their specialty and into a new product niche. They quietly withdrew their IniDOC product, and then did what all companies with a decent cash reserve do: they bought a third party company that had a product better than theirs.
IniCAD purchased "Docutrode", the "best in class" document management tool for architects, engineers, and fabricators. They relaunched it as IniDOC V2, and Judi was once again doing a round of demos.
It went better, but not great. The new document management product was an attempt to clone Visual Source Safe, and the install process was fraught with unnecessary complexity. While IniDOC V2 actually made sales, most of the profit was eaten up by the high volume of support calls, and those calls were almost entirely configuration and install issues.
IniCAD had a choice: they could improve their documentation, streamline their support path, redesign the install process to cut down on errors, and maybe do some business analysis to get a better sense of what the "right most of the time" defaults should be, or they could do a ground up rewrite using the team responsible for IniDOC V1.
They did a ground up rewrite. Judi, working at the dealer, got to see lots and lots of marketing copy about it, lots of promises, lots of "Web 2.0!" branding. Release dates came and went, without a product. For awhile, Judi wondered if IniDOC V3 had died, but no software product dies if you're willing to throw good money after bad, so it eventually released.
What Judi received was a software package that ran an IIS web server and a SQL Server database. It could support, in theory, hundreds of simultaneous users, but in practice more than five meant requests started timing out.
Judi didn't write the software and didn't have access to most of the source code, but she wanted to understand why it was so slow. So she started poking around in the database to see the structure.
The core of the database were three tables: one which actually stored the documents and their data (documents), one which lists off all the possible attribute metadata for any document (attributes), and one which links documents, attributes, and their values (document_attribute_values). Because metadata could be anything- numeric, text, even a thumbnail image- the only workable datatype was to store everything in BLOB columns.
On the UI side, as the user navigated the application, they saw logical folders, and the files in that folder were listed with metadata columns. The user could set global preferences for which metadata columns they wanted to see.
Judi poked around in the database, and found that almost all the business logic was implemented in stored procedures. And that's when she pulled up the procedure which handled that browsing.
The logic went basically like this:
Create a temporary table for the folder you're about to display. It has one column, document_id, and has one row for every document in the folder. Then, it opens a cursor on the attributes table. For every possible metadata field, it alters the query table to add column for the metadata field. Then, inside of that cursor for loop, the code opens a second cursor looking at the document_attribute_values table. There, it pulls the values (if there are any), and populates them into the column on the temporary table.
Once every document in the folder had gotten that treatment, the entire table was sent to the client side, where the users' display settings got applied and the metadata columns they didn't want to see were excluded.
Metadata fields were billed as being customizable and flexible. The application encouraged users to create metadata fields with abandon. A single deployment might have hundreds of possible metadata fields, but a single document might have, at most, thirty. In practice, most users only ever needed to see 5-8 columns.
Each time an engineer changed folders, a cursor-driven pivot table was created with hundreds of mostly empty columns, of which only a handful actually mattered. All that data went back to the client side, where the filtering happened.
IniDOC V3 sold slightly better than its previous versions, more due to increased demand for a document management solution than because of any merits to the product. Judi left IniCAD's dealer before IniDOC V4 had a chance to come out, but based on conversations with former co-workers, Judi believes it still more or less works this way.
Which should be a lesson to all software developers: all performance problems can be solved by simply not changing any code and waiting for hardware improvements to make it run faster.
[Advertisement] Otter - Provision your servers automatically without ever needing to log-in to a command prompt. Get started today!