Solved

DataTable versus SQLDataReader benefits on Grid

Posted on 2014-02-28
7
327 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 1

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 1

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 1

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

DevOps Toolchain Recommendations

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

Question has a verified solution.

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

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…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

867 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now