Why Does Sorting Require Another Call To The Database?

[Lisa]

I hope someone can help with this.

Every example I've seen, and every example I've written, where I fill
a datagrid from a recordset I get from the database, requires me to go
back to the database and get the recordset again every time I sort by
a column.

Maybe I'm just not understanding the concept, but since I already have
the data in the datatable, why shouldn't I be able to persist that on
the server and just sort the data that I already have? Why is it
necessary to go banging on the database every time I want to sort?

Is it possible to sort without going back to the database? Is there a
reason why it's a bad idea? It seems that it'd be the most user
friendly (quickest) way to do things, no?

Thanks,
Lisa

 

[Alvin Bruney [MVP]]

each just depends on your architecture. lazy programmers like me just change
the query and bang the database. better programmers like yourself search for
a more efficient way. consider this verse:

basically, you will have to maintain the last sort direction somewhere
and the cached data as well. tweak the code how you see fit.


//change sort direction if the previous column sorted is the same as the
current column sorted
string SortExpression = e.SortExpression;
string SDirection = "ASC";
if ( ViewState["SortExpression"] != null && ViewState["SDirection"] !=
null && e.SortExpression == ViewState["SortExpression"].ToString())
SDirection = (ViewState["SDirection"].ToString().StartsWith("ASC")) ?
"DESC" : "ASC";
else
SDirection = "ASC";

//set the session variables to new value
ViewState["SortExpression"] = SortExpression;
ViewState["SDirection"] = SDirection;

DataSet ds = (DataSet)Session["DATASET"];
DataView dv=new DataView(ds.Tables[0]);
dv.Sort = SortExpression + " " + SDirection;
BaseReport.DataSource = dv;
BaseReport.DataBind();

 

[Colin Young]

The problem with persisting the data in memory is that you might get
hundreds or thousands of requests between when the user requests the
original data and when they request the sorted data. You might even update
the underlying data, thereby invalidating the cached data, but I'll ignore
that possibility.

Now if you aren't getting that sort of traffic, there's a really good chance
you aren't really taxing the capabilitied of your DB server. If you are
getting that sort of traffic, it's entirely likely that server-side caching
is going to very quickly fill up the web server's memory, or the cached data
will be flushed before the user requests the resorted data. What you really
want to do is cache the data that all the users are requesting all the time.

See if this article is a good starting point:
http://aspnet.4guysfromrolla.com/articles/100902-1.aspx

The quickest way would be if you could cache it at the user's end, but there
isn't a good cross-platform solution for that (at least not one I'm aware
of).

Colin