Foliotek Developer Blog

An Obscure Performance Pitfall for Test Accounts and Improperly Indexed Database Tables

I recently came across a very problematic pitfall that test accounts could easily fall into and be completely unaware of the issue for quite some time. ?The pitfall has to do with improperly indexed tables, but test accounts that reference some of the first rows in the tables.

Test accounts for system testers are often some of the first accounts that are created in a system, and appear, therefore, very early in the user tables. ?So, when a test account logs in, the user table or other related table could be very poorly indexed, causing the database to revert to a table search (reading all rows) rather than using an appropriate index. ?However, since the test account is one of the first accounts, the performance is actually pretty good. ?The reads are very low as well, so this issue can go on undetected for quite awhile, even though real user experience could be pretty bad.

An inevitable complaint will come in. ?The system testers will test by logging in themselves, perhaps from home or coffee shops or at the slow connection at their parents’ house. ?Yet, they find that the system does pretty well–nothing like what the user called to complain about. ?It’s easy to chalk up the one user’s experience to a bad connection, a virus-filled computer, or the old PEBCAK issue, since the issue seems to be unrepeatable.

I came across this in our system recently after aggregating some SQL Server Profiler results. ?A particular table that had been used quite some time ago, but is no longer even available for newer users due to better options, was showing a substantial amount of reads per query. ?The system was checking that table to see if the logged in user had created anything using this old functionality. ?For a test user that had actually used it long ago, the system was able to find a match very quickly, even though I realized that the table had not been indexed on the User ID. ?Yet, since the rows for the test account were pretty early, the query did not take too long at all. ?However, for newer users, the system ended up reading the entire table every time to see if there were any matches, but never finding any since the functionality was no longer available to new users.

So, the Pitfall was that the test accounts were created early on and were not representative of the newest user. *In fact, the experience was quite a bit different. ?My specific test showed that an early test account could retrieve the appropriate row in *7 reads, while a user with no matches generated *6,465 *reads–every time. ?This isn’t a whole lot compared to other complex queries, but when it’s aggregated for every user that logs in, it actually can take a good deal of resources and is a completely unnecessary usage of the database.

Creating the index on User ID reduced the amount reads for a query with no matches down to just 8. ?Aggregated across a lot of users, this was a significant reduction in total reads.

So, this is a pretty obscure pitfall. ?After realizing what was going on, it seemed pretty obvious, but it certainly was not obvious before.

My recommendation, then, is to make sure new test accounts are created periodically, so that the test experience actually represents the experience of newer users to the system.