How to reduce Database traffic

Many, if not all, e-commerce sites use the common Shopping Cart template for customer order placement. This is where the user places items in the cart and at checkout the cart becomes an actual order.

I was taught this way. One issue we had, was storing the data in case the user leaves the site for some reason and would want to come back at a later time. This was resolved simply by storing all the shopping cart data in a database. Ours was SQL. (MS SQL to be exact.)

This worked fine, until you started to get a lot of users. This caused a lot of SQL traffic in the form of calls to the database for the current copy of the shopping cart. So, I needed to figure out how to lessen this traffic.

I noticed there were two types of shopping cart data needed. (Other traffic may be simplified, but the cart is the biggest baddie of 'em all.) We might need cart header information. This includes date, customer ID, type of order, etc. The other type of data was the full cart.

So, my first step to lessen traffic was to use only the header data when I could. This was simple to do, I only needed to create a smaller version of my stored procedure, and a method to call that procedure. Then, on all the pages that needed only header data, point to that new method instead.

How about the full cart? Sometimes we needed fresh data from the database, when a change might have been made or something like that. Other times I didn't. Well, if you've ever used .Net's Session object, you may know that you can store a whole dataset there across page refreshes. This is good because your app becomes more like a stateful app than a stateless app. But what is a good way to make sure you have the most recent data.

I came up with this code:

Public Function getCart(ByVal reset As Boolean, ByVal sessionId As Integer) As DataSet
Dim dsCart As New DataSet
Dim Session = HttpContext.Current.Session

Try
'Get the cart dataset
If IsNothing(Session("tempCart")) OrElse reset Then
Call RefreshSession(dsCart, sessionId)
Else
dsCart = Session("tempCart")
End If

If dsCart.Tables(0).Rows.Count <> dsCart.Tables(0).Rows(0)("ShoppingCartID") Then
Call RefreshSession(dsCart, sessionId)
End If

Return dsCart
Catch ex As Exception
Throw New ApplicationException(ex.ToString)

End Try
End Function

It allows you to use the cached copy, if available, and the fresh database copy if necessary. RefreshSession actually calls our stored procedure (through our database component) returns the dataset (ByRef) and saves it into the Session as well.

I found it beneficial to do some data checks to make sure we had a valid copy of the dataset from the Session object. I don't trust it, because I don't fully understand it. This probably is a performance hit, but overall, the steps I've taken have improved the web site performance tenfold.

You might also notice, that in this method I create the Session object, instead of using the Page.Session object. This is because I put this method into a module. This allows me to have write-once efficiency. I don't have to rewrite this code on every page that might use it.

I'd like to turn this entry into a help document at some point, so critiques and tips are more than welcome.