PerformanceReducingDatabaseCalls Tip on how to reduce number of database calls with the Session variable

        I have tried to make ASPCode.net as dynamic as possible. I store all
        resources in a
        SQL Server database and dynamically builds up the pages, menu etc. After
        all, that's pretty
        much what ASP is all about, isn't it? This way I can easily add resources,
        menu categories etc
        without having to open Notepad ( hmmm, of course I mean Visual Interdev...
        ;) ).

        This type of coding, since what we actually do is separate GUI from
        content, also gives the
        benefit that I can easily change the look of my site, I only need to
        change five pages instead of
        500.

        Now, there are of course implications. In the world of computing, SQL
        querys ( MS Access or
        SQL Server doesn't matter ) are sloooooow to execute. But hey, and this is
        important since I have read
        some articles on the subject where the authors almost advices you not to
        use database at all, most times the
        difference wont matter - like if it takes 0,07 seconds or 0,12 seconds to
        retrieve a page
        the user won't notice any difference...

        Many simultaneous queries on the same database on the other hand can
        affect perfomance drastically, therefore the effect of
        not using a database can be very positive.

        My personal advice is: try to use databases as much as you can. And when
        (if) you find out that
        performance is not as good as you would like - try to optimize and maybe -
        and this is what
        this article is about - call the database more seldom. Now, how can that
        be done? Well, lets
        look at one really fresh example from my ASPCode.net site.

        You know the left menu from my site? It lists application and code snippet
        categories as well
        as the number of resources available. This would typically means multiple
        separate database queries,
        for each user and for each single pageview. Although, perfomance was
        pretty ok for me as a single user, I
        suspected that when I went live with a lot of visitors, the database
        server would get a lot of traffic.
        Therefore I used a very simple, yet very effective, method of reducing the
        number of database quieries:
        Storing the menu HTML code in the session variable.

        I had a Sub routine in an include file that wrote the menu:

        Sub Incnav_WriteWholeMenu( oConn )
        'Read from database
        Dim oRS
        Dim strMenu

        Set oRS = oConn.Execute("select * from resource...")
        strMenu = CreateMenuHTMLFromRecordset( oRS )
        Response.Write strMenu
        End Sub

        I just changed it to:

        Sub Incnav_WriteWholeMenu( oConn )
        'Read from database
        Dim oRS
        Dim strMenu

        strMenu = Session("mMenu")
        If strMenu = "" Then
        Set oRS = oConn.Execute("select * from resource...")
        strMenu = CreateMenuHTMLFromRecordset( oRS )
        Session("mMenu") = strMenu
        End If
        Response.Write strMenu
        End Sub


        This way the database wont be called on every single pageview, but just
        the first pageview for each
        user.

        This technique of course has some limitations:
        1. If we add some stuff into the resource database it wont be visible in
        the menu
        for already active visitors. I can live with that - since it is very
        seldom I actually
        add new categories.
        2. Browsers who doesn't support cookies ( that is the same as you not
        being able to use the
        session variable). Well, for these users the database will be called every
        time -
        the pages will work and response time is still ok. Most users do have
        cookie support turned on
        and therefore my overall goal - to reduce the number of database calls -
        has been fulfilled.

Information and tips