It’s an interesting topic for this month’s T-SQL Tuesday, and while I think this isn’t necessarily a SQL Server project, because usually SQL Server isn’t the problem with building complex software, my death march involves SQL Server.
This month’s invitation is from Jeff Mlakar and I really am looking forward to reading some of the entries.
Adopting OOP
If you’ve ever worked on a greenfield OOP project, you’ve probably felt like there is a lot of work investment up front that builds a base for your application. For a long time bit, it feels like very little is getting done as you build classes and subclasses, and experiment with the structures that make sense. Your users and managers may complain wonder about what work is actually being completed in this stage.
Eventually this tips over and all of a sudden you have something to show for your efforts and your objects start becoming useful and visible to others.
This is my story.
Leaving FoxPro
Early in my career, I learned to program using FoxPro. This served me well and let me build a number of useful applications for various clients and employers. Eventually I was hired to manage a tiny small IT department that had to maintain and existing FoxPro inventory and order entry system and the internal infrastructure, as well as manage a rewrite of this application to a more powerful, useful system.
Prior to my being brought on, the company had hired a consultant to handle the rewrite, while I managed another employee who maintained desktops, servers, printers, and more. The consultant had decided to move to Visual FoxPro backed by SQL Server. Since I had experience with FoxPro, SQL Server, and infrastructure, I ended up managing and participating in both sides of this environment.
Visual FoxPro
I had more experience with SQL Server and the consultant had more experience with Visual FoxPro, so I handled setting up the database and discussing some of the data modeling choices with him. He made all the OOP and VFP decisions, with the estimation that this would be a 8-10 month project. After all, we had an existing system, most of the business rules were already encoded, and we weren’t looking to add much functionality.
Over the first 2-3 months, regular meetings showed me code being written, with some basic scaffolding of the objects we’d used and the various properties and methods that were required. We agreed on a object to relational mapping, and things seemed to be going well.
Disaster Strikes
The first issue that set us back was a disk crash early in month 3. The consultant had been backing up work to a zip file on his machine, but not the network. You can cry along with me guess what happened. His machine crashed, and we were slightly stuck. Fortunately, we discovered a copy of code that was weeks old, and managed to restart development.
At this point, we had no VCS in place and my boss wasn’t interested in buying VSS. I decided on a simple setup. We worked off file shares for all code, with coordination on which files we’d edit (we were across the hall from each other). We also scripted a simple method to zip up all code and copy it to a different folder for each day of the week. Rudimentary, but it gave us some control in the mid 90s.
Complaints Curiosity Rises
About 6 months in, my boss started to ask questions. Where were the prototypes? Where is something to test. We put him off, showing him some basic objects, and explaining how we were front loaded with setting up new classes to better handle our business rules. We were coming from a procedural system, so much of the code had to be rewritten to work in an OOP environment.
I was told that we needed to move, so I started joining in with coding. Up to this point, I’d just done some reviews, and now I needed to brush off some of my OOP skills from college as well as learn more about VFP, which was different from FoxPro.
Over the next few months, I started pushing us to make decisions and move, rather than debate whether we needed to set parent methods that would be shared by multiple subclasses. I argued with our consultant, insisting we move on, even if we had to store some code in both subclasses. We needed to get work done.
A Large System
This was a fairly complex inventory and order entry system, unique to our business, and somewhat large for two people. At 8 months, we still didn’t have a working prototype, though we had certain items built and could show to users.
We also had change orders coming in, with requests to alter some functionality that wasn’t as useful or relevant as it had been in the past. My ability to help rose and fell as other issues came up, so development was sporadic.
A New Server
Finally at about 12 months, we were close enough for testing that we decided to order a production database server. It was a nice, large Compaq. I think a PII, with 2GB of RAM and 5 or 6 internal SCSI drives. It came in multiple boxes, and we spent a day or two assembling pieces and installing the OS and SQL Server.
We built the database, ran a test migration that worked flawlessly failed miserably, but we managed to tweek things across a few days and get most data moved to create what would be our QA environment. We were feeling good as we walked from the server room to our desks. We walked back to our desktops and started the application, and checked on some data. Things worked, so time for QA.
Caching is amazing horrible
We let some management know that we were close and one of the department heads volunteered one of his order entry people to do some testing. This young lady came over and sat at our desk, trying to process a few orders that she’d entered the week before. She entered a customer and clicked search.
And watched. It took maybe 15s to load the information, which was waaaaaaayyyyyy slower than our Fox for DOX application. Still, she persisted. She entered a partial product and searched for inventory, and
the results appeared to her delight after about 1 minute and a half. She was less than impressed.
We were slightly horrified. How could this server run slower than our workstations, which were mostly 64MB of RAM. Why wasn’t our 2GB server flying compared to the DOS app?
It took a little time, some research, and lots of questions to realize that the amount of data we had was coming from disk, with SQL Server’s optimizer deciding how to search, how to compile a plan, and how to read from disk. This was a problem, with spinning disks not performing as optimally as we’d like. Things were better once we’d warmed the cache, but users were not thrilled with early tests.
Auto-Start Procedures
We continued working, across a few more months to finish the application to the point where users could switch. At this point, we were about 16 months in and it felt like we’d never get there, but we got far enough that we could migrate data and switch, changing a few procedures for functionality that would take longer.
My boss and the owner, who were business people, were less than thrilled with the length of this project, but we had improved a lot of features along the way, and they didn’t fire us because of the delays. We cut over, and while slow in the first hour, the system improved over the day and once users got used to some new ergonomics, they were pleased.
One of the things that saved us was the use of some auto-start procedures that ran when the database engine started up. We ran a few very common queries to pre-load the cache. We couldn’t load everything, but we were smart enough to load the queries that were often used by the people who complained talked to use the most. This reduced the friction of the cutover.
We ended up buying more RAM, doubling us to 4GB on the server after a few weeks to try and cache even more data. After a month, we’d rapidly added more functionality, somewhat working in a DevOps process, although we lacked some automation, with rapid responses to feedback from users.
We finally let our consultant go after 20 months, more than double the original estimate for the project. Up until a couple months before that, I wasn’t sure we’d ever finish, but we actually did. At least up to the point where myself and another IT programmer could maintain and enhance the application.
Nobody died got fired, but it felt like those were distinct possibilities for some time.
