|
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.
|