Tech Off Thread

15 posts

ASP.NET Session vs Database Queries

Back to Forum: Tech Off
  • User profile image
    rswafford

    I am maintaining an ASP.NET 2.0 application for my company, and am looking for ways to improve performance and otherwise streamline the app.  The best way to describe it is as a store scheduling and reporting application.  User logs in and selects what location they want to work with, and then currently it goes out to the database and retrieves all of the settings associated with that store, and sticks them in Session variables.  There are probably between 100-200 entries for each store that get stuck in the session, which is a lot I know.

    What I'm wondering is if I would be better off querying the database for those settings every time I need them, rather than storing all that data InProc in the session?  On average, I would say there's probably 5-10 pieces of data I'd be querying the database for on each page request.  Would that number of queries degrade performance at all, or would trading that for the decrease in session size be a worthwhile enhancement in overall performance?

  • User profile image
    odujosh

    Is the SQL Database on the same box. If so I would just query for them as needed.

  • User profile image
    rswafford

    The database is not on the same server, but it is on the same rack, so they're right next to eachother on the network.

  • User profile image
    Rotem Kirshenbaum

    Those settings are read only, right ? Why not use the Application or Cache object to set them for all users ?

  • User profile image
    rswafford

    They are actually configuration settings, and are unique per store, so they have to be stored per session, or queried each time they're needed.  Also, they aren't read-only, some of the pages in the application are config pages that allow users to change the settings.

  • User profile image
    RichardRudek

    Have you thought about running some tests ?

    Back when I was assisting in an (old) ASP site, they didn't know the answer either. So I suggested they simply wrapped the competing programming styles with Timer() calls. In ASP (VBScript) Timer() returns a Single type, so all you had to do was subtract the start from stop timer readings. Simple.
    eg:

    t = Timer()
    'ADO stuff
    t1 = Timer() - t

    t = Timer()
    ' Session stuff
    t2 = Timer() - t

  • User profile image
    odujosh

    I am pretty sure you ll be fine pulling it as you need it. If you really worried about network traffic move it to a local mdf in the App_Data folder just for config settings. This is hosted by SQL Express, which buys your up to a 4 GB database (a pretty unreachable limitation in most cases)

  • User profile image
    RichardRudek

    odujosh wrote:
    I am pretty sure you ll be fine pulling it as you need it. If you really worried about network traffic move it to a local mdf in the App_Data folder just for config settings. This is hosted by SQL Express, which buys your up to a 4 GB database (a pretty unreachable limitation in most cases)


    If I were in charge of this, I'd want some evidence to back this up.

    Continually pulling from your database Server not only increases it's load, but it increases your site's reliance on Server up times/reliability requirements, worsens maintainence procedures, etc. If the database predominantly contains only site configuration data, then it would want to be a bloody big gain over a one-time (start-up) loading into session state.

    But then again, I'm not a web developer...[A]

  • User profile image
    ScanIAm

    I don't think it matters if you are a web developer or not, it's more about the location and response times.

    You can actually set up your Session, Cache, etc. to be in a sql database and/or a local access database and/or memory.

    Like odujosh said, though, you need to test it out and make the best decision for your installation.

    Off the top of my head,

    1) if you have low volume, then a remote SQL server would be a fine place to store the data
    2) if your volume is higher and the network becomes a bottleneck, you'll want a database on the local machine (sqlexpress/.mdb/text file/whatever).
    3) for high volume, you are going to have to start caching stuff up in memory using either the Cache or Session objects.

    It's both a science and an art.  If you find that the front load time is too long for your users, then you can slowly load up the Session with the required variables as they are needed.  This will spread out the load time over multiple pages and the user might not notice.

    Also, if you want to get really crafty, you can

    1) load up the bare minimum needed when they choose the store.
    2) start a thread that will load up the rest of the data in the background.

  • User profile image
    RichardRudek

    ScanIAm wrote:
    You can actually set up your Session, Cache, etc. to be in a sql database and/or a local access database and/or memory.

    ...

    3) for high volume, you are going to have to start caching stuff up in memory using either the Cache or Session objects.

    Hmm. So, does this mean that the "SQL state provider" (I'm assuming here) does not support lazy loading - as in you, as a developer, have to implement this caching type of stuff ?.

    By lazy-loading, I mean loading as necessary, but then caching the result.

    I'm not sure what you mean the "memory" bit. I suppose things like uncommitable data such as shopping cart data, would be good to keep in memory until the user finally accepts, and pays for it. ie The Database Server only gets bothered with "real" data.

    Hmm, it would be cool if the "SQL state provider" did lazy writing, as well. But then again, I suppose we'd be crossing the boundaries at this point, as you'd need some way of triggering/closing a transaction. ie What a Database already provides.

    Thanks, good stuff, and sorry RS for the hijack. Smiley

  • User profile image
    ZippyV

    RichardRudek wrote:
    Have you thought about running some tests ?

    Back when I was assisting in an (old) ASP site, they didn't know the answer either. So I suggested they simply wrapped the competing programming styles with Timer() calls. In ASP (VBScript) Timer() returns a Single type, so all you had to do was subtract the start from stop timer readings. Simple.
    eg:
    t = Timer()
    'ADO stuff
    t1 = Timer() - t

    t = Timer()
    ' Session stuff
    t2 = Timer() - t



    In ASP.net you can turn on tracing and it will do all of that for each serverside event.

  • User profile image
    blowdart

    Of course you could read the database and cache it, using a SqlDependency, which means the cache will be flushed if the underlying datatable changes ...

  • User profile image
    ScanIAm

    RichardRudek wrote:
    
    ScanIAm wrote:
    You can actually set up your Session, Cache, etc. to be in a sql database and/or a local access database and/or memory.

    ...

    3) for high volume, you are going to have to start caching stuff up in memory using either the Cache or Session objects.

    Hmm. So, does this mean that the "SQL state provider" (I'm assuming here) does not support lazy loading - as in you, as a developer, have to implement this caching type of stuff ?.

    By lazy-loading, I mean loading as necessary, but then caching the result.

    You implement it, but it's as simple as

    Cache["name"] = object;

    to save it or

    object = Cache["name"]

    to retrieve it.

    Session works the same way, as do a few other 'caching' type objects.  The difference between them has to do with their scope.

    Session data is only for the current session (hence the name)

    Cache data is available to the entire appliation.  I'm pretty sure that if you are sharing thread pools in IIS, all the threads have access to the Cache.

    RichardRudek wrote:
    
    I'm not sure what you mean the "memory" bit. I suppose things like uncommitable data such as shopping cart data, would be good to keep in memory until the user finally accepts, and pays for it. ie The Database Server only gets bothered with "real" data.

    Caching, as a concept, is done when you want to keep a local copy of some data that might have a high overhead to obtain.  As long as the place where you decide to cache your data has faster read/write times than the original source, it will give you an improvement in speed.

    You can configure the the ASP.NET cache objects (Session, Cache, etc.) to store their information in memory for very quick retrieval.  But, you can also configure them to store their information in SQLServer or SQLExpress or pretty much anywhere that can read/write data.

    At first, it makes no sense to store in SQL, but if the original data comes from a slow source, then even another SQL Server on the network would be a faster place to put/retrieve the data.

    Plus, if you are writing a web app that needs to reside on a web farm, you need to keep your session data in some centralized location like SQL Server.
    RichardRudek wrote:
    
    Hmm, it would be cool if the "SQL state provider" did lazy writing, as well. But then again, I suppose we'd be crossing the boundaries at this point, as you'd need some way of triggering/closing a transaction. ie What a Database already provides.

    Thanks, good stuff, and sorry RS for the hijack.

  • User profile image
    RichardRudek

    ScanIAm wrote:
    
    RichardRudek wrote:
    
    I'm not sure what you mean the "memory" bit. I suppose things like uncommitable data such as shopping cart data, would be good to keep in memory until the user finally accepts, and pays for it. ie The Database Server only gets bothered with "real" data.

    Caching, as a concept, is done when you want to keep a local copy of some data that might have a high overhead to obtain.  As long as the place where you decide to cache your data has faster read/write times than the original source, it will give you an improvement in speed.

    You can configure the the ASP.NET cache objects (Session, Cache, etc.) to store their information in memory for very quick retrieval.  But, you can also configure them to store their information in SQLServer or SQLExpress or pretty much anywhere that can read/write data.

    Ah.  So does this mean the "Cache" object's backing-store (SQL, Memory, etc) is separately configurable from the Session and Application objects ? 

    I suppose I should look this up rather than learn via forum posts. But anyway...
     
    ScanIAm wrote:
    
    At first, it makes no sense to store in SQL, but if the original data comes from a slow source, then even another SQL Server on the network would be a faster place to put/retrieve the data.


    Yes. But the problem I have with using SQL is really an issue of appeasement. Many Database Admins that I've encountered (you know, people whose sole job is to build, optimise and maintain databases), often cite that the "Developers" are constantly pushing their problems onto them (their boxes) to fix.

    Plus I suppose there is the problem with SQL Server competeing for system resources, which in 32-bit systems, is typically RAM (memory, Random Access Memory). If a version of SQL (Express or otherwise) is running on the same box as the web server to "improve performance", I can't imagine that this would be scalable - the original poster (rswafford)  was talking about racks and sites. In fact, I'd imagine quite a few people would start talking about firing people who suggest this kind of stuff for anywhere other than a dev box. [1]

    ScanIAm wrote:
    
    Plus, if you are writing a web app that needs to reside on a web farm, you need to keep your session data in some centralized location like SQL Server.


    Very good point, and I certainly don't have too much of a problem with this. However, when you consider the DB admin, we can do a better job. I mean, I believe that their is/was a non-Database solution for this, involving some kind of "State-Server", if I'm not mistaken.

    Anyway, I don't know wether this kind of discussion is helping anyone. I mean with me being a non-web dev that's not really interested enough to read up on this kind of stuff... Smiley


    EDIT: Added note, in red.

    [1] Unless of course we're talking about a big-arse box that's running a virtualisation solution... Smiley

  • User profile image
    blowdart

    RichardRudek wrote:
    

    I'm not sure what you mean the "memory" bit. I suppose things like uncommitable data such as shopping cart data, would be good to keep in memory until the user finally accepts, and pays for it. ie The Database Server only gets bothered with "real" data.


    Actually shopping carts are probably a good candidate for databases. Think about how many sites let you build up a shopping cart, leave, and when you return it's all still there. Heck I'm implementing it right now, although not through a SQL backed profile provider.

    You've got me wondering now how often the profile provider commits, I shall have to have a poke around.

    I will say it again though, you can cache with a SQL dependency. So read once and cache using that, then if the underlying table changes, the cache is flushed and you reread (usually via a static helper class obviously). Thus you get the advantage of caching, but with no stale data.

Comments closed

Comments have been closed since this content was published more than 30 days ago, but if you'd like to continue the conversation, please create a new thread in our Forums, or Contact Us and let us know.