cancelling SQL queries

Discussion in 'ASP General' started by Gary, Jul 10, 2003.

  1. Gary

    Gary Guest

    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

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

    Thanks again for any advice,

    -Gary
    Gary, Jul 10, 2003
    #1
    1. Advertising

  2. 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.
    Andrew Durstewitz, Jul 10, 2003
    #2
    1. Advertising

  3. Gary

    Gary Guest

    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" <> wrote in message
    news:3f0da310$0$200$...
    > 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, Jul 10, 2003
    #3
  4. Gary

    Bob Barrows Guest

    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,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
    >
    Bob Barrows, Jul 10, 2003
    #4
  5. 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

    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" <> wrote in message
    > news:3f0da310$0$200$...
    >
    >>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.

    >
    >
    >
    Shailesh Humbad, Jul 10, 2003
    #5
  6. Gary

    Bob Barrows Guest

    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
    Bob Barrows, Jul 11, 2003
    #6
  7. Gary

    Gary Guest

    "Bob Barrows" <> wrote in message
    news:...
    > 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, Jul 11, 2003
    #7
  8. Gary

    Gary Guest

    "Bob Barrows" <> wrote in message
    news:...
    > 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
    Gary, Jul 11, 2003
    #8
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Bob
    Replies:
    4
    Views:
    4,964
    S. Justin Gengo
    Jul 22, 2003
  2. del

    Cancelling a HTTP data post

    del, Nov 7, 2003, in forum: ASP .Net
    Replies:
    1
    Views:
    327
  3. Ian Roddis

    xslt queries in xml to SQL queries

    Ian Roddis, Feb 26, 2006, in forum: Python
    Replies:
    3
    Views:
    1,498
    Crutcher
    Feb 26, 2006
  4. ecoolone
    Replies:
    0
    Views:
    755
    ecoolone
    Jan 3, 2008
  5. Abby Lee

    so many queries within queries I'm confused

    Abby Lee, Aug 4, 2004, in forum: ASP General
    Replies:
    11
    Views:
    351
    Aaron [SQL Server MVP]
    Aug 6, 2004
Loading...

Share This Page