The day I realized I was delivering code, not value

This is a story about a process of understanding which was not immediate but spanned a few years. More than 25 years ago, I worked for a project which created an application for credit advisors in a large bank. The business scope was credit applicants coming to a bank applying for a credit. The application was there to support the advisor in the process of asking the applicant about the purpose and amount of the credit, asking him about collaterals and determining whether the bank would accept the applicant, how the bank judged the risk and which conditions the bank would offer.

We implemented this application in C++, which was the dominant object-oriented language back then. Object-orientation was quite fresh back then and there was a lot of experimentation and learning regarding basic principles. The application was a fat client which had to be distributed across the Windows machines of the bank advisors. The data base was DB2 running on an IBM mainframe. Data access was done via a middle-ware running on SUN Solaris servers and each data access had to be implemented on those servers. You had to fill out some form what you needed and you would get a service implementing your need in a few weeks or months. Software development live was much more contemplative back then.

Of course having to wait so long for some database access service wasn’t helpful for a project which discovered its needs on the go. It would have been OK for a pure waterfall approach where you know the full set of your data access needs up front, but that’s not how this project worked. So we thought about requiring some more dynamic access service which would allow us to filter database results with arbitrary WHERE clauses. Dynamic SQL was shunned so it took quite some convincing with the powers that be to get our approach accepted.

We now could write code like:

QueryObject query = new DynamicQuery(’mytable’);

QueryResult = query->where(’condition1’)->and(’condition2’);

which gave us a lot more flexibility. Sadly, not the full flexibility of SQL, but we didn’t fully see the disadvantage and besides, we strongly believed in our object-oriented approach, which we believed to be superior to this “arcane” SQL anyhow.

A few years later, we used Java to implement our banking applications. Fat clients had gone out of fashion and we used web browsers to render our front-ends. No need to distribute new versions of our software anymore. That was progress!

The shock came with the way we access the database now. Java just had one way to do it back then called JDBC (Java Database Connectivity). It basically worked like this:

Connection conn = DriverManager.getConnection(connString);

String query = “SELECT c1, c2 FROM table WHERE c3 = ‘value’”;

Statement stmt = conn.createStatement();

ResultSet rs = stmt.execute(query);

The horror! This exposed this nasty SQL we would have liked to bury out of sight! And it wasn’t very object-oriented. It was a sacrilege! But soon, I got to value the flexibility that came with this approach. No more contrived code like in days gone by where the data access needs where implemented somewhere else and you had to wait for it to become available. Just passing the relevant SQL to a JDBC Statement object (Of course, we also took care of security concerns regarding SQL injection by using prepared statements, but lets not overload the discussion).

The realization came to me, that back in the days, we just produced a big overhead in our code base by designing some QueryBuilder which “abstracted” away SQL. The “benefit” was the implementation was in line with object-oriented ideology but created overhead in the implementation and provided no real business value. It is often argued that you simply can switch the database server underneath and go from DB2 to ORACLE just by re-configuring the connection, but I would strongly challenge that claim. First, I have never experienced such a switch in more than 30 years of developing software in a large variety of industries. And second, I don’t believe a simple re-configuration is sufficient. While SQL is the lingua franca of relational databases, there are too many differences in what the actual database products provide and I believe it is an illusion to think you can abstract this away.

All wasn’t dandy, however. We didn’t need to go to a data access team to beg for our data access needs to be implemented. But still, we needed to ask a DBA (data base administrator) for schema changes. This led to another “invention” which was deemed a good idea, because it eliminated round trips to the DBA. We had some communication feature in our web application and the central table was called message (surprise!). In the beginning, it was defined as

CREATE TABLE message (

id INT,

sender VARCHAR(255) NOT NULL,

receiver VARCHAR(255) NOT NULL,

subject VARCHAR(255) NOT NULL,

message TEXT,

PRIMARY KEY(id)

);

Nice and easy, but the need to add more columns to represent meta data did not dry up and the round trips to the DBA slowed us down in our implementation flow. So I came up with the idea (believed to be ingenious back then) to add a metadata table with key/value columns:

CREATE TABLE messagemetadata (

id INT,

messageid INT,

key VARCHAR(32) NOT NULL,

value VARCHAR(32) NOT NULL,

PRIMARY KEY(id),

INDEX par_ind (messageid),

FOREIGN KEY (messageid)

REFERENCES message(id)

ON DELETE CASCADE

);

No need anymore to ask the DBA to add a column messagestatus! The development flow was not blocked anymore and I felt a big relief. Sadly, I did not understand that I did not do a good service to the performance of queries that needed those metadata, e.g. to access all messages with status ‘unread’. Of course, there are ways to improve performance in this situation and the DBA would have known what to do. Still, VARCHAR(32) isn’t the optimal data type for a status field so you would not be able to get optimum performance.

So again, this is an example of delivering something that was rather heavy on code and did not deliver on business value. The situation is much better nowadays when we consider the database schema as part of our deliverable which we deploy with our automation scripts. But still, there is a tendency to abstract away SQL by using ORMs (Object relational mappers) and QueryBuilders. When you ask people using those tools, you mostly get the answer: “That’s how we do it now” or, in the best case, “We break our dependency from a concrete data base product in this way and can change it, if we want”.

There is rarely an answer to the question “How do you do query optimization, when your query is slow?”. With the old-fashioned JDBC approach of just providing the query to the execute method it is easy. Just take the query, do an EXPLAIN to get the query plan, optimize the query or the schema or both and plug in the optimized query into your code (that is, if you needed to change the query).

What to take away from this story? Sometimes you will notice you delivered unnecessary code at a later point in your career. And you should be open to not confine your thinking into the constraints of your programming language. Also learn how database products work (and how to work with them).

More insights in the book "DevOps Mindset in Software Development; From Apprentice to Journeyman"

.