S
Scott M.
Does the DataTable.Load() method do its operation in one database round-trip
when it is passed a DataReader?
when it is passed a DataReader?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
W
William Vaughn
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.
--
__________________________________________________________________________
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)
____________________________________________________________________________________________
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.
--
__________________________________________________________________________
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)
____________________________________________________________________________________________
S
Scott M.
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.
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.
C
Cor Ligthert[MVP]
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
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
M
Miha Markic
AFAIK it reads row by row, sort of. I wouldn't say it is an one round-trip.
W
Wen Yuan Wang [MSFT]
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:
(e-mail address removed).
==================================================
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.
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:
(e-mail address removed).
==================================================
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.
S
Scott M.
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?
DataReader's read operations? Wouldn't it just stay open until .NET asks
for it to be closed?
P
Patrice
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
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
Scott M. said: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?
"Wen Yuan Wang [MSFT]" said: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:
(e-mail address removed).
==================================================
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.
C
Cowboy \(Gregory A. Beamer\)
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.
--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA
*************************************************
| Think outside the box!
|
*************************************************
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.
--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA
*************************************************
| Think outside the box!
|
*************************************************
Scott M. said: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?
"Wen Yuan Wang [MSFT]" said: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:
(e-mail address removed).
==================================================
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.
W
William Vaughn
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?
--
__________________________________________________________________________
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)
____________________________________________________________________________________________
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?
--
__________________________________________________________________________
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)
____________________________________________________________________________________________
Scott M. said: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?
"Wen Yuan Wang [MSFT]" said: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:
(e-mail address removed).
==================================================
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.
S
Scott M.
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.
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.
William Vaughn said: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?
--
__________________________________________________________________________
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)
____________________________________________________________________________________________
Scott M. said: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?
"Wen Yuan Wang [MSFT]" said: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:
(e-mail address removed).
==================================================
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.