DataTable.Load()

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Scott M. - 06 Mar 2008 22:33 GMT

Does the DataTable.Load() method do its operation in one database round-trip
when it is passed a DataReader?

Reply to this Message

William Vaughn - 07 Mar 2008 00:43 GMT

Ah, not really. The DataReader is optimized based on cache-size blocks but
as far as the server knows it's a single RT and very low impact. Of course,
this assumes you're using Load or some other high-speed extraction code that
does not let the SQL Server service thread idle.

Signature

__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________

> Does the DataTable.Load() method do its operation in one database
> round-trip when it is passed a DataReader?

Reply to this Message

Scott M. - 07 Mar 2008 01:03 GMT

I'm sorry, I'm not sure I get what you mean.

You say that as far as the server knows, it's a single RT. Doesn't that
mean that SQL sees the DataReader making one call for data?

Thanks.

> Ah, not really. The DataReader is optimized based on cache-size blocks but
> as far as the server knows it's a single RT and very low impact. Of
[quoted text clipped - 3 lines]
>> Does the DataTable.Load() method do its operation in one database
>> round-trip when it is passed a DataReader?

Reply to this Message

Cor Ligthert[MVP] - 07 Mar 2008 04:55 GMT

Scott,

AFAIK is forever written that for retrieving dat beside a Execute.Scalar
forever a DataReader is used.

Bill gives in my idea a more exact answer, to include to include in that
cache side blocks. However, as a DataReader does not do it in one roundrip,
then a Table.Load or whatever class inheritting from the DBAdapters will as
well not do that.

Cor

> I'm sorry, I'm not sure I get what you mean.
>
[quoted text clipped - 10 lines]
>>> Does the DataTable.Load() method do its operation in one database
>>> round-trip when it is passed a DataReader?

Reply to this Message

Wen Yuan Wang [MSFT] - 07 Mar 2008 09:36 GMT

Thanks for William and Cor's reply.

Hello Scott,

In my opinion, there is only one round-trip in Load method. If you use
profile to trace what happens on SQL server, I believe you won't find any
connection open/close event when loading.

But, on ado.net side, .net runtime get data from DB server by cache side
blocks.
The implement of Load method should be something like blow:
While (DataReader.read())
{Table.Rows[row][column]=DataReader.getFiled(int);}

Hope this helps. Please feel free to let me know if you have more concern.
We are glad to assist you.
Have a great day,
Best regards,

Wen Yuan
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
msdnmg@microsoft.com.

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Reply to this Message

Scott M. - 07 Mar 2008 12:39 GMT

Why would you see the connection opening and closing on the server during a
DataReader's read operations? Wouldn't it just stay open until .NET asks
for it to be closed?

> Thanks for William and Cor's reply.
>
[quoted text clipped - 41 lines]
> This posting is provided "AS IS" with no warranties, and confers no
> rights.

Reply to this Message

Patrice - 07 Mar 2008 13:00 GMT

This is done in a single *database* round trip. It uses though a buffer so
depending on the round trip definition it could be also seen as using
multiple (*network*) round trips (i.e. though I don't know about the details
it's likely something like when the buffer is read the client will get the
next round of data at the TDS protocol level). I discussed this a while ago
with someone who unplugged the netword after having opened the reader and
was amazed because it worked while the doc says it reads "row by row".

I told him to get more data and it failed later so we concluded it reads
using a buffer (the doc likely talks about a logical point of view not about
the underlying implementation details).

Not sure if you are interested in TDS protocol details but IMO if you have a
particular issue in mind your best bet is likely to ask directly about your
issue...

--
Patrice

> Why would you see the connection opening and closing on the server during
> a DataReader's read operations? Wouldn't it just stay open until .NET
[quoted text clipped - 49 lines]
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.

Reply to this Message

Cowboy (Gregory A. Beamer) - 07 Mar 2008 16:30 GMT

That is what Wen was saying: You should not see any open and close. I would
take this further and say "I would be absolutely shocked to see any new
connections", as the under-the-hood operations are not transparent and do
not bubble up to the connection level.

What Bill was talking about is the way SQL Server handles things, which is
not something the average developer would ever get into. What Bill was
stating is that communication will be in blocks. This does not mean a new
connection opening each time, as the Reader is a firehose cursor. As you run
through it and get past what is cached, you will get another request.

In normal operations, you run through a Reader like so:

while(Reader.Read()
{
}

This operation takes a fraction of a second to complete, at least in most
applications, as you do not want to leave a connection dangling.

Now, back to DataTable.Load(). It is still using a Reader, underneath the
hood. As it loads, the data is loaded in blocks. As more info is needed,
more blocks of data are requested, until complete. This is transparent to
the user, as the entire Load() operation should normally be in milliseconds.
Sure, you can load a boatload of data, but, other than reporting, there are
few reasons to do that.

Signature

Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

*************************************************

| Think outside the box!

*************************************************

Reply to this Message

William Vaughn - 07 Mar 2008 17:33 GMT

If you're seeing Opens and Closes you're looking in the wrong place. When
the connection is closed, the server-side agent fetching the query rows is
taken out and shot and all of his possessions are given to the poor. A
DataReader needs an open connection for the entire fetch process. AFA the
Load operation, it's doing onsy Read calls to get the rows from the
client-side buffer. The underlying mechanism is retrieving rows in blocks
from the server-side agent that's fetching them in blocks. Yes, all of this
is transparent to the ADO.NET developer (as it should be).

Ah, what's the problem you're trying to solve? Performance? I've seen very
few problems solved by asking the question faster or by listening for the
answer faster. It's all in the question; the query. How long does it take to
run?

Signature

Reply to this Message

Scott M. - 08 Mar 2008 00:20 GMT

I'm not trying to solve any problem, I'm just trying to get a deeper
understanding of what is happening at the network/sql server level. I am
quite familiar with the .NET coding details (must have open connection -
iterate over the rows using a while loop - forward only, read-only firehose
cursor).

Thanks everyone.

> If you're seeing Opens and Closes you're looking in the wrong place. When
> the connection is closed, the server-side agent fetching the query rows is
[quoted text clipped - 69 lines]
>>> This posting is provided "AS IS" with no warranties, and confers no
>>> rights.

Reply to this Message

Miha Markic - 07 Mar 2008 07:49 GMT

AFAIK it reads row by row, sort of. I wouldn't say it is an one round-trip.

Signature

Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

> Does the DataTable.Load() method do its operation in one database
> round-trip when it is passed a DataReader?

Reply to this Message

DataTable.Load()

Free Magazines