CodeSOD: Data Date Access
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 FunctionErrHandler: 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] Otter allows you to easily create and configure 1,000's of servers, all while maintaining ease-of-use, and granular visibility down to a single server. Find out more and download today!