Multiple Active Result Sets (MARS)

  • Thread starter Thread starter Griff
  • Start date Start date
G

Griff

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
 
Sorry - 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.....
Click to expand...

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.
 
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.
 
Griff said:
Two questions relating to this:

1 - Do I require SQL Server 2005 or can this work with SQL Server 2000?
Click to expand...

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.
Click to expand...

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!
***************************
 
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.
__________________________________
 
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)?
 
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.
__________________________________
 
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).
Click to expand...

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).
 
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.
 
Pablo, thanks a lot for adding these comments. Your article was especially
informative.
 
- Asynchronous command execution is entirely implemented on the client interfaces, not in the
server. It's completely unrelated to OPTION(FAST x) or FASTFIRSTROW
Click to expand...

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/
 
Back
Top