SQL Server Performance

ADO program not able to run Asynchronously with SQL

Discussion in 'ALL SQL SERVER QUESTIONS' started by Rich, Feb 7, 2012.

  1. Rich New Member

    Hi,

    I have an Real-Time application written in C++ (VS 2008) that must not wait around to finish the execution of a Stored Procedure executing Synchronously via ADO. So i have been trying to modify my application to use ADO's Asynchronous calls so that i can prepare the next piece of information and send that as soon as it is ready. I want this so i do not need to buffer or cause any delays, as the code is fast enough to send to the Database again before the previous send has completed.

    However, when i try to use the adAsyncExecute to use ADO in an Asynchronous mode i do not get the raw_ExecuteComplete event. In this case I only see this event when i close the connection, which what i think is wrong as it is not the same without the adAsyncExecute option.
    Also when i run the program i get one of the following errors:
    • Operation cannot be performed while connecting asynchronously.
    • The connection cannot be used to perform this operation. It is either closed or invalid in this context.
    I have search around on the Web and through the ADO Guide (which i found only in the MDAC 2.8 SDK), but have found related information but absolutely no answers to make it work Asynchonously.

    So can anyone please shed any light at all for me to successfully work with ADO Asynchronously.

    Many thanks!

    Rich.
  2. davidfarr Member

    C++ (VS 2008), 'adAsyncExecute' and the 'raw_ExecuteComplete' event are not terminologies inherently used by SQL server.
    You are more likely to find an answer on a C++ VS2008 developers forum, since this is not really a SQL Server question.
  3. Rich New Member

    Ok, thanks, i have posted there, but i now have a SQL related question...

    When issuing INSERTs to SQL Server 2008 R2, an Exclusive Lock is made on the table, correct? I understand this to mean that nothing else can use the table until the Insert is finished and the lock is no longer on the table. Would this also mean that i could not INSERT a 2nd row until the 1st row had finished??

    Therefore I have been looking at some SQL Hints and was wondering if i used the ROWLOCK Hint to only lock the Rows affected by the Transaction (a single row), would i be able to make multiple inserts?

    Thanks.
  4. davidfarr Member

    The ROWLOCK hint only applies to UPDATE and DELETE statements. It does not apply to INSERT statements.
    During an INSERT; only the rows that you are inserting are locked until your transaction completes. The entire table is not locked.
    Depending on your query syntax, you might be inserting one single row --Insert into MyTable values ('alpha',1) -- or you might be inserting multiple rows --Insert into MyTable Select field1, field2 from MyTable2--
    No matter if your query inserts one row or multiple rows; SQL server will process each inserted row one at a time until the transaction (your query) completes.
    SQL server will process each row one at a time because it needs to evaluate and execute the sequential auto-increment values, triggers and constraints that may exist on the table.

    It is also important to consider a database term called "atomicity". The success of your second insert statement (or second call to a stored proc) might depend on the success of your first. Would you really want to be cramming 2,3,4 transactions asychronously when the first transaction might have failed ? ....possibly messing up your entire process sequence because you did not wait for the result of the first one ?

Share This Page