cancelling SQL queries

[Gary]

I'm using ASP (VB Script) to generate some reports from a SQL Server
database. These queries often take a significant amount of time to complete,
and many of these reports consist of multiple queries.

One of the biggest problems end users have with this is that if the report
generation is cancelled (e.g. hitting the stop button on the browser) SQL
Server continues to process the request, which usually renders the server
unresponsive until the query completes.

I tried using Response.IsClie ntConnected to detect whether the client is
still connected to the server, and if not, I call cancel on the RecordSet
object. Unfortunately, this doesn't seem to work, and I'm trying to
understand why. Any advice as to why this doesn't do what I expect would be
greatly appreciated.

Here is the code I'm executing:

for i = 0 to 20
strSQL = objRpt.Subrepor tSQL(cstr(i)).S QLString
set rsts(i) = CreateObject("A DODB.Recordset" )
strSQL = objRpt.ParseSQL (strSQL,strpara mnames,strParam Values)
err.Clear
done = false
rsts(i).CursorT ype = 3 ' adOpenStatic
rsts(i).LockTyp e = 1 ' adLockReadOnly
rsts(i).CursorL ocation = 3 ' adUseClient
rsts(i).Open strSQL,whconn', ,adAsyncConnect

Do
if err <> 0 then
for each ce in whconn.Errors
Response.Write "Error - " & ce.NativeError & " - " &
ce.Description & "<br>"
next
Response.End
else
if not done then
Response.Write " <!-- Success -->" & vbcrlf
done = true
end if
end if
if not Response.IsClie ntConnected then
rsts(i).cancel
Response.Write "<!-- cancelled -->" & vbcrlf
exit Do
end if
AspSleep(2)
Loop
next

-----------------------

Thanks again for any advice,

-Gary

[Andrew Durstewitz]

If i'm not mistaken you can't "multitread " ASP through the coding
language. Meaning stop something you started until you finish it.

I would suggest setting up a stored procedure and doing something
database wise to increase your speed. Most of these problems can be
overcome with good database design.

hth,
Andrew

* * * Sent via DevBuilder http://www.devbuilder.org * * *
Developer Resources for High End Developers.

[Gary]

According to the documentation on the RecordSet object, cancel is supposed
to cancel the execution of a pending Open call. I also tried calling the
RecordSet open function with the adAsyncConnect option, but I get an error
when I use it.

We use stored procedures all over the place. The queries are very optimized.
There is just lots of data to process and lots of joins.

Thanks for your suggestions.

-Gary

"Andrew Durstewitz" <ad******@devbu ilder.org> wrote in message
news:3f******** *************@n ews.frii.net...

If i'm not mistaken you can't "multitread " ASP through the coding
language. Meaning stop something you started until you finish it.

I would suggest setting up a stored procedure and doing something
database wise to increase your speed. Most of these problems can be
overcome with good database design.

hth,
Andrew

* * * Sent via DevBuilder http://www.devbuilder.org * * *
Developer Resources for High End Developers.

[Bob Barrows]

You need to use adAsyncFetch or adAsyncFetchNon Blocking with recordsets, not
adAsyncConnect.
You should also tell ADO what the CommandType is. In this case it looks like
adCmdText, so the Open statement should look like this:
rsts(i).Open strSQL,whconn', , _
adAsyncFetchNon Blocking + adCmdText

Is the reason for the long execution time that you are returning a lot of
records? Have you looked into a paging solution?
Can you move some of this processing into a stored procedure so you're not
creating so much network traffic?
Perhaps you need to look into warehousing some of this data if you don't
need up-to-the-second information in these reports ...

HTH,
Bob Barrows

Gary wrote:

Here is the code I'm executing:

for i = 0 to 20
strSQL = objRpt.Subrepor tSQL(cstr(i)).S QLString
set rsts(i) = CreateObject("A DODB.Recordset" )
strSQL = objRpt.ParseSQL (strSQL,strpara mnames,strParam Values)
err.Clear
done = false
rsts(i).CursorT ype = 3 ' adOpenStatic
rsts(i).LockTyp e = 1 ' adLockReadOnly
rsts(i).CursorL ocation = 3 ' adUseClient
rsts(i).Open strSQL,whconn', ,adAsyncConnect

[Shailesh Humbad]

See an example at:

http://www2.themanualpage.org/asp/as...connected.php3

I don't think you can cancel a SQL query by using methods of the
Recordset, because ASP doesn't support asynchronous event notifications.
But you can stop the execution of the script as described in the above
link. If you want to improve execution time on the ASP end, try reading
my article ASP Speed Tricks at http://www.somacon.com/aspdocs/ It
describes some methods to improve the speed of reports like the ones you
describe. If you're doing a read-only query and don't need recordset
navigation, then you probably want an adOpenForwardOn ly cursor, which
would also be a bit faster.

Shailesh

Gary wrote:

According to the documentation on the RecordSet object, cancel is supposed
to cancel the execution of a pending Open call. I also tried calling the
RecordSet open function with the adAsyncConnect option, but I get an error
when I use it.

We use stored procedures all over the place. The queries are very optimized.
There is just lots of data to process and lots of joins.

Thanks for your suggestions.

-Gary

"Andrew Durstewitz" <ad******@devbu ilder.org> wrote in message
news:3f******** *************@n ews.frii.net...

If i'm not mistaken you can't "multitread " ASP through the coding
language. Meaning stop something you started until you finish it.

I would suggest setting up a stored procedure and doing something
database wise to increase your speed. Most of these problems can be
overcome with good database design.

hth,
Andrew

* * * Sent via DevBuilder http://www.devbuilder.org * * *
Developer Resources for High End Developers.

[Bob Barrows]

Gary wrote:

It is all historical data, and is in fact a data warehouse (or
perhaps more precisely, a data mart).

So why are multiple queries involved? I'm assuming there are due to your use
of what looks like an array of recordsets.
Could you utilize a stored procedure that ran all the queries and returned
multiple recordsets?

Bob

[Gary]

"Bob Barrows" <re*******@yaho o.com> wrote in message
news:OX******** ******@TK2MSFTN GP10.phx.gbl...

So why are multiple queries involved? I'm assuming there are due to your use of what looks like an array of recordsets.
Could you utilize a stored procedure that ran all the queries and returned
multiple recordsets?

The reports being generated contain subreports, each of which is a separate
query. And actually the product is designed to allow custom reports, so the
queries can be altered dynamically. If they were static, a stored procedure
would probably make sense. In other words I don't write any of the reports,
I just maintain the engine for generating them. Report writers could, I
suppose, write stored procedures that I could execute, but I wasn't planning
to go that way. I guess if I could prove that we would see significant gains
by writing future reports as stored procedures, it would be worth pursuing.

Even if they ran in 10 minutes, it's annoying to have to either shutdown SQL
Server or wait until it finishes before another report can be run. So the
goal, however it happens, is to get SQL to stop (the equivalent of hitting
the cancel button in Query Analyzer).

Thanks again.

-Gary

[Gary]

"Bob Barrows" <re*******@yaho o.com> wrote in message
news:uf******** ******@tk2msftn gp13.phx.gbl...

You need to use adAsyncFetch or adAsyncFetchNon Blocking with recordsets, not adAsyncConnect.
You should also tell ADO what the CommandType is. In this case it looks like adCmdText, so the Open statement should look like this:
rsts(i).Open strSQL,whconn', , _
adAsyncFetchNon Blocking + adCmdText

I've tried adAsyncFetch, adAsyncFetchNon Blocking + adCmdText, and
adAsyncExecute, but none of them cause the call to recordset.open to behave
asynchronously.

I did see a MSKB article about an ADO bug where the open call may behave
synchronously even if adAsyncFetch is specified. In this case the example
code is VB, where the recordset is created using "WithEvents "

Private WithEvents rst As ADODB.Recordset

and I know the Event part of ADO is not supported by VBScript or JScript. So
I wonder if this is even possible from ASP.

-Gary