Hello All

As we all know, we can use DataReader as well as DataSet to bind a datagrid, I just want to know which would be faster?

If DataReader is a live connection with database, if I use CommandBehavior.CloseConnection, live connection would be lost, so it can be faster!!

However, dataset is a disconnected architechture, so it can be fast too? I guess it depends on the number of tables contained within it!!!

Anyone wtih good explanation...thanx

Regards
Mike

Fortune favours the brave, so don't regret on missed oppurtunities.

__________________
Regards
Mike

In general, I do all my data access with data readers. Given that my focus is on web applications, I don't see an advantage to datasets. Most of what I have read and heard promotes using data readers if you are going to dump the data to a class which is the methodology I use. Why exactly a data reader is faster? I don't know for sure. But I suspect that it is because a dataset holds a much more complex representation of the data. In a Database to class pattern, you really just want to "firehose" the data from the DB into your classes and unhook from the DB as quick as you can to keep connections available for other requests.

If you are going to utilize the disconnected capabilities of a dataset, such as for a winforms app, then datasets make more sense. A winforms app can more easily keep a dataset "alive" (in memory) for direct wiring to a bound control like a data grid [view] and then for incremental updates.

One thing that frustrates me is all the examples out there about databinding web controls. It seems so many examples utilize datasets that are created thru the wizard. This seems to confuse the learner in many ways. First they don't really see how the dataset gets created and populated. Then they don't understand how to get updates to work. Certainly, re-loading a dataset on a postback is terribly bad and inefficient design.


One of my coworkers was recently at a talk given my Rob Howard (an MS MVP and Wrox author). One thing he stressed about architecture was: minimize connection time and reuse connections. You'll probably find that the choice of class used doesn't make difference without good database IO practices.

-Peter

Thanx Peter for that reply, one thing that Rob Howard stresses here is reuse of connection, does he mean about connection pooling or anything else, ok let me ask in general, how to reuse connection to increase application performance?

Regards
Mike

Fortune favours the brave, so don't regret on missed oppurtunities.

Thanks for the question. I had been using datareaders exclusively until I started getting a lot of errors, " There is alrady an open datareader", when trying to reuse functions and put a dataset in its place.

Found this explanation:

http://www.sitepoint.com/article/dataset-datareader

???

Richard

Connection pooling should be handled automatically. My understanding is that as long as you are connecting using the same connection string, pooling will happen. The important thing is to close an active connection as soon as you can so it can go back into the pool for another request to pick up.

Richard,

Yes, you'll definitely encounter these kinds of errors if you try to use two readers at once. This shouldn't deter you from using them however when you can given their performance gains.

If you are running into conditions where you are writing nested queries, you might want to reconsider your code design. Making repeated calls to the database in a loop is very inefficient. Perhaps there's a better way for you to select your data so that you can work with a single data reader from a single query or a dataset with 2+ queries, but certainly not a looping query call.

-Peter

Hi Peter,

I think I better correct myself or at least make things a little clearer. I don't use the datareader exclusively. As a matter of fact I don't think I've ever used a datareader to bind to a datagrid, to directly answer the question concerning which is the best for binding to a datagrid. I always use the dataset to bind to a datagrid.

I have had a lot of problems otherwise with either already having a datareader open or not having a connection open for the datareader. And in instances where those problems occured I just moved to the dataset to clear up the problem. Maybe this is another issue but I usually open a connection to the DB on page load and run all the functions on the page load to use the same connection. But if I have to fire one of those functions on a button click then I start running in the problem of either having a datareader open or not having a connection open.

Richard

I know where you are coming from. I've used some of the same techniques. One thing to consider:

The first time you open a connection is the most expensive. However, subsequent opens are much faster because of connection pooling. So I think you are better off using a "open late, close early" approach. Only open the connection just prior to using it, and drop the connection as soon as you can. Even if you open and close a connection a dozen times in the course of a page cycle, only the first will be of significance. The rest will be efficient because of the pooling.

If you are using a methodology where by you open a connection on page load, and close when the page is done... how many connection open and closes are you making for page hits that don't touch the database at all?

-Peter

Wow. That's a cool little piece of information about the connection pooling. I didn't know about that.

 

Well, I hope I'm understanding you correctly. I open and close the connection on the Page Load but only run the functions that need data within the "If Not IsPostBack".

The drop-downs that run SelectedIndexChanged to repopulate the other drop-downs run off of the same Page Load Open/Close. The Save function opens and closes its own connection.

I try to use the same connection as much as possible but I'm sure there are places where I can trim down my DB connections.

So really to answer your question simply is that I do one Open and one Close if there isn't any data being requested, say if someone refreshes the page.

Thanks Peter,
Richard

That's the point. How often are you tying up a connection by opening it on a page that never uses it. If all the pooled connections are open and some code needs a connection, another is created, thus slowing down the application and chewing up more memory.

-Peter

So, let me see if I understand what you're say.

I don't need to open/close the connection for the whole page. I'm not really getting anything performance advantage. In fact, I may be slowing things down.

And I can open/close the connection within each function on the same page and actually get a performance boost since the connection will only be used when its needed and each successive open/close will just use the same pooled connection???

Not sure if I have this right yet but I do really appreciate your patient explanation.

Thanks Peter.

Richard