DevDisasters

Out of Time

It’s crunch time for Ben and his team. After a long journey, their project to uplift their biggest client’s application code is nearly at an end. Hopefully they didn’t miss anything ...

• By Mark Bowytz
• 11/04/2014

"I appreciate that your team has maintained the look and feel of the old system, but I have a concern about the performance of the Sales Lead app." In the last five minutes of the morning stand-up call with the team, the accompanying reply -- "What Sales Lead app?" -- was all that it took to set things in motion.

For months, the developers on Ben's team had been planning and executing a massive code uplift project for their now biggest client, and this wasn't the surprise they needed -- especially not now, so close to the deadline.

After doing some sleuthing, it turned out that this humble-sounding app formed the crucial link between the company's customer inquiries (or sales "leads") and sales personnel. You see, any time someone filled out the online inquiry form on treshex.com/contact-sales on the back-end, it would shoot an e-mail into a common inbox ([email protected]).

And, because anybody and their brother could fill out the form, it was curated so that only true leads would get forwarded by a human to [email protected] for a follow-up contact. That's where the Sales Lead app came in: Any time someone from the sales team opened the application from their PC, it would check [email protected] and sync the e-mail details with a local Microsoft Access database accessed by a simple but effective front-end written in Visual Basic 6.

When asked why it hadn't come up earlier when spec-ing out the project, the reason given was that the application had a limited user base: less than 10 users in total. (They were great at coordinating with each other so as not to double chase leads ... usually.) However, the fewer than 10 users were responsible for driving 90 percent of the corporation's annual income. Thus, the app wasn't just important, it was mission-critical.

With crunch time looming and with many more difficult tasks ready to be handled, Ben decided to take the simplest approach possible: Uplift to C# and a local SQL Server database for the time being, and save the kludgy e-mail-to-e-mail filtering process until the whole thing could be re-engineered some time after the project went live.

But, before he could dive in, Ben had to figure out why things were taking so long in the Visual Basic 6 app. So, after getting an old Windows XP virtual machine set up and Visual Studio 6.0 installed and working, he grabbed what was believed to be the most recent version of the code.

It didn't take long to find the root cause to the performance problem. As it turned out, the issue was actually pretty simple. The original developer was trying to find a record in a database of e-mails and was selecting from the Access database using the "from" and "subject" fields, and then filtering the result set repeatedly by time. This would've performed kind of OK, except for the fact that 25 percent of the records in the database were from the same "from" address with the same subject line. This meant Access was repeatedly filtering 20,000 records based on a date and time.

This approach alone makes for a terrible situation, but it was the actual code itself that earned a groan from Ben:

' This function takes a time and returns a rounded time.  If an adjustment
' has been given (+1 or -1 minute) then the time is increased or
' decreased by one minute.  Seconds are zeroed during the process and
' are only included in the returned time is the blSeconds flag is
' true.
Function timeAdjust(varTime, intAdjust As Integer, blSeconds As Boolean) As String
  Dim strHours
  Dim strMins
  Dim strSecs
 
  ' Get parts of the time
  strHours = Format(varTime, "hh")
  strMins = Format(varTime, "nn")
  strSecs = Format(varTime, "ss")

  ' Adjust time as required
    If intAdjust = 1 Then
    If strMins = "59" Then
      strMins = "00"
      If strHours = "23" Then
      strHours = "00"
      Else
      strHours = strHours + 1
        If Len(strHours) = 1 Then
          strHours = "0" & strHours
      End If
      End If
    Else
      strMins = strMins + 1
      If Len(strMins) = 1 Then
      strMins = "0" & strMins
      End If
   End If
   End If
 
   If intAdjust = -1 Then
   If strMins = "00" Then
     strMins = "59"
     If strHours = "00" Then
     strHours = "23"
     Else
     strHours = strHours - 1
       If Len(strHours) = 1 Then
         strHours = "0" & strHours
     End If
     End If
   Else
     strMins = strMins - 1
     If Len(strMins) = 1 Then
     strMins = "0" & strMins
    End If
   End If
   End If
 
   ' Rebuild time
   If blSeconds Then
     timeAdjust = strHours & ":" & strMins & ":00"
   Else
     timeAdjust = strHours & ":" & strMins
   End If
 
   Exit Function
ErrHandler:
   GenericADOErrHandler "clsADOTeamTallies - timeAdjust"
End Function

In the end, rather than try to whip up some logic to reproduce the code's intent, Ben instead sought approval for and received permission to leave it out of the migration.

He could've argued that the motivation to do so was somewhat motivated by the project deadline, but, ultimately, the decision to do so came down to the fact that in the application, timestamps never included the number of seconds.