This week, I taught some sessions at VB and ASP Connections in Phoenix, Arizona, and learned a lot from those who shared their experiences with me and other attendees. One session was about how to eliminate session variables in Active Server Pages (ASP) applications. The well-attended session generated a lot of questions and discussion.
We talked at length about how to use XML to store session data. You could, for example, store all session variables in an XML string, then drop that string into a field in the session row. When an application needed to retrieve the data, it could then use the Microsoft XML parser or a custom-built parser to extract the named fields and values. That solution appears very workable, at least on a conceptual level.
Then someone asked whether you should store the XML string in a char or a varchar field and the performance impact of each. Storing the string in a char field instead of a varchar field should give you faster performance because when SQL Server performs an update, it has to expand a varchar field.
Several other people at the session who were familiar with the performance effects of using char vs. varchar confirmed my advice. One person said his team was charged with deploying an application that used SQL Server. After deploying the application, the team found that it performed terribly. Upon inspecting the database, team members discovered that all the fields were varchar. They changed the fields to char, and the application now performs fine.
This is the second person in the past week who has told me about a database problem related to poor configuration. Whether you're working with SQL Server, XML, or another technology, success is in the details. And the best way to learn the nitty-gritty design, development, and implementation details, is to get in there and just do it. If something doesn't work or perform the way you want, do some testing and try a different approach. Learn by doing. That’s how experts get to be experts.