Solved

DataTable versus SQLDataReader benefits on Grid

Posted on 2014-02-28
7
328 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
  • 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Gridview selected row 9 46
Sql server insert 13 31
ASP.NET MVC -Add authentication 2 21
How do sunrise and sunset times change with altitude 14 31
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

777 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