Solved

DataReader creates too much traffic and database queries?

Posted on 2012-03-28
12
284 Views
Last Modified: 2012-06-21
The need to know:

Since the debate is so great for the DataReader and filling a GridView control instead of a Dataset, why are developers suggesting a GridView, is it because it can hold all the data in memory while being able to make use of the many GridView features?

I know I can fill the simpler controls directly, I have actually done so, but the problem that I see with using the DataReader only, is that with my complex Stored Procedure that brings a "large" amount of data over to the web Client, it will need to connect to the Database Server each time I need to access the data, and each time the complex Stored Procedure will need to run. So isn't this a bit expensive if you're not making use of caching to store the data in memory, since the Database Server will now have much more traffic than if the web Client could make use of a Disconnected Dataset?

Does the DataReader create too much traffic and to many database queries?
0
Comment
Question by:mitdaniels
  • 6
  • 6
12 Comments
 
LVL 74

Assisted Solution

by:käµfm³d 👽
käµfm³d   👽 earned 500 total points
ID: 37779694
I'm not sure I'm fully understanding the question, but a DataReader is only going to create traffic when it requests data, and from what I am reading it will only fetch as much data as can be read at the time. What I mean by this is that if you are not calling Read fast enough, then the network card's buffers will fill up, and if they fill completely, then data transmission is more-or-less paused until you start reading again. The reader is not going to keep the data you have already read in memory. This is because of the reader's "forward-only" nature. This trait usually gives you better application performance because you are not caching data in your application's memory space.

As far as "too many queries": you only generate a query whenever you call one of the ExecuteXXX methods. Even a DataSet or a GridView is going to have to generate and execute a query if it is in a situation to persist data/changes back to the database.

A DataSet on the other hand (which can be filled via a DataReader) would incur the penalty of transmitting the whole of the data over the wire and persisting the data in the memory space of your application. I believe for large amounts of data, using a DataSet would be detrimental--though I think the quantity of data has to be quite large (I don't recall the exact number). Caching small(er) amounts of data may very well yield better performance.

I guess you could say, "right tool for the job."

1 - http://stackoverflow.com/questions/6033451/how-does-fetching-data-from-sql-server-to-sqldatareader-work
0
 

Author Comment

by:mitdaniels
ID: 37780029
Perhaps I don't fully understand how the Dataset deals with the data it stores in memory. It is my understanding that queries can be made against a Dataset after it has disconnected, and is now on the client side. If that is the case then it would be best to bring as much data over as is efficient for your web application, and then make any additional queries against the Dataset? This would mean less traffic between server and client. Only the initial "large" query would then be of concern, and any updating to the actual database.

This is a question, I'm not sure I understand the Dataset correctly.
0
 
LVL 74

Accepted Solution

by:
käµfm³d   👽 earned 500 total points
ID: 37780161
It is my understanding that queries can be made against a Dataset after it has disconnected, and is now on the client side.
Yes.

If that is the case then it would be best to bring as much data over as is efficient for your web application, and then make any additional queries against the Dataset?
Perhaps, but think about this:  A web request is essentially a one time shot--like mailing a postcard. Once I send the request, the server processes it, and sends me a response. Disregarding Session, nothing persists on the server between requests to a page. If you were going to persist the data you queried from the database for subsequent requests to the page, your choices for persisting the data would be:

1.

Store the data in the Session (assuming InProc session state)

2.

Store the data to a file

3.

Store the data in a cookie (highly impractical)
If you store the data in the Session, then for every user you are storing X number of rows in memory. Yes, you can store a DataSet to the Session object, but this consumes system resources. If you store data to a file, then you incur the cost of reading the file on subsequent requests, but this would most likely be quicker than calling out to the DB. If the data is small, you might get away with sending it in a cookie (or maybe ViewState), but that would probably be nonsensical--especially considering that a user would have access to the cookie on their system. If you didn't encrypt the data, then an end-user could modify the data you sent. (With ViewState, they couldn't if you had the MAC validation enabled, but I still think saving such data to ViewState would be impractical.)

I may be forgetting some other mechanisms for persisting data. I'm sure if I did, each has its own pros and cons.
0
 

Author Comment

by:mitdaniels
ID: 37780250
I suppose then, a Dataset would be useful in an Intranet scenario and applications where users are likely to spend a long time on a single page. Since the nature of the web is to move from page to page, I see your point. There is also the scenario where users are starting to spend longer periods of time on a single page having Ajax send portions of data not the whole page.

Given that, would you say that I should break-up my large stored procedures and deal with data in smaller chunks, even if it is a 1:5 ratio or more?
0
 
LVL 74

Assisted Solution

by:käµfm³d 👽
käµfm³d   👽 earned 500 total points
ID: 37781058
in an Intranet scenario and applications where users are likely to spend a long time on a single page.
Um, but once the user sees the page, the server is done with the communication, and everything associated with it. If the server has completed the request, then it purges the memory associated with creating the response (not the Session).

The request/response behavior of the web really is similar to sending postcards. Let's say you and I are old-fashioned:  we hate email, and we only communicate via postcards. (Pretend, for this example, that we both have an infinite supply of postcards.) Let's start with me writing you. I grab a new postcard (PC) from my cupboard, and I begin to write what I want to say. You have no idea what I am writing because I haven't mailed the PC to you yet. I finish writing my PC. Then I go to the post office and mail the PC to you. The post office delivers the PC to you a few days later. You are now reading my PC to you. In this particular PC, let's say I asked you 3 questions. You will use my PC to know which questions I asked so that you can properly answer them in your response PC. I have no idea what your responses are (at the moment)--you have yet to mail your PC to me. You grab a PC from your supply and create your reply to me. You then go to the post office and mail your response PC to me. You now shred my PC (because you are Earth-friendly and big into paper recycling, perhaps). You now have no record of this current "conversation." A few days later, I receive your PC, and I read your response to me. You have no idea what I am doing with the PC--am I saving it, did I actually read it, etc.? You only know that you sent a response to me. This whole process is similar to what happens when your browser requests a web page.

For fun, let me play out a scenario involving the Session and persisting data outside of a DB...

Same setup:  you and I are communicating via PCs. One day, I mail you a PC and on it I have written a request that you go to the Farmer's market and pick me up 30 jars of homemade strawberry jam. I tell you that I will be visiting you in a week to pick up the jars, and can you hold onto them for me until I arrive. (For my explanation of web interactions, focus here on the storage of the jars, not me saying I am going to visit you.) I mail you my PC, you receive it, you read it, and because you and I are long-time friends, you perform my request. In the interim between my arrival, you keep the jars in a crate by your font door. This crate represents the Session object. Right now, you are storing my strawberry jam jars (some data) in your crate (Session object). You mail me back a PC and you let me know that you have fulfilled my request. A few days later, I send you a PC stating that I cannot make it up to visit you, and I ask if you will send me the jars via post. You read this PC when you receive it, and you fulfill my request. This has cleared the jars out of your crate--whereas in the days between your first response to me and this latest response, the jars stayed in your crate. In between my two requests of you, you persisted my jam jars in your crate until something prompted you to remove them from the crate (me asking you to mail them to me). Once you have mailed me the jars, you again shred the PC I sent you, and now there is no record of this conversation as far as you are concerned, and your crate is empty.
0
 

Author Comment

by:mitdaniels
ID: 37789820
Thanks for the explanation.

I'm not sure I get the connection between it and having to create more shorter and lighter stored procedures, which in itself implies more traffic between server and browser, because it clearly does. The flip side is that the speed is around 30 times faster than using a larger stored procedure that passes the data to a Dataset. So I do like the DataReader, but the real question is when to use a Dataset and why.

I am sure that Microsoft would not spend millions on a set of feature that is useless for the web?
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Closing Comment

by:mitdaniels
ID: 37906268
Thanks for the detail, but nobody has given a clear explanation of how the DataReader gets away with not causing more database hits than a DataSet.
0
 
LVL 74

Expert Comment

by:käµfm³d 👽
ID: 37906288
The short answer is the right tool for the job.

You can't cache a DataReader (i.e. store it in server memory) reliably. It maintains a connection to the DB while it is open. A DataSet, however, can remain in memory because the data it contains is disconnected from the DB (cached). The DataSet can remain in (web) server memory between requests to your page. There is no connection to the DB to maintain like there is with a DataReader.
0
 
LVL 74

Expert Comment

by:käµfm³d 👽
ID: 37906289
P.S.

Depending on how you write the code, a DataSet could certainly result in less trips to the DB than a DataReader would.
0
 

Author Comment

by:mitdaniels
ID: 37906329
So the DataReader causes the database connection to be open for much longer than if it quickly handed the data to a DataSet and then immediately closed the connection? How long the DataReader is open depends on the application, while using a DataSet allows one to close the database connection as soon as the DataSet is filled?

Given the same Stored Procedure, the time it takes to fill a DataSet before closing the connection, and the time it takes for the application to do what it needs with the data and then close the DB connection, is there a difference?

Can the DataReader handle more than one Stored Procedure during the same open connection?
0
 
LVL 74

Expert Comment

by:käµfm³d 👽
ID: 37906352
So the DataReader causes the database connection to be open for much longer than if it quickly handed the data to a DataSet and then immediately closed the connection?
More or less.

How long the DataReader is open depends on the application, while using a DataSet allows one to close the database connection as soon as the DataSet is filled?
Yes.

Given the same Stored Procedure, the time it takes to fill a DataSet before closing the connection, and the time it takes for the application to do what it needs with the data and then close the DB connection, is there a difference?
The data exists in memory on the machine where the application is running. A DataSet can use a DataReader to load itself with data.

Can the DataReader handle more than one Stored Procedure during the same open connection?
If you enable MARS via your connection string, and you pass multiple queries (i.e. sprocs) via your Command object, then yes. You would use the NextResult method of the DataReader to inspect the other results.
0
 

Author Comment

by:mitdaniels
ID: 37906363
Thanks for the additional communication, especially the following:
P.S.

Depending on how you write the code, a DataSet could certainly result in less trips to the DB than a DataReader would.

This is exactly what I wanted to know, thanks.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

What is Waterfall Model? Waterfall model is the classic Software Development Life Cycle method practiced in software development process. As the name "waterfall" describes, this development is flowing downwards steadily like waterfall, i.e., procee…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now