SQL Server Performance

Asynchronous fetch in ADO

Discussion in 'General Developer Questions' started by Chappy, Aug 19, 2003.

  1. Chappy New Member

    I have a large stored procedure which among other things, runs several other stored procedures. The proc takes about 3 minutes to run, and so I want to provide feedback to the user at certain key points in the procedure.

    I have set up an asynchronous ADO query which attempts to display records as they are retrieved. However, I think sql is buffering the whole response until the stored procedure ends, and then the async processing kicks in while its fetching over the network.

    To explain better:

    CREATE PROCEDURE dbo.TEST_ASYNC
    AS
    BEGIN
    SELECT 1
    WAITFOR DELAY '00:00:02'
    SELECT 2
    WAITFOR DELAY '00:00:02'
    SELECT 3
    WAITFOR DELAY '00:00:02'
    SELECT 4
    WAITFOR DELAY '00:00:02'
    SELECT 5
    END
    GO

    Is there any way ADO can retrieve the recordset returned by SELECT 1, before the stored procedure has completed ?

    Thanks
  2. Syd New Member

    actually no way<br />the best you can do is to show 'Please wait...' or some avi-file <img src='/community/emoticons/emotion-1.gif' alt=':)' />
  3. SQL_Guess New Member

    What about trying something ugly like ?<br /><br />CREATE PROCEDURE dbo.TEST_ASYNC<br />AS<br />BEGIN<br />insert into poll_select1_resutls<br />SELECT 1<br />WAITFOR DELAY '00:00:02'<br />insert into poll_select2_resutls<br />SELECT 2<br />WAITFOR DELAY '00:00:02'<br />insert into poll_select3_resutls<br />SELECT 3<br />WAITFOR DELAY '00:00:02'<br />insert into poll_select4_resutls<br />SELECT 4<br />WAITFOR DELAY '00:00:02'<br />insert into poll_select5_resutls<br />SELECT 5<br />END<br />GO<br /><br />And then have a seperate process that "poll's" those tables, looking to seeing if they've been inserted, and responding based on that.<br /><br />I know, I know ... it is a supremely UGLY way , and I don't really want to think about the performance, but depending on the requirements, it is sometimes more productive for a users to get partial results now, as part of a longer process, than waiting for a transaction to totally complete before they can do anythig.<br /><br />Now that I've thrown that ugly thought at you, someone else can say how ugly it is, and come up with a real solution [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  4. gaurav_bindlish New Member

    The problem here is that SQL server waits for the output buffer to be filled before it sends any records to client.

    There are two solutions to this problem-

    1. Use GO between the batches so the results will come as different resultsets. The output buffer is flushed as soon as GO is found in the statements. OFcourse u'll have to have multiple recordsets in client application.

    2. Use FAST n hint in the select query. This will send some of the data to client application and won't wait for the whole batch to finish. I am not sure of this solution but it's worth a try.

    Don't forget to post the solution.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  5. Argyle New Member

  6. Chappy New Member

    Thanks for the ideas guys. Ill let you know how I get on

Share This Page