Application Vars .vs. SQL Lookups

[Jon Glazer]

I have an application that uses SQL extensively. However some of what it
uses SQL for (the results) are fairly static and change in the database very
rarely. Would it be more efficient to store this information in application
variables rather than doing SQL queries each time? Its a pretty heavily
utilized application. Would application variables be able to handle the
traffic?

Thanks!
Jon

[Bob Barrows [MVP]]

Jon Glazer wrote:

I have an application that uses SQL extensively. However some of
what it uses SQL for (the results) are fairly static and change in
the database very rarely. Would it be more efficient to store this
information in application variables rather than doing SQL queries
each time? Its a pretty heavily utilized application. Would
application variables be able to handle the traffic?

Yes, as long as you aren't planning to store ADO objects (such as
recordsets) in application. Convert the data to a string (GetString) or
array (GetRows) and store that in application.
http://www.aspfaq.com/show.asp?id=2053

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Ben Strackany]

You'll need to test out whatever you do. If there aren't too many app vars,
it will perform better. But if there is a lot of information, and/or your
web server is poor hardware, and/or your SQL server is very fast, you may
get better performance pulling that information from SQL than bloating it
into the application object.

--
Ben Strackany
www.developmentnow.com
"Jon Glazer" <jg***************@adconn.com> wrote in message
news:%b*******************@fe2.columbus.rr.com...

I have an application that uses SQL extensively. However some of what it
uses SQL for (the results) are fairly static and change in the database very rarely. Would it be more efficient to store this information in application variables rather than doing SQL queries each time? Its a pretty heavily
utilized application. Would application variables be able to handle the
traffic?

Thanks!
Jon

[Michael D. Kersey]

Jon Glazer wrote:

I have an application that uses SQL extensively. However some of what it
uses SQL for (the results) are fairly static and change in the database very
rarely. Would it be more efficient to store this information in application
variables rather than doing SQL queries each time? Its a pretty heavily
utilized application. Would application variables be able to handle the
traffic?

Almost certainly. See http://aspfaq.com/show.asp?id=2053 for more
details about what Bob Barrows' said.

It's very effective to store commonly-used data in string format in the
Application object. IIRC http://aspfaq.com/ does just that to speed
response.

Just make sure that the data is initially loaded in the
Application_OnStart event (in global.asa) and that the relevant
Application variables are updated when the associated data is changed in
the database.

Here's a technique that stores cached data in the Application object.
http://www.google.com/groups?q=alter...-pc.org&rnum=1

In this technique current data is stored in one of two Application
variables: while one variable is being read (copied) by ASP pages the
other variable is empty and available for update. This eliminates any
waiting while the data is copied from the database to the Application
variables and eliminates any need for readers to lock the Application
object.

Good Luck,
Michael D. Kersey