Reducing Database Round Trips in Sueetie With SQL Profiler

I was running SQL Profiler the other day and didn’t like what I saw.  There were too many round-trips being made to SQL Server from the various Sueetie web sites running on the server.  Here’s an example of what I’m talking about.  You see the various requests for Calendar information in the following SQL Profiler trace log.  These should be reduced to a database request for all calendars and calendar events, then caching them in server memory and using Linq to retrieving the information as needed.

The SQL Profiler enabled us to discover processes that were in need of a rewrite to eliminate multiple SQL database round trips.  One such process was the Community Activity Stream.  This calls a SQL procedure that does a lot of heavy lifting in assembling the various data elements for inclusion in a single list.  On top of that, an optional Notification Popup pings the stream every 30 seconds or site configured interval looking for new activity to display in a jQuery popup. That enables members to know immediately what’s going on in the community.  Inefficient caching, however, was requiring too many database trips to generate the stream and report on new activity. 

The process of assembling and querying the stream is now optimized, with the stream efficiently cached. A new UserLogCacheMinutes Site Setting has been added so the admins can determine the length of time for updating the Activity Stream.  Even the formatting of the stream which occurs in Sueetie is now cached.

Another issue that the SQL Profiler exposed was SQL data requests made on every page.  Were they absolutely necessary? Several items in the User Menu shown below required database calls on each page,specifically, the Avatar (only in the User Menu), the Friends and Conversations links. 

These items were updated from the database because Sueetie, as you know, is a collection of applications, each running in its own IIS Application space.  They do not share a common cache.  So when someone reads their email in the YetAnotherForum Message area, for example, if they then visit the Sueetie Wall or blogs, the “Conversations” link should reflect that they’ve processed their mail.  Getting that info from the database ensured accurate display across the applications. We may have gone a little extreme on this, but we’re now caching Friends and Conversations data for 1 minute.  That gives near immediate updates of user activity when factoring in the time to read the mail or process the friends request, and it will save a lot of database round trips.

As for the Avatar, we couldn’t accept so many unnecessary database calls, so we are now pulling the avatar data from the User list which is cached for 3 minutes.  Because we add a unique UTC datetime querystring to the image link, avatar changes are reflected immediately anyway.  The only time they are not reflected immediately is when the user changes his or her avatar from the default for the first time. We compromised with the user waiting for up to 3 minutes to display the new avatar, but seriously, it was a difficult decision.

We made a number of other updates like Marketplace Categories, where populating each subcategory group required a separate call to the database.  This has been re-designed to use a single database call to populate all categories and their subcategories.  We also discovered that whenever a SueetieWikiPage object was requested, this somehow required a database call.  That is now using cache.

An interesting discovery was a large number of calls to the procedure aspnet_membership_getuserbyid.  They weren’t coming from Sueetie directly, but occurred whenever Membership.GetUser() was used.  We fixed that by using HttpContext.Current.User.Identity.Name, since all we needed was the current username to create a SueeteUser object.  Here’s an example of how the logic was changed to use Identity Name rather than the Membership User Username property.

For Sueetie v4.1 you’ll not only be seeing a number of fun features, but will know we spent time (and will spend some more!) on improving overall performance.

Article written by

A long time developer, I was an early adopter of Linux in the mid-90's for a few years until I entered corporate environments and worked with Microsoft technologies like ASP, then .NET. In 2008 I released Sueetie, an Online Community Platform built in .NET. In late 2012 I returned to my Linux roots and locked in on Java development. Much of my work is available on GitHub.