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

x
?
Solved

DataTable versus SQLDataReader benefits on Grid

Posted on 2014-02-28
7
Medium Priority
?
334 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 668 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 1332 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
Independent Software Vendors: 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!

 
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 1332 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

Industry Leaders: 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

Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

660 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