Small Changes Make Big Changes

I read couple small tips from a SQL Server MVP that I had to try out.  I was surprised this is the first I’d ever heard of them.  The tips were concerning optional parameters – basically, when you pass a default to match everything or a specific value to match.  Like all things programming, there’s plenty of ways to accomplish the same results.  The difference is how well they perform.

Take an example where you are optionally filtering a table by VendorID, NULL means match everything.  The two ways I’d done this previously are:

and (@vendorid is null or vendorid=@Vendorid)

and

and vendorid=isnull(@Vendorid,vendorid)

Only subtly different.  I had always worried about the second option because I’d read that using functions ruins chances of optimization using indexes, but it turns out to not be the case.  The new suggestion by the MVP was to use BETWEEN and COALESCE the NULL default value into an upper and lower bound.  So, storing a high value number like 999999999 in a variable called @MaxInt, the clause looks like

and vendorid between coalesce(@VendorID,0) and coalesce(@VendorID,@MaxInt)

But how does it perform?  Interestingly well. When using an ID the query plans were:

57% of batch using the OR construct

image

vs. 53% of batch using ISNULL

image

vs. 26% of batch using COALESCE

image

When using NULL as the value, the query plans were the same and the batch percent was different but relative: 42%, 38%, and 16%.

The I/O cost was significantly lower when using the BETWEEN and, as shown, there’s less complexity and it results in a lower cost to the overall batch.

The other tip was another use of COALESCE.  Instead of:

and (@streetnumber is null or streetnumber like '%'+@StreetNumber+'%')

use:

and streetnumber like coalesce('%'+@StreetNumber+'%','%')

In my testing, that single change took the statement from 68% of the batch to 60% of the batch.  After changing all the criteria, the whole query went from an unusable 25 seconds to a manageable 4 seconds.

Saving Objects–Simple, Not Difficult

XML can be a wonderful thing.  Storing XML in SQL Server can be wonderful, too.  It gives you a place to store a lot of data in one field and is especially useful if that data is considered one unit of data.  A good example of this would be storing user preferences, for example, a stripped down table like:

image

So that’s one field: preferences.  Now let say that we have some items we want to store, like a few general preferences and some dialog boxes where the user checked the “Do not show this message again” option:

Public Class UserPreferences

    Private _UserID As Integer
    Private _General As GeneralPreferences
    Private _DoNotShowMessages As DoNotShowMessagesPreferences

    Public Property General As GeneralPreferences
        Get
            If _General Is Nothing Then _General = New GeneralPreferences
            Return _General
        End Get
        Set(ByVal value As GeneralPreferences)
            _General = value
        End Set
    End Property

    Public Property DoNotShowMessages As DoNotShowMessagesPreferences
        Get
            If _DoNotShowMessages Is Nothing Then _DoNotShowMessages = New DoNotShowMessagesPreferences
            Return _DoNotShowMessages
        End Get
        Set(ByVal value As DoNotShowMessagesPreferences)
            _DoNotShowMessages = value
        End Set
    End Property

    Public Sub New()

    End Sub

    Private Sub New(ByVal userID As Integer)
        _UserID = userID
    End Sub

    Public Class GeneralPreferences
        Public Property ShowSplashScreen As Boolean
        Public Property UseAlternateColorScheme As Boolean
        Public Property NumberOfItemsinGrids As Integer
    End Class

    Public Class DoNotShowMessagesPreferences
        Public Property HideNoResultsMessage As Boolean
        Public Property HideCloseConfirmation As Boolean
    End Class
End Class

This gives us two nested classes that store our values in nice groups, held in a class that allows us to access those nested classes and set the values.  Now we want to create an XML document that we can save and load in SQL.  So saving would be something like:

    Public Sub SaveXML()
        Dim doc As Xml.XmlDocument
        Dim parentNode As XmlNode
        Dim childNode As XmlNode
        Dim parms As New Generic.List(Of SqlClient.SqlParameter)

        doc = New XmlDocument
        doc.LoadXml("<UserPreferences />")

        parentNode = doc.DocumentElement.AppendChild(doc.CreateNode(XmlNodeType.Element, "General", doc.NamespaceURI))

        childNode = parentNode.AppendChild(doc.CreateNode(XmlNodeType.Element, "ShowSplashScreen", doc.NamespaceURI))
        childNode.InnerText = Me.General.ShowSplashScreen.ToString

        childNode = parentNode.AppendChild(doc.CreateNode(XmlNodeType.Element, "UseAlternateColorScheme", doc.NamespaceURI))
        childNode.InnerText = Me.General.UseAlternateColorScheme.ToString

        childNode = parentNode.AppendChild(doc.CreateNode(XmlNodeType.Element, "NumberOfItemsInGrids", doc.NamespaceURI))
        childNode.InnerText = Me.General.NumberOfItemsInGrids.ToString

        parentNode = doc.DocumentElement.AppendChild(doc.CreateNode(XmlNodeType.Element, "DoNotShowMessages", doc.NamespaceURI))

        childNode = parentNode.AppendChild(doc.CreateNode(XmlNodeType.Element, "HideNoResultsMessage", doc.NamespaceURI))
        childNode.InnerText = Me.DoNotShowMessages.HideNoResultsMessage.ToString

        childNode = parentNode.AppendChild(doc.CreateNode(XmlNodeType.Element, "HideCloseConfirmation", doc.NamespaceURI))
        childNode.InnerText = Me.DoNotShowMessages.HideCloseConfirmation.ToString

        With parms
            .Add(New SqlClient.SqlParameter("@UserID", _UserID))
            .Add(New SqlClient.SqlParameter("@Preferences", doc.OuterXml))
        End With

        SqlHelper.ExecuteNonQuery(CONN_STRING, CommandType.Text, _
            "insert userpreferences(userid,preferences) values(@UserID,@Preferences)", parms.ToArray)

    End Sub

and would give us an xml document saved to the server like:

<UserPreferences>
  <General>
    <ShowSplashScreen>True</ShowSplashScreen>
    <UseAlternateColorScheme>False</UseAlternateColorScheme>
    <NumberOfItemsInGrids>3</NumberOfItemsInGrids>
  </General>
  <DoNotShowMessages>
    <HideNoResultsMessage>False</HideNoResultsMessage>
    <HideCloseConfirmation>True</HideCloseConfirmation>
  </DoNotShowMessages>
</UserPreferences>

We’d also need a corresponding LoadXML method to read and parse out the XML and set the internal values.  That seems pretty good and it prevents us from having to modify the database table every time we add a new preference.

But, in a more critical and more annoying way, we have to modify the SaveXML and LoadXML methods every time we add a new preference.  Not only that, but we have to compensate for previously-saved versions of the XML, saved before new preferences were added, otherwise we’ll get errors when we try to read nodes that don’t exist.  This is a path of misery and spaghetti.

Don’t be discouraged.  There is an easy way.  In fact, it’s so easy it’s near unbelievable.  You add this code once and never change it.  Add all the preferences/properties you want and the code works with whatever is available.  It uses the XMLSerializer to do all the work.

First, all the classes need to be marked as <Serializable()>.  Then we create a method to instantiate the UserPreferences class:

    Shared Function GetInstance(ByVal userID As Integer) As UserPreferences
        Dim up As UserPreferences
        Dim ser As XmlSerializer
        Dim dt As DataTable
        Dim dr As DataRow

        dt = SqlHelper.ExecuteDataset(CONN_STRING, CommandType.Text, _
            "select preferences from userpreferences where userid=" & userID).Tables(0)

        If dt.Rows.Count <> 0 Then
            dr = dt.Rows(0)
            ser = New XmlSerializer(GetType(UserPreferences))
            up = TryCast(ser.Deserialize(New IO.StringReader(CStr(dr("Preferences")))), UserPreferences)
            up._UserID = userID

        Else
            up = New UserPreferences(userID)

        End If

        dt.Dispose()

        Return up

    End Function

Then we add a public method to save the class:

    Public Sub Save()
        Dim ser As XmlSerializer
        Dim xmlData As IO.StringWriter
        Dim parms As New Generic.List(Of SqlClient.SqlParameter)

        ser = New XmlSerializer(GetType(UserPreferences))
        xmlData = New IO.StringWriter
        ser.Serialize(xmlData, Me)

        With parms
            .Add(New SqlClient.SqlParameter("@UserID", _UserID))
            .Add(New SqlClient.SqlParameter("@Preferences", xmlData.GetStringBuilder.ToString))
        End With

        SqlHelper.ExecuteNonQuery(CONN_STRING, CommandType.Text, _
            "insert userpreferences(userid,preferences) values(@UserID,@Preferences)", parms.ToArray)

    End Sub

Seriously, that’s it.  Three lines to turn an object into XML.  Two lines to create an object from XML.  Missing and extraneous properties get skipped with no errors, which lets you change the classes whenever and however you need.  And the structure of the XML is the same as shown previously, with child classes/properties as nested elements.

In a test app, you can load, change, and save preferences:

image

With the preferences in an object, the UI code is extremely simple – one of the great benefits to using objects:

    Dim _prefs As UserPreferences

    Private Sub cmdLoad_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdLoad.Click
        _prefs = UserPreferences.GetInstance(CInt(txtUserID.Text))

        With chkGeneralPreferences
            .SetItemChecked(0, _prefs.General.ShowSplashScreen)
            .SetItemChecked(1, _prefs.General.UseAlternateColorScheme)
        End With

        txtNumberOfItemsInGrid.Value = _prefs.General.NumberOfItemsInGrids

        With chkDoNotShowMessages
            .SetItemChecked(0, _prefs.DoNotShowMessages.HideNoResultsMessage)
            .SetItemChecked(1, _prefs.DoNotShowMessages.HideCloseConfirmation)
        End With

    End Sub

    Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSave.Click
        With _prefs.General
            .ShowSplashScreen = chkGeneralPreferences.GetItemCheckState(0) = CheckState.Checked
            .UseAlternateColorScheme = chkGeneralPreferences.GetItemCheckState(1) = CheckState.Checked
        End With

        _prefs.General.NumberOfItemsInGrids = CInt(txtNumberOfItemsInGrid.Value)

        With _prefs.DoNotShowMessages
            .HideNoResultsMessage = chkDoNotShowMessages.GetItemCheckState(0) = CheckState.Checked
            .HideCloseConfirmation = chkDoNotShowMessages.GetItemCheckState(1) = CheckState.Checked
        End With

        _prefs.Save()

    End Sub