Reader v. DataSet when Sorting DataGrid 1

[Isadore]

Q to the gurus:

I generate a DataGrid. It has Sorting turned on. Do I want to populate the DataGrid using a Reader or would it make more sense to fill the dataset using an adapter?

In other words, will the Reader return to the table for its information and sorting? Would a dataset prevent this and therefore be a better approach to reduce table connection requirements? An opinion or two. Thanks guys and gals.

 

[Zarcom]

I believe that a dataset would be in order here. The main reason being that a reader isn't able to fill a datagrid, as far as I know. I may be wrong in this but I don't think that I am. That'l do donkey, that'l do
Mark
If you are unsure of forum etiquette check here faq796-2540

 

[Custom24]

Hi
Zarcom -
In WinForms, datareaders are not able to be bound to datagrids, but in WebForms this is fine and is what I do all the time.

Isadore -
Interesting question. Neither way is ideal. If you use a dataset, you can do the sorting there, but you are going to have to persist (eg cache) the dataset somewhere in order to prevent it hitting the db again for the sort. If you use a datareader, you can avoid the overhead of a data set, but you correctly recognise that you'll have to hit the db every postback.

It's a trade-off. If you expect the user to use the sorting a lot, you might go with the persisted dataset option. If you don't, and especially if the datagrid is returning paged records properly (ie not just returning the entire results of the query over the wire between the webserver and the db server), then go with the reader option.

I hope this helps you.
Mark

 

[Zarcom]

Thanks for that Mark Tis good to know. That'l do donkey, that'l do
Mark
If you are unsure of forum etiquette check here faq796-2540

 

[Isadore]

Zarcom and Mark, thanks. You guys are good; no doubt many on this board, like myself, appreciate your attention. Too bad we can't drop a few pennies each time in your "consulting" basket.

Cache vs. hitting the dbase. I suppose if you have plenty of server memory, the latter.

Thanks again!

 

[Zarcom]

Yes those pennies would nice. Thanks That'l do donkey, that'l do
Mark
If you are unsure of forum etiquette check here faq796-2540

 

[Custom24]

Isadore -
I'm not so experienced with DataSets in WebForms - only in WinForms. As I say I tend to use the reader approach all the time so far in Webforms.
However, this is interesting, so I will try some stuff out and let you know.
Thanks
Mark

 

[Custom24]

Isadore - I've been thinking about this and realised that more than just the sorting will affect the best way to do this. Would you like to post some info about the nature of the project, how many simultaneous users, whether the information being pulled from the db varies a lot and whether the underlying db data changes quickly. Is it an intranet or internet project. And anything else you think might be relevant

Mark

 

[Isadore]

Mark, thanks.

Right now using Access-OLEDB, 20-30 hits per day, expected to reach 200 per day in 45 days, at which time we'll switch to SQL Server 2000.

Site is located at:

http://frontpage.auburn.edu/icaae/index.aspx

You can go to the Main Menu and choose one of the buttons that show "sites" (they are water quality monitors, citizens, throughout AL) - there I am using the Sort routine.

I was wondering about this, as I use readers religiously, but thought when the traffic picks up, especially since I am using Access tables, that I need to optimize.

For me, nothing critical at the moment. Thanks for thinking about this. I would suppose tho that if you have plenty of memory to spare go with the cache -- however, I keep coming back to the Session State as the No. 1 cause of restricted scalability -- hence, server memory.

 

[Custom24]

Isadore
Hi
I used to (until last year) work as an Analytical Chemist, so this is actually quite interesting. Nice site!
Anyway, it seems to me that you might like to consider the following

If you use a reader, you are fundamentally hitting the db for every request. You can avoid hitting the db every postback by only binding if not page.ispostback, but even so, that is a lot of hits, potentially.

As we mentioned, you can choose to persist a dataset, and I think from looking at your site this might be appropriate. The data is not constantly changing, and no SQL needs to be sent to the db to query the records or anything.

What you might like to consider is instead of using session state, to persist the ds in application state. You could persist the entire data table in application state, and then for each page request, deep clone another datatable, filtered by the dropdown choice. You can then sort this.

You will need to work out some means of invalidating the application ds so that is is refreshed from the db occasionally. The simplest might be to do this in the session start event in global.asax. You could store an application variable with the last time the ds was refreshed and update it if this is too long ago.

Having said all this, even 200 hits per day should not merit this scheme, unless perhaps there is other demand on the db. If the user experience is acceptable, then you don't need to squeeze more performance out of it.

One other thing you might like to look at is paging the datagrid - some of those tables are quite big and I expect they will grow over time.

Finally, I am not a consultant, or some guru. I'm just going through the same kinds of problems myself and keeping an eye on this forum so I can investigate stuff I am likely to run into.

Hope this is of use.
Mark

 

[Zarcom]

I haven't used this yet myself, but you should look into page caching. Using this simple pages such as the one you have are cached and the simple html part of them are returned when a request for the page is made. The page cache simply refreshed after a certain period of time or when you tell it to.
I think this would be what you are looking for. But like I said I haven't actually used this myself yet. That'l do donkey, that'l do
Mark
If you are unsure of forum etiquette check here faq796-2540

 

[Isadore]

Zarcom and Mike: Thanks for your time, several of the most common datagrids are saved on the cache (for its maximum of 300 seconds) and I think this is a payoff. I need to do some reading in the Application State area, interestingly, this morning, was reading a chapter in this area. So, a little academia is called for.

Mike - rec'd my M.S. in Chemistry from Florida State Univeristy, worked as an environmental chemist for about 6 years and then went to Florida where I rec'd my ME, and then on to law school - not practicing law though, I spend about 35% of my time with databases, and now dot NET, so don't have full time for it but if you count the late night hours I'm close to 40 hrs.

This is an interesting area, as many areas in dot Net are. Will send this thread to the printer and digest it a bit. Again, thanks again for you help.

 

[Zarcom]

np That'l do donkey, that'l do
Mark
If you are unsure of forum etiquette check here faq796-2540