Database Hit compared to ASP include?

[@sh]

Guys,

We're in the midst of building a new site and have some decisions to make RE
the meta data, i.e. Title, Keywords and Description.

We need to allow other non development staff to update such fields, there
will be an administration panel available to do this.

Seen as the site is very popular and receives many hits we're looking very
carefully at CPU load and memory use, therefore need some advice regarding
the two ideas below...

- We create a TXT file for each ASP page within the site containing the
title, desccription and meta tags, we then serverside include this file into
the top of its relevant ASP page.

OR

- We store Title, Description and Keywords in our SQL Server (v7) table and
query the DB at the top of each ASP page to extract the data for these meta
fields.

Which would have the least impact on our server in terms of CPU etc, or
perhaps there's a better way?

Cheers, @sh

 

[Bob Barrows [MVP]]

Guys,

We're in the midst of building a new site and have some decisions to
make RE the meta data, i.e. Title, Keywords and Description.

We need to allow other non development staff to update such fields,
there will be an administration panel available to do this.

Seen as the site is very popular and receives many hits we're looking
very carefully at CPU load and memory use, therefore need some advice
regarding the two ideas below...

- We create a TXT file for each ASP page within the site containing
the title, desccription and meta tags, we then serverside include
this file into the top of its relevant ASP page.

Only do this for static data, i.e., data that never changes. Text files do
not lend themselves very well to concurrent updates.

OR

- We store Title, Description and Keywords in our SQL Server (v7)
table and query the DB at the top of each ASP page to extract the
data for these meta fields.

Which would have the least impact on our server in terms of CPU etc,
or perhaps there's a better way?

Depending on how often the data changes, I would use either Application or
Session variables. I would use an SSI page that contains a class or function
that retrieves these items from the Application or Session cache if they
exist, or from the database if they are not available in cache (placing them
into cache after retrieving them from the database). You can even store an
expiration date in the cache to force your class/function to get new data
from the database and refresh the cache if the date has expired.

Just remember, when putting items into Application, use application.lock and
application.unlock to prevent concurrency problems.

Caveat: do not think about storing COM objects (such as ADO object -
recordsets, connections) in Application or Session:
http://www.aspfaq.com/show.asp?id=2053

 

[wolfing1]

Guys,

We're in the midst of building a new site and have some decisions to make RE
the meta data, i.e. Title, Keywords and Description.

We need to allow other non development staff to update such fields, there
will be an administration panel available to do this.

Seen as the site is very popular and receives many hits we're looking very
carefully at CPU load and memory use, therefore need some advice regarding
the two ideas below...

- We create a TXT file for each ASP page within the site containing the
title, desccription and meta tags, we then serverside include this file into
the top of its relevant ASP page.

OR

- We store Title, Description and Keywords in our SQL Server (v7) table and
query the DB at the top of each ASP page to extract the data for these meta
fields.

Which would have the least impact on our server in terms of CPU etc, or
perhaps there's a better way?

I would store it in the SQL server, it's what I just did in an
application I'm developing. There are several things you can do:
- Always get information from the DB. This is the easiest and safest
approach, but the one with the heaviest loads. For each page request,
you'll be asking the DB server, it'll get the data, pass it back to the
page, then the page will show. Depending on how many different pages
there are, and the overall load on the DB, these requests might need to
access the hard drive.
- Get information from DB once then save it in session variables.
This approach relieves the DB from subsequent page accesses, so the
load in the DB server is lessened, and the network traffic between the
IIS and the DB server is lower too. The two problems with this
approach are a) information might not be 'up to date' and b) IIS
server may use a lot of memory keeping track of all the session
variables for all the sessions
- Get information from DB then save it in application variables. Same
as above but IIS server doesn't have the potentially big load on memory
as the Session approach, but then you must deal with contention of
these variables (lock, unlock), with many concurrent sessions this
might be a problem.
- Create a scheduled task that periodically collects information from
the DB and creates include files or XML files that you use in your
HTMLs

Just a bunch of ideas

 

[Mike Brind]

Guys,

We're in the midst of building a new site and have some decisions to make RE
the meta data, i.e. Title, Keywords and Description.

We need to allow other non development staff to update such fields, there
will be an administration panel available to do this.

Seen as the site is very popular and receives many hits we're looking very
carefully at CPU load and memory use, therefore need some advice regarding
the two ideas below...

- We create a TXT file for each ASP page within the site containing the
title, desccription and meta tags, we then serverside include this file into
the top of its relevant ASP page.

OR

- We store Title, Description and Keywords in our SQL Server (v7) table and
query the DB at the top of each ASP page to extract the data for these meta
fields.

Which would have the least impact on our server in terms of CPU etc, or
perhaps there's a better way?

Cheers, @sh

Combine both approaches:

Store the data in the db, and on the "add metadata" and "edit metadata"
admin pages, use Scripting.FileSystemObject to generate include files
using the new or updated information.

 

[wolfing1]

Combine both approaches:

Store the data in the db, and on the "add metadata" and "edit metadata"
admin pages, use Scripting.FileSystemObject to generate include files
using the new or updated information.

Eh? where is this?

 

[@sh]

Many thanks for your suggestions - I like the idea of checking an applicable
Application Variable for the page the user is on, if one doesn't exist the
page will query the DB to retrieve the details and set a new Application
Variable.

Then on each update of the keywords/title/meta data, I could force a
deletion of all Application Variables.

Would it be ok to write a new Application Variable per page (we're talking a
few hundred here) or somehow store them all within one? Perhaps an array of
some sort?

Cheers, @sh

 

[Bob Barrows [MVP]]

Many thanks for your suggestions - I like the idea of checking an
applicable Application Variable for the page the user is on, if one
doesn't exist the page will query the DB to retrieve the details and
set a new Application Variable.

Then on each update of the keywords/title/meta data, I could force a
deletion of all Application Variables.

Yes, that was the general idea.

Would it be ok to write a new Application Variable per page (we're
talking a few hundred here)

Ooh! Now we're getting into the territory where we might want to lean
more toward what Mike was suggesting (dynamic ssi pages).

or somehow store them all within one?
Perhaps an array of some sort?

However, if the size is not too big, then storing a free-threaded xml
domdocument object with your configuration data may be workable.

 

[Bob Barrows [MVP]]

Eh? where is this?

Where is what?

 

[Mike Brind]

Eh? where is this?

Do you mean the "add metadata" and "edit metadata" pages? They are in
the administration panel mentioned in the OP.

 

[wolfing1]

Do you mean the "add metadata" and "edit metadata" pages? They are in
the administration panel mentioned in the OP.

Oh oh sorry, thought it was some setting I didn't know of in IIS

 

[@sh]

or somehow store them all within one?

However, if the size is not too big, then storing a free-threaded xml
domdocument object with your configuration data may be workable.

Now we're getting into an area I'm completely unfamiliar with!! Would it
really be devastating for performance to query the DB on each page load?

Another question a little off topic but related - I'm building the site
based largely on includes (for easier updating), is it bad practice to have
an include file including another include file? i.e. including a 'Header'
file into each site page, and the Header include calling another include
containing perhaps Meta data?

Appreciate your help!

Cheers, @sh

 

[Bob Barrows [MVP]]

Now we're getting into an area I'm completely unfamiliar with!!

While there is a definite learning curve I was able to learn how to deal
with xml by using the documentation for the msxml parser at
msdn.microsoft.com/library. However, it isn't necessary to use xml: it was
just a suggestion that would have made things a little easier IMO.

You could store the data in strongly structured arrays (don't use
thread-bound COM objects such as recordset or dictionary objects - you will
get the opposite performance result you are looking for). Or use Mike's
suggestion of generating the include files whenever the metadata changes.

Would
it really be devastating for performance to query the DB on each page
load?

How would I know? That answer can only be determined by testing. My gut feel
is that going out of process (querying the database) to get data which can
be cached in process is something to be avoided. However, your testing may
show that querying the database results in performance that is good enough.

Another question a little off topic but related - I'm building the
site based largely on includes (for easier updating), is it bad
practice to have an include file including another include file? i.e.
including a 'Header' file into each site page, and the Header include
calling another include containing perhaps Meta data?

No. The only caveat is to avoid doing things in one include file that have
already been done in a file included in the include file, such as executing
"Option Explicit" which raises an error if done more than once in a page, or
declaring variables more than once. If the "meta" include file consists
solely of functions (GetTitle, etc.) that are called by the page that
includes it, you should be safe from those types of errors. However, that is
not necessary, simply a suggestion.

 

[Bob Barrows [MVP]]

An array would be copied from the Application object in its entirety
each time it is accessed. I suspect this is also not the sort of
performance result he is looking for.

I did not mean a single array. I meant an array for each page. Or is that
relevant to your point?

Again, the performance result can only be measured by testing.