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.