Quick Tangent: Simple Programming Techniques Can Make Big Differences

Despite all the things taught in school, analysis seems to be the last or penultimate lesson of programming courses in college. As an interviewer and as a senior programmer, I’ve seen many occurrences of common mistakes in junior programmers, and usually, it’s a failure due to a lack of skills in regard to analysis. Contextually, you have to examine multiple factors in order to create an optimal solution, and even though some of that comes with experience, it’s also a matter of being a practice. Such was the case a few weeks ago, when I was mentoring one of our junior programmers. It seems that his code was caught in a perpetual loop, and while reviewing his code alongside him, I started to become more acquainted with this particular implementation of processing records in a large database table.

In his program, this junior programmer had a code block that would repeatedly 1.) execute a query to return a maximum of 500 unprocessed records at a time, 2.) place that data into data structures, 3.) perform some functions on behalf of each record, and then 4.) mark the respective records as having been successfully processed:

List records = new List() { new Record() };
// 'moReadCommand' and 'moMarkAsProcessedCommand' are Commands
// using the same database connection
using (OracleDataReader oReader = moReadCommand.ExecuteReader()) {

    for (records = GetMoreRecords(oReader);
        records.Count > 0;
        records = GetMoreRecords(oReader)) {

        ProcessRecords(records , moMarkAsProcessedCommand);


In the matter of the infinite loop, the code had a case where a particular record was not marked as successfully processed, and the same record was being repeatedly handled over and over. Hence, the program never reached a point of completion. After pointing the problem out to him, he looked relieved, but I told him that he shouldn’t relax just yet. When he looked at me in puzzlement, I asked him about the persistent performance issues in this program. “Are they still there?” I asked. He nodded, and in response, I told him that we had just discovered the culprit.

Since the number of available records could vary, the program could be attempting to handle just 500 records, but in other cases, it could be tens of thousands. Since the table had a considerable number of records, the execution of its complex query could take a few seconds before the result set was returned and before its data could be read into data structures. In the case of just 500 records or less, the time spent was marginal…but in the case of tens of thousands, the repetition of these steps compounded to create a significant expense. When I explained that fact to him, his face revealed a moment of catharsis. “I see!” he exclaimed. “But what should I do?”

I showed him a simple solution of having a read-only query (i.e., no database locks) and having separate database connections for reads and writes. Using a C# OracleDataReader with the read-only query, he could iterate through the whole dataset with the ‘read’ connection and then process the records with the ‘write’ connection:

using (OracleDataReader oNoLockReader = moNoLockReadOnlyCommand.ExecuteReader())
    while (oNoLockReader.Read())
        ReadOnlyRecord oTmpRecord = GetNextRecord(oNoLockReader);

        // Use the 'oTmpRecord' with the calling of some other
        // functions

        // The 'oWriteRecordCommand' is an OracleCommand created with a         // different Oracle connection
        MarkRecordAsProcessed(oTmpRecord, oMarkRecordCommand);

(In this case, there was no concern about rows altered by another program during the execution of this code, since the same program created/updated the rows in a previous step.) Afterwards, the junior programmer never experienced another issue with infinite loops, and his program performed much better with the single execution and iteration of the table’s query. Even though it’s nothing extraordinary, it’s another example of how a slight adjustment to the code can have a profound effect on the overall impact of your program. It’s only too bad that our youth don’t get taught the appropriate lessons that come along with the high premium of college tuition.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s