Results 1 to 3 of 3

Thread: ADO inserts running slow

  1. #1
    Shawn Elliott Guest

    ADO inserts running slow


    Hi list,
    I'm a long time lurker on this list and really enjoy the discussions, although I rarely get a chance to participate.

    Here is my situation: We are importing chunks of data (500 records at a time) from a C++ interface. The records have to be transformed before inserting into the target table which I am doing using a stored proc which is working fine. The records are in memory in C++ and the programmer is looping through the records building inserts into a temp table through ADO (which my proc picks up). The server business object is using the connection.execute method which is inserting one record at a time. That part of the process is taking over 15 seconds for 500 records which is the bulk of the total time.

    My question is: Using ADO is there a better way to insert these records into the temp table? I see mention of a recordset interface but my programmers are new to ADO and since I am the DBA and have never used ADO, I am not sure what to tell them.

    Any insight would be greatly appreciated.

    shawn

  2. #2
    Junjun Olympia Guest

    ADO inserts running slow (reply)

    Try using ADO's command and parameter objects to build "parameterized queries". They MAY improve performance and will make it easier for your programmers to code.
    As regards to using a Recordset object, I strongly discouraged doing so since this method opens up a cursor and may enforce a lock on the table you're using which could prevent other apps from using the same object.
    See MSDN for help on ADO.
    Good luck.


    ------------
    Shawn Elliott at 10/13/99 11:32:48 AM


    Hi list,
    I'm a long time lurker on this list and really enjoy the discussions, although I rarely get a chance to participate.

    Here is my situation: We are importing chunks of data (500 records at a time) from a C++ interface. The records have to be transformed before inserting into the target table which I am doing using a stored proc which is working fine. The records are in memory in C++ and the programmer is looping through the records building inserts into a temp table through ADO (which my proc picks up). The server business object is using the connection.execute method which is inserting one record at a time. That part of the process is taking over 15 seconds for 500 records which is the bulk of the total time.

    My question is: Using ADO is there a better way to insert these records into the temp table? I see mention of a recordset interface but my programmers are new to ADO and since I am the DBA and have never used ADO, I am not sure what to tell them.

    Any insight would be greatly appreciated.

    shawn

  3. #3
    Steve Camsell Guest

    ADO inserts running slow (reply)

    Shawn,

    I suggest using the ADO command object. Set the command type to stored proc. Then create an ADO parameter object for each field in your destination table. Append each parameter to the command object (command.append <parameter obj> and then the command object&#39;s prepared property to TRUE (this compiles and loads the SQL Stored proc into memory). You can now just repeatedly set the value of each parameter objects and call command.execute. This is the fastest way that I have found to repeatedly added records to SQL Server (I routinely import upwards of 500,000 from flat files into SQL.

    Depending upon the particular situation, you can always try Data Transformation Services as well.

    Steve.


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

    My question is: Using ADO is there a better way to insert these records into the temp table? I see mention of a recordset interface but my programmers are new to ADO and since I am the DBA and have never used ADO, I am not sure what to tell them.

    Any insight would be greatly appreciated.

    shawn

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •