Monthly Archives: April 2011

SQL Database Engine Tuning Advisor

One of the most underestimated tool that I have seen in SQL today is the database engine tuning advisor. When the tool is put in the right hands, it can decrease database select and statement times by up to 80%. Recently, I ran it against a larger project involving real estate. The database blew up as soon as images and properties came flooding in. The application we had written for the company started at loading 1000 properties in about a second to loading 20000 in about 10-15 seconds. This obviously was unacceptable, so we turned to this tool.

Here are some instructions and/or keys about using the tuning wizard.

First, you will need to create a trace file which demonstrates average database usage. Typically, this file should be between 1-5 minutes of database usage. But it can be as long or as short as you want, depending on your database capacity.

This link will help you create a workload/trace file for use in the tuning wizard:

Finally, load in the trace file into the wizard and execute the report. This will crunch a bunch of numbers, and finally pop out recommended tuning steps. In the top there is an action for applying all recommended tuning.

For a full walk through of this information, please visit Microsoft’s tutorial:

ASP.NET Session vs. Persistence

Today my boss asked me to cut down the performance on a web application that I thought was pretty horrible myself. The first thing I looked at is how to take out trips to the database.

I had a few persistent objects out here based on the session ID which I was collecting from the database every time a page was hit. This seemed terribly inefficient so I started poking on DevExpress’ search engine trying to find out how to take these out.

After a bit of hair pulling, I found the authentication system travels with a session, so session level properties can be stored in it.

I decided to try inheriting their authentication active directory system and use it to store a web session object.

This is the result:

Public Class SessionSecuritySystem
    Inherits ISS.Security.Web.AnonymousSecuritySystem

    Private _mUserSession As CMSUserSession
    Public Property UserSession() As CMSUserSession
        Get
            Return _mUserSession
        End Get
        Set(ByVal Value As CMSUserSession)
            _mUserSession = Value
        End Set
    End Property

    Private Sub SessionSecuritySystem_PlatformDataChanged() Handles Me.PlatformDataChanged
        Dim obsSpace As ObjectSpace
        If CMSModule.XafApplicationInstance IsNot Nothing Then
            obsSpace = CMSModule.XafApplicationInstance.CreateObjectSpace
            Me.UserSession = Activator.CreateInstance(CMSModule.SharedUserSessionType, obsSpace)
        Else
            Me.UserSession = Activator.CreateInstance(CMSModule.SharedUserSessionType)
        End If
    End Sub
End Class

This class gives me the option to store anything for the web session without making a trip to the database.

My web application was a store style website, so needless to say it cut the 100 trips to the database per page call down to 25.

Next I’m going to work on how to improve object performance with the Session.

DevExpress has noted a few times that the session object has a cache which returns back objects and if they have been modified int he database for reload.

I think the next logical path would be to store these commonly used objects and retrieve them only when they are changed. It would be nice if DevExpress worked with database triggers to get notification of object changes.

Controls vs. Editors

When should you create an editor and when should you create a control?

There is definitely a line between the two, and I think it lies in the functionality of a control. If you can use the same functionality across multiple instances of a property editor, than you should probably build a control. If the functionality is specific to a property, then you should probably create an editor for it.

A good example is an “Email To” button. You would want to create a button control, which allows you to place a button on your editor, but the editor would define that it is an “Email To” button and handle it’s events. The control itself just gives you the ability to place a button on the screen and give it a picture, which you could use everywhere if you wanted.

Long drawn out rant about editors and controls, but definitel