Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > cancelling SQL queries

Reply
Thread Tools

cancelling SQL queries

 
 
Gary
Guest
Posts: n/a
 
      07-10-2003
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,strParamValue s)
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

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

Thanks again for any advice,

-Gary


 
Reply With Quote
 
 
 
 
Andrew Durstewitz
Guest
Posts: n/a
 
      07-10-2003
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.
 
Reply With Quote
 
 
 
 
Gary
Guest
Posts: n/a
 
      07-10-2003
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" <(E-Mail Removed)> wrote in message
news:3f0da310$0$200$(E-Mail Removed)...
> 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.



 
Reply With Quote
 
Bob Barrows
Guest
Posts: n/a
 
      07-10-2003
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

Gary wrote:
> 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,strParamValue s)
> 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
>



 
Reply With Quote
 
Shailesh Humbad
Guest
Posts: n/a
 
      07-10-2003
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 adOpenForwardOnly 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" <(E-Mail Removed)> wrote in message
> news:3f0da310$0$200$(E-Mail Removed)...
>
>>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.

>
>
>


 
Reply With Quote
 
Bob Barrows
Guest
Posts: n/a
 
      07-10-2003
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


 
Reply With Quote
 
Gary
Guest
Posts: n/a
 
      07-10-2003

"Bob Barrows" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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


 
Reply With Quote
 
Gary
Guest
Posts: n/a
 
      07-11-2003
"Bob Barrows" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL Reference, SQL Queries, SQL help ecoolone ASP .Net 0 01-03-2008 10:58 AM
xslt queries in xml to SQL queries Ian Roddis Python 3 02-26-2006 06:49 PM
"Cancelling" more that one message at a time Perk Firefox 1 01-01-2005 05:38 AM
so many queries within queries I'm confused Abby Lee ASP General 11 08-06-2004 07:56 PM
Re: cancelling a postback on a button with javascript Bob ASP .Net 4 07-22-2003 07:10 PM



Advertisments