Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

DataReader creates too much traffic and database queries?

Posted on 2012-03-28
12
290 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 75

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 75

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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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 75

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
 

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 75

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 75

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 75

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Fluent Interface Design Pattern You can use the Fluent Interface (http://en.wikipedia.org/wiki/Fluent_interface) design pattern to make your PHP code easier to read and maintain.  "Fluent Interface" is an object-oriented design pattern that r…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…

856 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