Session veriables or SQL storage (best solution)?

[Guest]

I have a web application that will handle anywhere for 50 to 175 users at one
time but will likely have a user base in the 100s. This application contains
a GUI interface for the every day user and web services that can access the
GUI modules for 3rd party applications needing to get shared data

In the initial design plan, the idea was presented to retrieve the data for
the user’s dashboard and hold that data in session, only connecting to SQL
for the initial load and any update and delete. This way with each post back,
there us no need to connect to SQL to reload the data on a change. This is
b/c the SQL server is used by several in house applications not related to
this one and we had issues in the past of SQL server getting bogged down. So
the idea is to limit request to SQL with this application as needed. Its
cheaper for use to setup a dedicated web server then it is SQL server since
we have so many database that need to cross communicate, and SQL server
linking is just too slow and unreliable.

What is the trade offs of storing the session data in SQL? Even though it
still hitting SQL, I figure it’s still more efficient then executing the
entire query for the data. I know it would make it more scalable, but would
we not just end up back with SQL server getting bogged down again?

Now I realize holding entire dataset in memory is a big deal, in fact there
a 8 datasets per user. These datasets are quite small, anywhere from 1
datarow to up to 10 datarows.

The application is fast, but hasn’t been tested user say 50 simultaneous
users. Would you go with a RAM packed out web server dedicated to the app, or
store sessions in SQL on server that cannot be dedicated?

 

[sloan]

#1
Future posts on this topic would probably be better suited for:
microsoft.public.dotnet.framework.aspnet


There is a third option, which is
http://msdn2.microsoft.com/en-us/library/system.weakreference.aspx

The WeakReference class. You can weakreference object(s) in the Session,
and have "smart code" that figure out whether or not the data is cached, but
your have a work around if server strain is high.


Fourth option:
Don't use "bulky" dataset objects. Write custom objects/custom collections
to keep your data. They will probably have to be Serializable, but their
footprint ends up being smaller than a DataSet usually.

Fifth option. There are third party session state applications.


...

Take a look here;
http://www.eggheadcafe.com/articles/20020302.asp

 

[Mr. Arnold]

The application is fast, but hasn’t been tested user say 50 simultaneous
users. Would you go with a RAM packed out web server dedicated to the app,
or
store sessions in SQL on server that cannot be dedicated?

A SQL Server State server is slower than holding state in memory on the Web
Server or an Application state server. If I had my choice, I would go with a
dedicated SQL Server box dedicated to keeping session state or an dedicated
Application state server, but these solutions are for a WEB server farm
implementation for the most part.

If you're not in that situation of a WEB server farm and you can't dedicate
a SQL server box to keep state, then you have to have the Web Server
dedicated to that one application with a lot of RAM. What other choice do
you have?

 

[Robbe Morris - [MVP] C#]

Assuming your user base doesn't get more than 10 times that amount,
storing it in memory is fine.