Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

DataReader vs. DataTable

Posted on 2011-02-10
9
Medium Priority
?
628 Views
Last Modified: 2012-05-11
Hello,

I have the following code listed below and would like to know which method would be better to use, either a DataTable or DataReader. The content that this code retrieves will hardly change but it's data is tied to other data for reasons that I cannot make it static within the site.

This code below is called in 20 different pages. The same code in each 20 pages called each time a user visits each of those 20 pages.

CODE:

    protected void Page_Load(object sender, EventArgs e)
    {
            Retrieve_NavLinks();
    }

    protected void Retrieve_NavLinks()
    {
        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Test"].ConnectionString))
        {
            SqlCommand cmdRetrieveNavLinks = new SqlCommand();
            cmdRetrieveNavLinks.CommandText = "PicksPhoto_RetrieveNavLinks";
            cmdRetrieveNavLinks.CommandType = CommandType.StoredProcedure;
            cmdRetrieveNavLinks.Connection = conn;

            conn.Open();

            SqlDataReader rdr = cmdRetrieveNavLinks.ExecuteReader();

            rpRetrieveNavLinks.DataSource = rdr;
            rpRetrieveNavLinks.DataBind();
        }
    }


STORED PROCEDURE USED:

ALTER PROCEDURE [dbo].[PicksPhoto_RetrieveNavLinks]

AS

SELECT navm.mnav_id, navm.onav_id, navm.mnav_linkname, navm.mnav_url
FROM [App_NavMain] AS navm
INNER JOIN [App_NavOrder] navo
ON navm.onav_id = navo.onav_id
WHERE navm.mnav_id > 1
ORDER BY onav_id ASC

GO
0
Comment
Question by:asp_net2
[X]
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
  • 2
  • 2
  • 2
  • +2
9 Comments
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 34867281
A DataReader maintains a connection to the database for the entire time you are reading the data, and the data is delivered as you ask for it. A DataTable pulls all the data from the database into the DataTable construct and then lets you do all sorts of things to it.

Generally a DataTable will be slower and more memory intensive.

In your situation could you consider caching the data for a period of time to avoid having to pull it every page load?
0
 
LVL 30

Expert Comment

by:anarki_jimbel
ID: 34867312
OK, I'm not quite sure about other complications for your app but I'd probably (especially for ASP) I'd choose Datareader.

The main and very important advantage - performance. I believe for on-line apps it's crucial.

It's easier to work with tables, definitely.

Probably, volue of data is important. If it's small - DataTable would be OK and fast enough.

Also. datareaders have other drawback. Please read the article:

http://www.eggheadcafe.com/articles/20030205.asp
0
 
LVL 4

Author Comment

by:asp_net2
ID: 34867533
Could you show me example how I would cache the code I have above? Is there anything I have to enable for caching?

I guess I'm a little confused, I was hoping of a solution that would somehow prevent 20 hits to my DB for the same data as in the code above.
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 21

Expert Comment

by:Dale Burrell
ID: 34867568
Well your question said "would be better to use, either a DataTable or DataReader" - which has been answered.

It sounds like you also want to know how to improve the performance of the site. Firstly it depends how often and under what circumstances the data changes. If having considered that you determine that you can afford to cache the data for a period then you need to consider it the data the same for all users in which case look at asp.net caching (http://msdn.microsoft.com/en-us/library/6hbbsfk6(v=VS.71).aspx) if it differs from user to user then consider using a session variable (http://msdn.microsoft.com/en-us/library/6ad7zeeb.aspx).

HTH
0
 
LVL 4

Author Comment

by:asp_net2
ID: 34867601
The data will hardly ever change. All users need to see the same data. I plan on expecting thousands of hits per day.
0
 
LVL 30

Expert Comment

by:anarki_jimbel
ID: 34867750
OK, this definitely needs caching to avoid DB traffic, I'd say. Not quite sure how this is done in ASP correctly - should be on Google.
0
 
LVL 14

Accepted Solution

by:
systan earned 2000 total points
ID: 34868566
if where you are comfortable to use your code?, then go for it.

Using dataReader is faster and a direct memory use.
Using dataTable can be filled if another data component to be called, like, dataRow, or dataAdapter.

As long as I remember when I benchmark these to component, it was a 2 seconds difference in a hundred thousand records.

DataReader vs. DataTable ?
Winner is DataReader

BUT,
like I said "where you are comfortable to use your code?, then go for it."
it only matter a second or 2 or maybe 3 for a million records.
0
 
LVL 11

Expert Comment

by:SAMIR BHOGAYTA
ID: 34870546
0
 
LVL 14

Expert Comment

by:systan
ID: 34871329
The best way to optimized your web performance is the way you handle your configurations,  and the code structures you wrote if is clean and simple.



thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

670 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