Solved

DataTable versus SQLDataReader benefits on Grid

Posted on 2014-02-28
7
333 Views
Last Modified: 2014-02-28
I was told in a previous post to populate a grid with DataTable instead of SQLDataReader.

I just ran a test of 30,000 records in the result set, but a pagesize of 100, and the page load was typically identical in both cases. I ran the test about 10 times for each scenario and they fluctuated within 3 seconds of each other, sometimes winning, sometimes losing. But nothing that would seem to be a clear winner ever.

What are the benefits again?
0
Comment
Question by:Starr Duskk
[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
  • 3
  • 3
7 Comments
 
LVL 40

Assisted Solution

by:Jacques Bourgeois (James Burger)
Jacques Bourgeois (James Burger) earned 167 total points
ID: 39895716
Reread my answer in your previous post.

DataReader is usually best if you simply display the data.

DataAdapter is usually best if you need to send back corrected data to the database after modifications.
0
 
LVL 83

Assisted Solution

by:CodeCruiser
CodeCruiser earned 333 total points
ID: 39895818
>DataReader is usually best if you simply display the data.

There is another aspect though. Datareader reads one row at a time. Datatable is populated in one go.
0
 
LVL 2

Author Comment

by:Starr Duskk
ID: 39895902
I did tests of 230,000 records and there was no noticeable difference in speed between the two. That being said, I don't understand the importance of read one row at a time, versus populated all at once.

I do notice if I wrap a timer around the DataTable creation it takes 5 seconds, whereas the SQLDataReader takes less than one second. But the page load is identical for both, so the time is made up elsewhere.

I'm so confused. :(

What am I losing by using SQLDataReader, what am I specifically gaining by using DataTable? I don't do inserts, updates, deletes within my grids.

thanks.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 2

Author Comment

by:Starr Duskk
ID: 39895911
And also does a SQLDataReader require it be closed? Maybe that's the big issue. I'm not closing it, but you said a DataTable doesn't need to be closed.
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 333 total points
ID: 39895917
Yes reader needs closing as it occupies the connection while its open.

You may not be seeing difference in speed with one user but the difference in speed as well as available connections limit on SQL Server would become apparent once you try to scale your application.
0
 
LVL 2

Author Comment

by:Starr Duskk
ID: 39895954
>>You may not be seeing difference in speed with one user but the difference in speed as well as available connections limit on SQL Server would become apparent once you try to scale your application.

aha! That's exactly my problem! thanks!
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39896002
Glad to help :-)
0

Featured Post

Automating Terraform w Jenkins & AWS CodeCommit

How to configure Jenkins and CodeCommit to allow users to easily create and destroy infrastructure using Terraform code.

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…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

632 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