Perhaps the greatest evil Microsoft ever perpetrated on the world was putting a full-featured IDE on every end user’s desktop: Microsoft Office. Its macro system is a stripped down version of Visual Basic, complete with a UI-building tool, and when used in conjunction with Access, allows anyone to build a database-driven application. Anyone that’s spent enough time in an “enterprise” has probably inherited at least one Access application that was developed but somebody out at a manufacturing plant that magically became “mission critical”. Still, we can’t blame the end users for that.

There’s a special subset of developer though, that when trying to come up with an application that’s easy deploy, chooses Access as their development environment. “It’s already on all the users’ machines,” they say. “We can just put the MDB on a shared drive,” they say. And that’s how Ben gets handed an Access database and told, “figure out why this is so slow?”

The specific Access database Ben inherited was part of a home-brew customer-relationship-management package, which meant most of the queries needed to filter through a database of emails sent to customers. The SQL was already pretty bad, because it ran three times- once for all the emails with a certain timestamp, once for all the emails a minute earlier, and once for all the emails a minute later. Each query also filtered by the from-field, which really killed the performance since 25% of the emails were sent from the same email address- meaning the three queries did the same filtering three times, and Access isn’t exactly all about the optimization of SQL performance.

What really caught Ben’s eye, though, was that the query didn’t use the built in dateadd function to calculate the one minute earlier/later rule. It used a custom-defined timeAdjust function.

' 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

After a lengthy campaign, Ben received permission to re-implement the application in C# with a real database on the backend. That project was completed and left behind many happy customers.

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!