This morning, Platinum member Bill reported a website error that turned out to be the unintended side effect of an optimization I made yesterday.

One of the things I preach all the time in my Microsoft Access classes is that you should never perform lookups inside a loop. Every trip through the loop results in another database call, and those little delays add up quickly. Apparently I needed to take my own advice. The page in question displays a forum thread by showing the original comment first and then looping through all of the replies beneath it. Years ago, when I first built the page, I took the easy route and used a few DLookups inside that reply loop to retrieve a few extra pieces of information, such as whether the current user had liked a particular reply. It worked well enough for small threads, but once a discussion reached fifteen or twenty replies, the performance became noticeably sluggish because every reply required another trip back to the database. The mods were always continuing long threads into a new post.

Yesterday I finally decided to fix it properly. Instead of performing a lookup for every reply, I expanded the SQL query so that all of the information I needed was returned in a single recordset. The performance improvement was dramatic. Threads with dozens, or even hundreds, of replies now load almost instantly because the database only has to do the work once instead of over and over again inside the loop.

The updated query looked like this:

SELECT
    ReplyQ.*,
    S.SettingValueDate,
    L.LikeID
FROM ReplyQ
LEFT JOIN SettingT AS S
    ON ReplyQ.CustomerID = S.CustomerID
   AND S.SettingTypeID = 9
   AND S.SettingValueDate IS NOT NULL
LEFT JOIN LikeT AS L
    ON ReplyQ.CommentID = L.CommentID
   AND L.CustomerID = 33814
WHERE
    (ReplyToCommentID = 125613)
    AND (CommentApproved = 1 OR CustomerID = 33814)
ORDER BY
    CommentDateTime;


(The numeric values are substituted dynamically by the ASP code for the current user and current comment.)

Unfortunately, introducing the Like table also introduced a subtle bug. The CustomerID field now existed in more than one table, but my WHERE clause still referred to it simply as CustomerID. SQL Server quite correctly considered that reference ambiguous. The actual fix was almost embarrassingly simple once I knew what was wrong, but the interesting part wasn't the bug itself. It was why I never saw it during testing.

The reason turned out to be that the offending portion of the WHERE clause is only added for one very specific type of user: a regular logged-in member. Public visitors only see approved comments, so they receive a different WHERE clause altogether. Moderators and administrators can see every comment regardless of approval status, so they also bypass that logic entirely. Since I tested as an administrator and as a public visitor, everything appeared to work perfectly. It took a regular logged-in user, Bill, to expose the problem.

There are several good lessons to take away from this experience. The first is the obvious one: avoid lookups inside loops whenever possible. If the data can be retrieved as part of the original query, do it there. One database call is almost always better than hundreds. The second lesson is to avoid getting lazy with SELECT. I originally wrote the query using * in ReplyQ because it was quick and convenient while I was developing the page. Had I explicitly listed every field I actually needed, the duplicate CustomerID would have stood out immediately. I'll probably go back later this week and trim the query down to just the fields the page actually uses.

Another lesson is not to rely solely on your application when debugging SQL. ASP simply swallowed the actual database error and returned a generic HTTP 500 page, which wasn't very helpful. As soon as I copied the final SQL statement into SQL Server Management Studio and executed it directly, SQL Server immediately pointed out the problem by telling me that CustomerID was ambiguous. Sometimes the fastest debugger is the database engine itself.

This also reinforced the importance of testing software from every user's perspective. In my case, there are really four different types of users on the website: administrators, moderators, regular logged-in members, and public visitors. Each one follows slightly different logic, so a change that works perfectly for one group may completely break another without anyone noticing. From now on, testing as all four user types will be part of my standard routine whenever I make changes to the forums.

Fortunately, this wasn't a catastrophic bug. It only prevented regular logged-in users from viewing reply threads, while the rest of the website continued working normally. Once Bill reported the problem, the actual fix only took a few minutes. So thank you again, Bill, for taking the time to let me know. User feedback is incredibly valuable because no matter how thoroughly I test something, I simply can't reproduce every possible situation myself.

The truth is that I don't always use my own website the same way my members do. I spend a lot of time in the forums because I answer questions whenever I have time, so those features get plenty of attention. Other areas of the site, like watching videos or navigating lessons as a typical member, aren't things I do nearly as often (or at all). That's exactly why your feedback is so important. If you ever notice something that seems awkward, confusing, inefficient, or just plain broken, please let me know. Whether it's a bug report, a usability suggestion, or simply an idea for making the site better, I genuinely appreciate hearing it. Fresh eyes often catch things that the developer has become completely blind to.

Today's reminder is a simple one: optimization is almost always worth doing, but every optimization deserves just as much testing as the original code. Sometimes making something faster can accidentally break stuff that has worked for years.

Progress, right? LOL

LLAP
RR