Asynchrous query

I have a query that because the database is across the country it takes time
to bring a large amount of data to the client executing a command. I was
wondering if there was a way to execute an asychronus command? Ideally I
would like to get periodic feedback on the progress. I am sure that most of
the delay is network related but it is hard to tell how close to finishing a
command is by just executing the ExecuteScalar from a SQL command.
Thank you for your suggestions.
KevinOn Sat, 8 Jul 2006 16:25:01 -0700, Kevin Burton
<KevinBurton@.discussions.microsoft.com> wrote:
>I have a query that because the database is across the country it takes time
>to bring a large amount of data to the client executing a command. I was
>wondering if there was a way to execute an asychronus command? Ideally I
>would like to get periodic feedback on the progress. I am sure that most of
>the delay is network related but it is hard to tell how close to finishing a
>command is by just executing the ExecuteScalar from a SQL command.
>Thank you for your suggestions.
Easiest way is probably to create the extra thread in your app code.
Don't know any standard way of monitoring progress.
If you program down at the ODBC/dblib level (is that still even
possible on SQL2005?), with server-side cursors, then you can monitor
progress. But it's a lot of work! Hmm, I suppose, in that extra
thread of yours, you could use server-side cursors and monitor it even
at the OLEDB level!
What language is your client written in?
(Does OLEDB support async queries? I don't know, I've been writing
nothing but TSQL for too long now!)
Josh|||ADO.NET 2.0 has asynchronous operations, like BeginExecuteReader and EndExecuteReader, etc. Doesn't
seem to be a BeginExecuteScalar, though.
But you should carefully consider in what way you want the execution to be asynchronous.
If you use any of above mentioned methods, you cannot read off of the results until the whole set
has been passed to the client.
Using a regular ExecuteReader, you can read the results as they are sent to the client. You don't
have to wait for the whole set until you can present data to the end user. Note that SQL Server can
use worktables and don't send the result until it is done, depending on the execution plan. A FAST
hint can be useful, but that can increase the total query cost (sometimes radically).
Above doesn't matter for a ExecuetScalar, of course, but it can be important when reading a number
of rows.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kevin Burton" <KevinBurton@.discussions.microsoft.com> wrote in message
news:1417C3D0-681F-4DC0-9F4F-38DB3C2F8DCE@.microsoft.com...
>I have a query that because the database is across the country it takes time
> to bring a large amount of data to the client executing a command. I was
> wondering if there was a way to execute an asychronus command? Ideally I
> would like to get periodic feedback on the progress. I am sure that most of
> the delay is network related but it is hard to tell how close to finishing a
> command is by just executing the ExecuteScalar from a SQL command.
> Thank you for your suggestions.
> Kevin|||Thank you for the tip. My client is written in .NET. I guess I was hoping
for some access to the network as I indicated before the physical database
resides a long way away so I am suspecting that the actual query is probably
quick enough it is just tranferring the data across the network that takes
the time. The thing of it is that I what the code somehow to adjust for the
long distance data transfer. I can't do that as far as I can see without
knowing about the network being used to transfer the data. Anyway, thanks
again. If the bottle neck does in fact prove to be the SQL query then I will
look into your suggestions.
Kevin|||I started to look at these calls and it brought up another question. The data
that is being read is a column with XML data. It is typed 'text' and the
server is running 2000 so I don't have an option to type the column as XML. I
would however like to use ExecuteXmlReader but I get an exception indicating
that the data must be XML. How do I convince the client that it is XML data?
My current query looks like:
SELECT XMLSent FROM dbo.OutboundTable WHERE documentTransactionID=xxx AND
OrderNumber='yyy'
It seems silly to read in a large chunk of "string" data, pass it to a
MemoryStream, build an XmlReader, and then use that to build an XML document
(specifically an XPath document because I want to do some XPath searches on
the data). If I can get an XmlReader directly from the SQL query via
ExecuteXmlReader it seems more efficient. Is this possible with the data
structured as I indicated?
Thank you.
Kevin
"Tibor Karaszi" wrote:
> ADO.NET 2.0 has asynchronous operations, like BeginExecuteReader and EndExecuteReader, etc. Doesn't
> seem to be a BeginExecuteScalar, though.
> But you should carefully consider in what way you want the execution to be asynchronous.
> If you use any of above mentioned methods, you cannot read off of the results until the whole set
> has been passed to the client.
> Using a regular ExecuteReader, you can read the results as they are sent to the client. You don't
> have to wait for the whole set until you can present data to the end user. Note that SQL Server can
> use worktables and don't send the result until it is done, depending on the execution plan. A FAST
> hint can be useful, but that can increase the total query cost (sometimes radically).
> Above doesn't matter for a ExecuetScalar, of course, but it can be important when reading a number
> of rows.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Kevin Burton" <KevinBurton@.discussions.microsoft.com> wrote in message
> news:1417C3D0-681F-4DC0-9F4F-38DB3C2F8DCE@.microsoft.com...
> >I have a query that because the database is across the country it takes time
> > to bring a large amount of data to the client executing a command. I was
> > wondering if there was a way to execute an asychronus command? Ideally I
> > would like to get periodic feedback on the progress. I am sure that most of
> > the delay is network related but it is hard to tell how close to finishing a
> > command is by just executing the ExecuteScalar from a SQL command.
> >
> > Thank you for your suggestions.
> >
> > Kevin
>|||That is a bit too high in the API stack for me to be able to comment on. Perhaps FOR XML in the
query would allow you to type it as XML? I suggest you post this to an ADO.NET group, unless someone
jump in here with more specific recommendations.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kevin Burton" <KevinBurton@.discussions.microsoft.com> wrote in message
news:9342A66A-B585-4890-8E8B-719114510350@.microsoft.com...
>I started to look at these calls and it brought up another question. The data
> that is being read is a column with XML data. It is typed 'text' and the
> server is running 2000 so I don't have an option to type the column as XML. I
> would however like to use ExecuteXmlReader but I get an exception indicating
> that the data must be XML. How do I convince the client that it is XML data?
> My current query looks like:
> SELECT XMLSent FROM dbo.OutboundTable WHERE documentTransactionID=xxx AND
> OrderNumber='yyy'
> It seems silly to read in a large chunk of "string" data, pass it to a
> MemoryStream, build an XmlReader, and then use that to build an XML document
> (specifically an XPath document because I want to do some XPath searches on
> the data). If I can get an XmlReader directly from the SQL query via
> ExecuteXmlReader it seems more efficient. Is this possible with the data
> structured as I indicated?
> Thank you.
> Kevin
> "Tibor Karaszi" wrote:
>> ADO.NET 2.0 has asynchronous operations, like BeginExecuteReader and EndExecuteReader, etc.
>> Doesn't
>> seem to be a BeginExecuteScalar, though.
>> But you should carefully consider in what way you want the execution to be asynchronous.
>> If you use any of above mentioned methods, you cannot read off of the results until the whole set
>> has been passed to the client.
>> Using a regular ExecuteReader, you can read the results as they are sent to the client. You don't
>> have to wait for the whole set until you can present data to the end user. Note that SQL Server
>> can
>> use worktables and don't send the result until it is done, depending on the execution plan. A
>> FAST
>> hint can be useful, but that can increase the total query cost (sometimes radically).
>> Above doesn't matter for a ExecuetScalar, of course, but it can be important when reading a
>> number
>> of rows.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Kevin Burton" <KevinBurton@.discussions.microsoft.com> wrote in message
>> news:1417C3D0-681F-4DC0-9F4F-38DB3C2F8DCE@.microsoft.com...
>> >I have a query that because the database is across the country it takes time
>> > to bring a large amount of data to the client executing a command. I was
>> > wondering if there was a way to execute an asychronus command? Ideally I
>> > would like to get periodic feedback on the progress. I am sure that most of
>> > the delay is network related but it is hard to tell how close to finishing a
>> > command is by just executing the ExecuteScalar from a SQL command.
>> >
>> > Thank you for your suggestions.
>> >
>> > Kevin
>>