My nursing home simulator has had a definite win.

For those who came in late: I work for a company that produces medication management software. One of the problems we found a while back was that the software slowed down a lot in nursing homes. The explanation was simple: in an ordinary hospital, patients come in, get treated and leave. With our software they leave even quicker, because they don't have to lie around taking up valuable bed space to recover from medical malpractice caused by misadministered prescriptions. But in a nursing home, patients come and stay, hopefully for years. So the parts of the program that read patient histories and produce summaries start out OK in nursing homes, but after a few months they bog down horribly. We needed to find out how this was happening, but we had a problem: we don't have several months of useful data from nursing homes to test with; if we did, we'd have already solved the problem.

So I wrote my simulator. It's a Lisp program that reads a set of sample data and some probabilities, and produces a database full of plausible-looking data. It took a while to sort out a couple of bugs, during which time I learned a hell of a lot about how our software works under the covers, but last week it started producing useful results. Want a month worth of nursing home data, with patients coming in, getting prescribed, taking their pills, occasionally dropping dead in the night and being replaced the next morning? Type one command and voila! Want six months worth? Want a year?

Well, a year was a problem. That much SQL takes a long time to generate and a very long time to load into the database. I fiddled around with all sorts of memory settings, and I was getting stumped until my boss suggested inserting GO commands at regular intervals into the script. The GO command is a bit mysterious. It's not actually an SQL statement like SELECT and CREATE, but a command to the server software. If you have a long sequence of SQL statements followed by a GO, the server runs all of them as a single transaction. Sometimes it even rearranges the order of the statements, which can cause problems if you've got a CREATE TABLE followed by an INSERT: better stick GO between those or it might try to insert data into the table before it creates it. But transactions need to be held in memory all at once, like a Jatravartid juggling fifty chainsaws, so my year's worth of data caused my computer a fair amount of stress. Sticking in the GO commands did the trick though, and what previously took hours to run now takes three minutes.

Which brings us to the big win. I opened up the software with a year's worth of data and went to the Administration Overview page, which is what the nurses see when they're about to administer all those pretty pills to a ward's worth of geriatrics. Reports from an earlier nursing home trial had shown a slow-down with even just three months worth of data, so a year should be painfully slow. But when I ran it... it was fine. No slow-down at all. I was mystified for a while -- I even considered taking some of the RAM out of my computer to see if it had been a memory issue -- but then another suggestion from my boss led me down a different path. I disabled the part of my simulator that recorded the administration of medications, so that they were being prescribed but not given. Then I loaded it back in and tried again. Success! Or rather, failure: it failed to load the Administration Overview in anything less than a minute and a half. This was the behaviour we'd heard from the real-world nursing home trial, and was exactly what I'd been given the simulator task to find.

What we think happened is this: around the middle of last year, a new column was added to the database structure, to help reduce the amount of data our software had to wade through to produce the Overview. It works, but only if drugs are being given regularly. If the users prescribe but don't administer, and then try to use the Admin Overview, the system has to read right back to the beginning to find enough history to display on the screen. But if they prescribe and administer regularly, allowing for the occasional delayed and missed doses, the new column records sufficient information that the system doesn't need to go searching. The result: speedy display, no waiting, and no worries for our new nursing home clients.

What it means is: we now know, with confidence, that we're safe. In a way it's less satisfying than if I'd found a problem and had to fix it, but it's reassuring to know that we don't have to rush out an untested patch. Not that we ever do that, but it's good to avoid the temptation. The software is solid and stable, and the simulator has proved its worth. That's the kind of happy ending I like.