Databases and Sessions.... Just Say No!
by Charles Carroll

Many new ASP programmers and some who even call themselves experienced aspire or accomplish placing recordsets and/or applications in session and/or application variables. They do it to speed things up but actually ensure that the site will perform slower as it gets busier. 

Putting a recordset or application into application or session scope leads to:

• serialization of requests
• thread-affinity of request
• wasted memory in the case of session variables
• a bottle-neck in the case of application variables

What works for few users will not for many

Imagine how fast and convenient a Porsche is when driving to Las Vegas with your significant other. Must be the best way to get there... Right? Now imagine a bunch of seniors want to go. 200 seniors x 100 trips in the porsche. Ouch! The tour bus or an airplane scales. The porsche does not. Developers should always design for scaling not minimal users.

Application Level Connections

Mechanically, creating one Application level variables with a database connection is simple.

SUB application_onstart
   set application("theCONN")=server.createobject("adodb.connection")
END SUB

Then any code that wants to use that connection might look like this:
<%
mySQL="select * from publishers where state='NY'
set rstemp=application("theconn").execute(mySQL)
%>
However this code will not scale well. The code is mechanically easy, but the scripts using this code will suffer from Thread-Affinity and Serialization.

An application variable lives until a web server shuts down. It also lives on a specific thread; we would say the object has thread affinity. Any script utilizing that object must live on same thread, as that object or make an expensive marshalled call to talk to that object.

Thread Affinity

Thread affinity is bad. If a request comes in it would be fast if any thread could perform the task. Thread affinity guarantees only 1 thread can service the request and if that thread is busy the task waits in line for that thread, despite other threads who may be able to service the request.

http://www.learnasp.com/advice/threads.asp
explains threading fundamentals.

Serialization is an enemy of Scalability

If multiple tasks all have affinity to the same thread they become serialized, that is to say they all run in sequence. As a real-world analogy for serialization imagine you wanted to buy a meal at a fast-food cashier and the person in front of you ordered 150 sandwiches for their swim-team. You don't get your 1 sandwich until they finish their order. 6 request for 150 records from a database means the fourth request for 1 record comes after those 900 are retrieved. Without thread affinity the requests could be divided among available threads.

Conclusion: All recordsets and database access would have some level of thread affinity if one connection only is made in global.asa. The side-effect would be recordsets that were forced to specific threads and serialized requests effectively slowing a site's overall performance and for specific scripts.

Recordsets stored at Sessions Level...

Mechanically, this is easy to accomplish:

SUB session_onstart
   set session("rstemp")=server.createobject("adodb.recordset")
END SUB

Then any code that wants to use that connection might look like this:
<%
    ...
    mySQL="select * from publishers where state='NY'
    set session("rstemp")=conntemp.execute(mySQL)
%>
However this code will not scale well. The code is mechanically easy, but the scripts using this code will suffer from Thread-Affinity, Serialization, and there will always be more recordsets than users accessing a site. In fact a more aggregious version of Thread Affinity occurs which I will nickname Thread-Locking.

Thread Affinity Part #2

In IIS3 and IIS4 once a user is assigned a session object (recordset or VB5/VB6 component) with thread affinity every script run in that session is forced onto the thread the first script is assigned. It effectively handcuffs every user's script to the thread their first script was assigned. This occurs because recordsets and VB5 and VB6 need to store their data on a specific thread and until destroyed (unlike C++ components which specifically aggregate the Free Thread marshaller) the objects may never be moved to a different thread which hurts performance.

More Recordsets than Users??? Please explain...

If for example a mere 20 users who access 1 page each and no others at tour site, their session objects (in this case recordsets) persist for the default session timeout (20 minutes unless the registry is modified or a page explicitly sets it using the session.timeout command. If then 50 users access several scripts and 20 of them go lunch or go home for the day, the recordsets will waste memory until their session times out. In this example there would be 90 recordsets and 30 active users within minutes. How can any scenario where significantly more resources are allocated than are being used be efficient?

http://www.learnasp.com/freebook/asp/globalproblems.aspx
explains a typical scenario.

Create and Destroy on Every Page

Even though every page creates and destroys connections, the newly created ones can execute on any thread. Also because of round-robin execution resources are used sparingly and connections are never open too long.

Make Sure Connection Pooling is in Effect

Connection Pooling can speed up sites. ADO invisibly can co-ordinate that any destroyed connections are not instantly destroyed, instead they are left ready to be grabbed by the next page who requests creating a connection. That page's code has a create request that gets intercepted at a low-level and given an already created connection ready to do database access. The programmers code then is not being executed literally; their create requests often get serviced by already created connections.

http://www.learnasp.com/freebook/asp/dbpooling.aspx
has more information and details.

What is the fastest way then (if I want fast, scalable database displays)?

"The Worlds Fastest Listbox"
http://www.learnasp.com/freebook/asp/speedappdata.aspx
shows a lightning fast way to display databases.

GetString to speed up Data Transfer
http://www.learnasp.com/freebook/asp/dbtablegetstring.aspx

GetRows to speed up Data Transfer
http://www.learnasp.com/freebook/asp/dbtablegetrows.aspx

All we discuss is fast code over at:
http://www.asplists.com/asplists/aspfastcode.asp

What If I am willing to pay the price?

Make sure you test the load using tools like the Web Stress Tool @
http://homer.rte.microsoft.com

Much more information on scalability can be found on
http://www.asplists.com/asplists/aspscalability.asp
which has links  and a signup for an excellent 2 way listserver.

There are many worthy charities!!. But perhaps help starving children in Africa or South America AND help Charles too. a $5 tip buys him lunch at McDonalds, a $20 tip buys his kid Hitoshi a new computer game, a $39 tip buys his daughter Michiko a few nice outfits. See our donor list.