Asp.Net session vs. Database Queries.

[DNB]

I did post this message in C# group also...

I would like to know what you guys think is the best way to access data:
Asp.Net session vs. Database Queries.

In our application we are using asp.net tree view to display hierarchical
data and when user clicks on particular node it brings up totally different
page with all the asp.net controls dynamically generated.


Example:
Tree view Control is as follow:
Student1
Student2
Student3
Student4
.......
......
.....
.....
Student 75

Now I have 2 options:
1) Now when user click on any node (i.e. Node Student2) another page will be
displayed and all the data related to that will be accesses from SQL server
using SQL Queries

2) OR Should all the data be accessed initially loaded into Session
Variable before building Tree View and when user access Node Student 2, just
get it from Session Variable.

Thanks
DNB

 

[Aidy]

If the dataset is small and unchanging you could store it in the
Application, or in some static (shared in VB.net) class properties. If the
dataset is large and/or frequently updated you're better getting it from the
database.

 

[Scott Roberts]

Make your life easier, go ahead and load from the DB every time.

Unless you expect users to frequently click every node in the tree in rapid
succession, in which case you might want to reconsider your UI design
anyway.

 

[Cowboy \(Gregory A. Beamer\)]

In general, I like to persist data in a database until it is going to be
used. You can prefetch some data, but loading a lot of data into session (or
cache) is not generally the first choice, as most of it will never be used.

In this case, I would NEVER use Session. Caching might be an option,
depending on the nature of the application.

Example: Seventy five students. Prefetch all data about the students and
cache (In this case, better than session). User clicks a single student. You
now have seventy four student's data loaded in session (ie, in memory) for
the length of the session, even though only one was used.

Here is why caching is preferred over session. You cache once across all
sessions, so the same data is available to all people using the particular
page. The cache can be set to update occasionally or, in custom situations,
based on events (very easy if using SQL 2005, 2008 -- requires polling in
2000). If 100 people are using this app at the same time, you have saved 99%
of the memory used to cache over loading in each session.

Here is why caching is not necessarily wise in this case: The more students,
the more information you are caching. And, this is likely to be information
you can just as easily query as needed (caching the query may or may not be
wise, depending on how many times the same person will access the same
record). You can also set cache to expire independently of session timeout.
If the information can be pulled as needed, with perhaps a temporary cache,
I would head this direction.

Now to your options

1) Now when user click on any node (i.e. Node Student2) another page will
be
displayed and all the data related to that will be accesses from SQL
server
using SQL Queries

This can be another page or another control on the same page. GridView and
DetailsView work very nicely together to make the application flow. Throw in
an AJAX UpdatePanel and you can even have the information flow in without a
full server round trip.

2) OR Should all the data be accessed initially loaded into Session
Variable before building Tree View and when user access Node Student 2,
just
get it from Session Variable.

Very rarely do I try to prefetch, much less into session