Multiple Active Result Sets (MARS)

Two questions relating to this:
1 - Do I require SQL Server 2005 or can this work with SQL Server 2000?
2 - My ASP.NET book says that I require MDAC 9.0. My registry setting says
that I'm using 2.8 but I can't find where I can download an update from.
Thanks if you can help with either of these questions.
GriffSorry - need to be a bit more specific here.
I'm wanting to implement the ability to have asynchronous command
execution....
Griff|||1. Yes for MARS you need Sql Server 2005
Your second question is not clear, please give more inputs...
Thanks,
Sree|||> I'm wanting to implement the ability to have asynchronous command
> execution....
You can execute SQL commands asynchronously in ADO.NET 2.0 (Visual Studio
2005) using BeginExecute../EndExecute... methods of the command object
regardless of the provider. Each concurrent command will need a separate
connection. You can also accomplish the same result on your own using
delegates and multiple threads in any version of ADO.NET. Old fashioned ADO
can also execute command asynchronously if you specify the adAsynchExecute
ExecuteOption.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Griff" <howling@.the.moon> wrote in message
news:ejhcXwKKGHA.2900@.TK2MSFTNGP14.phx.gbl...
> Sorry - need to be a bit more specific here.
> I'm wanting to implement the ability to have asynchronous command
> execution....
> Griff
>|||MARS is part of SQL Native Client (SQLNCI) and is a SQL 2005-only feature.
SQLNCI provides features above and beyond MDAC.
You can still execute asynchronous queries without SQL 2005/SQLNCI. See my
response to your other question.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Griff" <howling@.the.moon> wrote in message
news:%23lKiyuKKGHA.3936@.TK2MSFTNGP12.phx.gbl...
> Two questions relating to this:
> 1 - Do I require SQL Server 2005 or can this work with SQL Server 2000?
> 2 - My ASP.NET book says that I require MDAC 9.0. My registry setting
> says that I'm using 2.8 but I can't find where I can download an update
> from.
> Thanks if you can help with either of these questions.
> Griff
>|||"Griff" wrote:
> Two questions relating to this:
> 1 - Do I require SQL Server 2005 or can this work with SQL Server 2000?
MARS is SQL Server 2005 only. Asynch communication can be done with either
SQL 2005 or 2000, but 2005 has better functionality built in. Note that most
of the functionality is included in SQL Server 2005 Express, which is a good
development option (may even be a good production option, depending on the
app); if you can find an ISP with SQL 2005 (I can suggest some), I would go
with SQL 2005 and use the newer model.
> 2 - My ASP.NET book says that I require MDAC 9.0. My registry setting says
> that I'm using 2.8 but I can't find where I can download an update from.
2.8 is the latest MDAC:
http://msdn.microsoft.com/data/mdac/downloads/default.aspx
I know the 9.x is important, but the reason skips my mind. If you have 2.8
with the proper .NET library installed, you are fine.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************|||> I know the 9.x is important, but the reason skips my mind. If you have 2.8
> with the proper .NET library installed, you are fine.
All that you need comes bundled up with SQL Server 2005 libraries.
- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
__________________________________________________________
"Cowboy (Gregory A. Beamer) - MVP" <NoSpamMgbworld@.comcast.netNoSpamM> wrote
in message news:AB190AE5-48A7-4604-B791-C85D4031F3EA@.microsoft.com...
> "Griff" wrote:
>> Two questions relating to this:
>> 1 - Do I require SQL Server 2005 or can this work with SQL Server 2000?
> MARS is SQL Server 2005 only. Asynch communication can be done with either
> SQL 2005 or 2000, but 2005 has better functionality built in. Note that
> most
> of the functionality is included in SQL Server 2005 Express, which is a
> good
> development option (may even be a good production option, depending on the
> app); if you can find an ISP with SQL 2005 (I can suggest some), I would
> go
> with SQL 2005 and use the newer model.
>> 2 - My ASP.NET book says that I require MDAC 9.0. My registry setting
>> says
>> that I'm using 2.8 but I can't find where I can download an update from.
> 2.8 is the latest MDAC:
> http://msdn.microsoft.com/data/mdac/downloads/default.aspx
> I know the 9.x is important, but the reason skips my mind. If you have 2.8
> with the proper .NET library installed, you are fine.
>
> --
> Gregory A. Beamer
> MVP; MCP: +I, SE, SD, DBA
> ***************************
> Think Outside the Box!
> ***************************|||Asynchronous command is not the same as MARS.
Asynch command exec should work on SQL2k also.
- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
__________________________________________________________
"Griff" <howling@.the.moon> wrote in message
news:ejhcXwKKGHA.2900@.TK2MSFTNGP14.phx.gbl...
> Sorry - need to be a bit more specific here.
> I'm wanting to implement the ability to have asynchronous command
> execution....
> Griff
>|||Good answers all.
Just be aware that ADO.NET 2.0 async ops don't work like ADO classic.
ADO.NET only _executes_ the query async--the row-fetch operation is
synchronous unless you write your own backgroundworker thread routine to
handle it.
MARS? Just leave it alone--you won't need it for 90% of the things needed to
be done.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Griff" <howling@.the.moon> wrote in message
news:%23lKiyuKKGHA.3936@.TK2MSFTNGP12.phx.gbl...
> Two questions relating to this:
> 1 - Do I require SQL Server 2005 or can this work with SQL Server 2000?
> 2 - My ASP.NET book says that I require MDAC 9.0. My registry setting
> says that I'm using 2.8 but I can't find where I can download an update
> from.
> Thanks if you can help with either of these questions.
> Griff
>|||Hmm, regarding async in ADO.NET 2.0. I'm trying to understand exactly what it is that is
synchronous. Are you saying that I cannot read any rows until all rows has been returned? I.e., a
FAST hint in the SQL query would be of no advantage, but probably lead to higher resource
utilization and slower response time (depending on the plans of course)?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"William (Bill) Vaughn" <billvaRemoveThis@.nwlink.com> wrote in message
news:OSG$1UcKGHA.3272@.tk2msftngp13.phx.gbl...
> Good answers all.
> Just be aware that ADO.NET 2.0 async ops don't work like ADO classic. ADO.NET only _executes_ the
> query async--the row-fetch operation is synchronous unless you write your own backgroundworker
> thread routine to handle it.
> MARS? Just leave it alone--you won't need it for 90% of the things needed to be done.
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> INETA Speaker
> www.betav.com/blog/billva
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> __________________________________
> "Griff" <howling@.the.moon> wrote in message news:%23lKiyuKKGHA.3936@.TK2MSFTNGP12.phx.gbl...
>> Two questions relating to this:
>> 1 - Do I require SQL Server 2005 or can this work with SQL Server 2000?
>> 2 - My ASP.NET book says that I require MDAC 9.0. My registry setting says that I'm using 2.8
>> but I can't find where I can download an update from.
>> Thanks if you can help with either of these questions.
>> Griff
>|||Synchronous is the opposite of asynchronous. It means that when you execute
a synchronous operation (as most are) the application is blocked until the
operation is completed. When you execute BeginExecuteReader, only the
_query_ is executed asynchronously. Not a single row has been returned from
the query when ADO.NET signals that the asynchronous operation is complete.
You still have to execute Read or Load to return the rows. As you do, your
application is blocked until the last row is returned (Load method).
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eiN02bkKGHA.1192@.TK2MSFTNGP11.phx.gbl...
> Hmm, regarding async in ADO.NET 2.0. I'm trying to understand exactly what
> it is that is synchronous. Are you saying that I cannot read any rows
> until all rows has been returned? I.e., a FAST hint in the SQL query would
> be of no advantage, but probably lead to higher resource utilization and
> slower response time (depending on the plans of course)?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "William (Bill) Vaughn" <billvaRemoveThis@.nwlink.com> wrote in message
> news:OSG$1UcKGHA.3272@.tk2msftngp13.phx.gbl...
>> Good answers all.
>> Just be aware that ADO.NET 2.0 async ops don't work like ADO classic.
>> ADO.NET only _executes_ the query async--the row-fetch operation is
>> synchronous unless you write your own backgroundworker thread routine to
>> handle it.
>> MARS? Just leave it alone--you won't need it for 90% of the things needed
>> to be done.
>> --
>> ____________________________________
>> William (Bill) Vaughn
>> Author, Mentor, Consultant
>> Microsoft MVP
>> INETA Speaker
>> www.betav.com/blog/billva
>> www.betav.com
>> Please reply only to the newsgroup so that others can benefit.
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> __________________________________
>> "Griff" <howling@.the.moon> wrote in message
>> news:%23lKiyuKKGHA.3936@.TK2MSFTNGP12.phx.gbl...
>> Two questions relating to this:
>> 1 - Do I require SQL Server 2005 or can this work with SQL Server 2000?
>> 2 - My ASP.NET book says that I require MDAC 9.0. My registry setting
>> says that I'm using 2.8 but I can't find where I can download an update
>> from.
>> Thanks if you can help with either of these questions.
>> Griff
>>
>|||> Synchronous is the opposite of asynchronous.
Yes, I wrote synchronous in the sense that you implied that the new "asynchronous" features of
ADO.NET 2.0 are not truly asynchronous. I.e., I was wondering which parts were still synchronous.
> You still have to execute Read or Load to return the rows. As you do, your application is blocked
> until the last row is returned (Load method).
Which answers my question :-). You cannot read the rows and present them "as they come". I.e., the
app behavior will be more like QA grid mode (synchronous behavior) compared to text mode (truly
asynchronous behavior).
I have a feeling that the SQL Server FAST hint is sometimes overused. The developer think that
he/she can gain something by using these hints: "I'll present the first few rows immediately", where
very few applications/API/programmers actually program in that sense. And when SQL Server receives a
FAST hint, the overall resource utilization can be significantly higher (using a non-clustered index
for an ORDER BY over a large set, for instance).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"William (Bill) Vaughn" <billvaRemoveThis@.nwlink.com> wrote in message
news:%23mUrrioKGHA.1124@.TK2MSFTNGP10.phx.gbl...
> Synchronous is the opposite of asynchronous. It means that when you execute a synchronous
> operation (as most are) the application is blocked until the operation is completed. When you
> execute BeginExecuteReader, only the _query_ is executed asynchronously. Not a single row has
> been returned from the query when ADO.NET signals that the asynchronous operation is complete. You
> still have to execute Read or Load to return the rows. As you do, your application is blocked
> until the last row is returned (Load method).
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> INETA Speaker
> www.betav.com/blog/billva
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> __________________________________
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:eiN02bkKGHA.1192@.TK2MSFTNGP11.phx.gbl...
>> Hmm, regarding async in ADO.NET 2.0. I'm trying to understand exactly what it is that is
>> synchronous. Are you saying that I cannot read any rows until all rows has been returned? I.e., a
>> FAST hint in the SQL query would be of no advantage, but probably lead to higher resource
>> utilization and slower response time (depending on the plans of course)?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "William (Bill) Vaughn" <billvaRemoveThis@.nwlink.com> wrote in message
>> news:OSG$1UcKGHA.3272@.tk2msftngp13.phx.gbl...
>> Good answers all.
>> Just be aware that ADO.NET 2.0 async ops don't work like ADO classic. ADO.NET only _executes_
>> the query async--the row-fetch operation is synchronous unless you write your own
>> backgroundworker thread routine to handle it.
>> MARS? Just leave it alone--you won't need it for 90% of the things needed to be done.
>> --
>> ____________________________________
>> William (Bill) Vaughn
>> Author, Mentor, Consultant
>> Microsoft MVP
>> INETA Speaker
>> www.betav.com/blog/billva
>> www.betav.com
>> Please reply only to the newsgroup so that others can benefit.
>> This posting is provided "AS IS" with no warranties, and confers no rights.
>> __________________________________
>> "Griff" <howling@.the.moon> wrote in message news:%23lKiyuKKGHA.3936@.TK2MSFTNGP12.phx.gbl...
>> Two questions relating to this:
>> 1 - Do I require SQL Server 2005 or can this work with SQL Server 2000?
>> 2 - My ASP.NET book says that I require MDAC 9.0. My registry setting says that I'm using 2.8
>> but I can't find where I can download an update from.
>> Thanks if you can help with either of these questions.
>> Griff
>>
>>
>|||I'd like to put some clarification and to confirm some of the comments in
the thread, and then point to some resources. I'm assuming that since we're
in the ADO.NET newsgroup, this is about ADO.NET in particular :)
- As several folks pointed out, MARS and asynchronous command execution are
different, unrelated things (they can be used to together, but that's
another story)
- Asynchronous command execution works against all versions of SQL Server
that ADO.NET can talk to (7.0 and later). You get the same functionality
against all of them.
- Asynchronous command execution is *not* the same as old ADO's asynchronous
execution, and it's *not* the same as using an asynchronous delegate
(BegingInvoke) or the thread-pool.
- Asynchronous command execution is entirely implemented on the client
interfaces, not in the server. It's completely unrelated to OPTION(FAST x)
or FASTFIRSTROW
A few years ago, in the first beta of ADO.NET 2.0 and early pre-release
versions of SQL Server, there used to be something called MDAC 9.0. That
thing does not exist any more. ADO.NET is now self-contained, so you don't
need any external libraries in order to use asynchronous command execution,
MARS, or any other SQL Server feature from ADO.NET.
For more information about asynchronous command execution you can read:
http://msdn.microsoft.com/library/en-us/dnvs05/html/async2.asp
--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OUF5hLpKGHA.3732@.TK2MSFTNGP10.phx.gbl...
>> Synchronous is the opposite of asynchronous.
> Yes, I wrote synchronous in the sense that you implied that the new
> "asynchronous" features of ADO.NET 2.0 are not truly asynchronous. I.e., I
> was wondering which parts were still synchronous.
>
>> You still have to execute Read or Load to return the rows. As you do,
>> your application is blocked until the last row is returned (Load method).
> Which answers my question :-). You cannot read the rows and present them
> "as they come". I.e., the app behavior will be more like QA grid mode
> (synchronous behavior) compared to text mode (truly asynchronous
> behavior).
> I have a feeling that the SQL Server FAST hint is sometimes overused. The
> developer think that he/she can gain something by using these hints: "I'll
> present the first few rows immediately", where very few
> applications/API/programmers actually program in that sense. And when SQL
> Server receives a FAST hint, the overall resource utilization can be
> significantly higher (using a non-clustered index for an ORDER BY over a
> large set, for instance).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "William (Bill) Vaughn" <billvaRemoveThis@.nwlink.com> wrote in message
> news:%23mUrrioKGHA.1124@.TK2MSFTNGP10.phx.gbl...
>> Synchronous is the opposite of asynchronous. It means that when you
>> execute a synchronous operation (as most are) the application is blocked
>> until the operation is completed. When you execute BeginExecuteReader,
>> only the _query_ is executed asynchronously. Not a single row has been
>> returned from the query when ADO.NET signals that the asynchronous
>> operation is complete. You still have to execute Read or Load to return
>> the rows. As you do, your application is blocked until the last row is
>> returned (Load method).
>> --
>> ____________________________________
>> William (Bill) Vaughn
>> Author, Mentor, Consultant
>> Microsoft MVP
>> INETA Speaker
>> www.betav.com/blog/billva
>> www.betav.com
>> Please reply only to the newsgroup so that others can benefit.
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> __________________________________
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:eiN02bkKGHA.1192@.TK2MSFTNGP11.phx.gbl...
>> Hmm, regarding async in ADO.NET 2.0. I'm trying to understand exactly
>> what it is that is synchronous. Are you saying that I cannot read any
>> rows until all rows has been returned? I.e., a FAST hint in the SQL
>> query would be of no advantage, but probably lead to higher resource
>> utilization and slower response time (depending on the plans of course)?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "William (Bill) Vaughn" <billvaRemoveThis@.nwlink.com> wrote in message
>> news:OSG$1UcKGHA.3272@.tk2msftngp13.phx.gbl...
>> Good answers all.
>> Just be aware that ADO.NET 2.0 async ops don't work like ADO classic.
>> ADO.NET only _executes_ the query async--the row-fetch operation is
>> synchronous unless you write your own backgroundworker thread routine
>> to handle it.
>> MARS? Just leave it alone--you won't need it for 90% of the things
>> needed to be done.
>> --
>> ____________________________________
>> William (Bill) Vaughn
>> Author, Mentor, Consultant
>> Microsoft MVP
>> INETA Speaker
>> www.betav.com/blog/billva
>> www.betav.com
>> Please reply only to the newsgroup so that others can benefit.
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> __________________________________
>> "Griff" <howling@.the.moon> wrote in message
>> news:%23lKiyuKKGHA.3936@.TK2MSFTNGP12.phx.gbl...
>> Two questions relating to this:
>> 1 - Do I require SQL Server 2005 or can this work with SQL Server
>> 2000?
>> 2 - My ASP.NET book says that I require MDAC 9.0. My registry setting
>> says that I'm using 2.8 but I can't find where I can download an
>> update from.
>> Thanks if you can help with either of these questions.
>> Griff
>>
>>
>>
>|||Pablo, thanks a lot for adding these comments. Your article was especially
informative.
--
Dan Guzman
SQL Server MVP
"Pablo Castro [MS]" <pablocas@.online.microsoft.com> wrote in message
news:e$fR4z2KGHA.1312@.TK2MSFTNGP09.phx.gbl...
> I'd like to put some clarification and to confirm some of the comments in
> the thread, and then point to some resources. I'm assuming that since
> we're in the ADO.NET newsgroup, this is about ADO.NET in particular :)
> - As several folks pointed out, MARS and asynchronous command execution
> are different, unrelated things (they can be used to together, but that's
> another story)
> - Asynchronous command execution works against all versions of SQL Server
> that ADO.NET can talk to (7.0 and later). You get the same functionality
> against all of them.
> - Asynchronous command execution is *not* the same as old ADO's
> asynchronous execution, and it's *not* the same as using an asynchronous
> delegate (BegingInvoke) or the thread-pool.
> - Asynchronous command execution is entirely implemented on the client
> interfaces, not in the server. It's completely unrelated to OPTION(FAST x)
> or FASTFIRSTROW
> A few years ago, in the first beta of ADO.NET 2.0 and early pre-release
> versions of SQL Server, there used to be something called MDAC 9.0. That
> thing does not exist any more. ADO.NET is now self-contained, so you don't
> need any external libraries in order to use asynchronous command
> execution, MARS, or any other SQL Server feature from ADO.NET.
> For more information about asynchronous command execution you can read:
> http://msdn.microsoft.com/library/en-us/dnvs05/html/async2.asp
> --
> Pablo Castro
> Program Manager - ADO.NET Team
> Microsoft Corp.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:OUF5hLpKGHA.3732@.TK2MSFTNGP10.phx.gbl...
>> Synchronous is the opposite of asynchronous.
>> Yes, I wrote synchronous in the sense that you implied that the new
>> "asynchronous" features of ADO.NET 2.0 are not truly asynchronous. I.e.,
>> I was wondering which parts were still synchronous.
>>
>> You still have to execute Read or Load to return the rows. As you do,
>> your application is blocked until the last row is returned (Load
>> method).
>> Which answers my question :-). You cannot read the rows and present them
>> "as they come". I.e., the app behavior will be more like QA grid mode
>> (synchronous behavior) compared to text mode (truly asynchronous
>> behavior).
>> I have a feeling that the SQL Server FAST hint is sometimes overused. The
>> developer think that he/she can gain something by using these hints:
>> "I'll present the first few rows immediately", where very few
>> applications/API/programmers actually program in that sense. And when SQL
>> Server receives a FAST hint, the overall resource utilization can be
>> significantly higher (using a non-clustered index for an ORDER BY over a
>> large set, for instance).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "William (Bill) Vaughn" <billvaRemoveThis@.nwlink.com> wrote in message
>> news:%23mUrrioKGHA.1124@.TK2MSFTNGP10.phx.gbl...
>> Synchronous is the opposite of asynchronous. It means that when you
>> execute a synchronous operation (as most are) the application is blocked
>> until the operation is completed. When you execute BeginExecuteReader,
>> only the _query_ is executed asynchronously. Not a single row has been
>> returned from the query when ADO.NET signals that the asynchronous
>> operation is complete. You still have to execute Read or Load to return
>> the rows. As you do, your application is blocked until the last row is
>> returned (Load method).
>> --
>> ____________________________________
>> William (Bill) Vaughn
>> Author, Mentor, Consultant
>> Microsoft MVP
>> INETA Speaker
>> www.betav.com/blog/billva
>> www.betav.com
>> Please reply only to the newsgroup so that others can benefit.
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> __________________________________
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:eiN02bkKGHA.1192@.TK2MSFTNGP11.phx.gbl...
>> Hmm, regarding async in ADO.NET 2.0. I'm trying to understand exactly
>> what it is that is synchronous. Are you saying that I cannot read any
>> rows until all rows has been returned? I.e., a FAST hint in the SQL
>> query would be of no advantage, but probably lead to higher resource
>> utilization and slower response time (depending on the plans of
>> course)?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "William (Bill) Vaughn" <billvaRemoveThis@.nwlink.com> wrote in message
>> news:OSG$1UcKGHA.3272@.tk2msftngp13.phx.gbl...
>> Good answers all.
>> Just be aware that ADO.NET 2.0 async ops don't work like ADO classic.
>> ADO.NET only _executes_ the query async--the row-fetch operation is
>> synchronous unless you write your own backgroundworker thread routine
>> to handle it.
>> MARS? Just leave it alone--you won't need it for 90% of the things
>> needed to be done.
>> --
>> ____________________________________
>> William (Bill) Vaughn
>> Author, Mentor, Consultant
>> Microsoft MVP
>> INETA Speaker
>> www.betav.com/blog/billva
>> www.betav.com
>> Please reply only to the newsgroup so that others can benefit.
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> __________________________________
>> "Griff" <howling@.the.moon> wrote in message
>> news:%23lKiyuKKGHA.3936@.TK2MSFTNGP12.phx.gbl...
>> Two questions relating to this:
>> 1 - Do I require SQL Server 2005 or can this work with SQL Server
>> 2000?
>> 2 - My ASP.NET book says that I require MDAC 9.0. My registry
>> setting says that I'm using 2.8 but I can't find where I can download
>> an update from.
>> Thanks if you can help with either of these questions.
>> Griff
>>
>>
>>
>
>|||> - Asynchronous command execution is entirely implemented on the client interfaces, not in the
> server. It's completely unrelated to OPTION(FAST x) or FASTFIRSTROW
I think I having problems getting my point across. My point is that a developer might think:
"Asynchronous... Great. I can now submit my query using a FAST hint and read and present the rows to
the user as SQL Server output the rows in its out buffer. The user will see rows immediately and
doesn't have to wait for the whole set to be returned."
And this is not how ADO.NET asynchronous execution work. Unless there is some undocumented
AsyncGetRecord method.
So, the point is that the cost for the query can be *significant* higher using a FAST hint, and it
is sad if someone uses such hint and not only is the response time for that operation slower, the
resource consumption on the server is higher. In below example, the query with the FAST hint is uses
300 *times* more I/O:
USE AdventureWorks
SET STATISTICS IO ON
SELECT OrderQty, SalesOrderId, ProductID
FROM Sales.SalesOrderDetail
ORDER BY ProductID
--1241 I/O
SET STATISTICS IO ON
SELECT OrderQty, SalesOrderId, ProductID
FROM Sales.SalesOrderDetail
ORDER BY ProductID
OPTION(FAST 10)
--371771 I/O
This is not really an ADO.NET issue, it is only a caution of using the FAST hint, unless you program
in an environment which allow you to read the rows as they arrive in the input buffer (like ODBC
does). Oh, and btw, thanks for the info, enlightening. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Pablo Castro [MS]" <pablocas@.online.microsoft.com> wrote in message
news:e$fR4z2KGHA.1312@.TK2MSFTNGP09.phx.gbl...
> I'd like to put some clarification and to confirm some of the comments in the thread, and then
> point to some resources. I'm assuming that since we're in the ADO.NET newsgroup, this is about
> ADO.NET in particular :)
> - As several folks pointed out, MARS and asynchronous command execution are different, unrelated
> things (they can be used to together, but that's another story)
> - Asynchronous command execution works against all versions of SQL Server that ADO.NET can talk to
> (7.0 and later). You get the same functionality against all of them.
> - Asynchronous command execution is *not* the same as old ADO's asynchronous execution, and it's
> *not* the same as using an asynchronous delegate (BegingInvoke) or the thread-pool.
> - Asynchronous command execution is entirely implemented on the client interfaces, not in the
> server. It's completely unrelated to OPTION(FAST x) or FASTFIRSTROW
> A few years ago, in the first beta of ADO.NET 2.0 and early pre-release versions of SQL Server,
> there used to be something called MDAC 9.0. That thing does not exist any more. ADO.NET is now
> self-contained, so you don't need any external libraries in order to use asynchronous command
> execution, MARS, or any other SQL Server feature from ADO.NET.
> For more information about asynchronous command execution you can read:
> http://msdn.microsoft.com/library/en-us/dnvs05/html/async2.asp
> --
> Pablo Castro
> Program Manager - ADO.NET Team
> Microsoft Corp.
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:OUF5hLpKGHA.3732@.TK2MSFTNGP10.phx.gbl...
>> Synchronous is the opposite of asynchronous.
>> Yes, I wrote synchronous in the sense that you implied that the new "asynchronous" features of
>> ADO.NET 2.0 are not truly asynchronous. I.e., I was wondering which parts were still synchronous.
>>
>> You still have to execute Read or Load to return the rows. As you do, your application is
>> blocked until the last row is returned (Load method).
>> Which answers my question :-). You cannot read the rows and present them "as they come". I.e.,
>> the app behavior will be more like QA grid mode (synchronous behavior) compared to text mode
>> (truly asynchronous behavior).
>> I have a feeling that the SQL Server FAST hint is sometimes overused. The developer think that
>> he/she can gain something by using these hints: "I'll present the first few rows immediately",
>> where very few applications/API/programmers actually program in that sense. And when SQL Server
>> receives a FAST hint, the overall resource utilization can be significantly higher (using a
>> non-clustered index for an ORDER BY over a large set, for instance).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "William (Bill) Vaughn" <billvaRemoveThis@.nwlink.com> wrote in message
>> news:%23mUrrioKGHA.1124@.TK2MSFTNGP10.phx.gbl...
>> Synchronous is the opposite of asynchronous. It means that when you execute a synchronous
>> operation (as most are) the application is blocked until the operation is completed. When you
>> execute BeginExecuteReader, only the _query_ is executed asynchronously. Not a single row has
>> been returned from the query when ADO.NET signals that the asynchronous operation is complete.
>> You still have to execute Read or Load to return the rows. As you do, your application is
>> blocked until the last row is returned (Load method).
>> --
>> ____________________________________
>> William (Bill) Vaughn
>> Author, Mentor, Consultant
>> Microsoft MVP
>> INETA Speaker
>> www.betav.com/blog/billva
>> www.betav.com
>> Please reply only to the newsgroup so that others can benefit.
>> This posting is provided "AS IS" with no warranties, and confers no rights.
>> __________________________________
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:eiN02bkKGHA.1192@.TK2MSFTNGP11.phx.gbl...
>> Hmm, regarding async in ADO.NET 2.0. I'm trying to understand exactly what it is that is
>> synchronous. Are you saying that I cannot read any rows until all rows has been returned? I.e.,
>> a FAST hint in the SQL query would be of no advantage, but probably lead to higher resource
>> utilization and slower response time (depending on the plans of course)?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "William (Bill) Vaughn" <billvaRemoveThis@.nwlink.com> wrote in message
>> news:OSG$1UcKGHA.3272@.tk2msftngp13.phx.gbl...
>> Good answers all.
>> Just be aware that ADO.NET 2.0 async ops don't work like ADO classic. ADO.NET only _executes_
>> the query async--the row-fetch operation is synchronous unless you write your own
>> backgroundworker thread routine to handle it.
>> MARS? Just leave it alone--you won't need it for 90% of the things needed to be done.
>> --
>> ____________________________________
>> William (Bill) Vaughn
>> Author, Mentor, Consultant
>> Microsoft MVP
>> INETA Speaker
>> www.betav.com/blog/billva
>> www.betav.com
>> Please reply only to the newsgroup so that others can benefit.
>> This posting is provided "AS IS" with no warranties, and confers no rights.
>> __________________________________
>> "Griff" <howling@.the.moon> wrote in message news:%23lKiyuKKGHA.3936@.TK2MSFTNGP12.phx.gbl...
>> Two questions relating to this:
>> 1 - Do I require SQL Server 2005 or can this work with SQL Server 2000?
>> 2 - My ASP.NET book says that I require MDAC 9.0. My registry setting says that I'm using
>> 2.8 but I can't find where I can download an update from.
>> Thanks if you can help with either of these questions.
>> Griff
>>
>>
>>
>