cancelling SQL queries

G

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.IsClientConnected 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.SubreportSQL(cstr(i)).SQLString
set rsts(i) = CreateObject("ADODB.Recordset")
strSQL = objRpt.ParseSQL(strSQL,strparamnames,strParamValues)
err.Clear
done = false
rsts(i).CursorType = 3 ' adOpenStatic
rsts(i).LockType = 1 ' adLockReadOnly
rsts(i).CursorLocation = 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.IsClientConnected then
rsts(i).cancel
Response.Write "<!-- cancelled -->" & vbcrlf
exit Do
end if
AspSleep(2)
Loop
next
 
A

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.
 
G

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
 
B

Bob Barrows

You need to use adAsyncFetch or adAsyncFetchNonBlocking 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', , _
adAsyncFetchNonBlocking + 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
 
S

Shailesh Humbad

See an example at:

http://www2.themanualpage.org/asp/asp_response_isclientconnected.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 adOpenForwardOnly cursor, which
would also be a bit faster.

Shailesh
 
B

Bob Barrows

Gary said:
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
 
G

Gary

Bob Barrows said:
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
 
G

Gary

Bob Barrows said:
You need to use adAsyncFetch or adAsyncFetchNonBlocking 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', , _
adAsyncFetchNonBlocking + adCmdText

I've tried adAsyncFetch, adAsyncFetchNonBlocking + 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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,482
Members
44,901
Latest member
Noble71S45

Latest Threads

Top