Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2003
    Posts
    3

    Unanswered: Inserts performance

    Hello.
    I'm trying to optimize a process that synchronizes some proprietary objects to a database. Currently, this synchronization is made via ADO by using SQL statements. There are two differents actions that are made: the first initialization is made by a lot of INSERTs into different tables and the second operation is inserting/updating/removing objects one by one when it has changed.
    Our objects are stored across multiple tables because they have a hierarchical structure.
    My question is about performance for the first operation : what is the best way to achieve multiple inserts across multiple tables in the shortest time. We actually use ADO and an INSERT statement for each row which is obviously not the preferred method.
    Thanks.

  2. #2
    Join Date
    Mar 2003
    Location
    N Delhi
    Posts
    11
    One method you can try is:: Define cluster indexes on each table such that new inserts are distributed on multiple pages. Not only on last page.
    Multiple inserts on last page will slow down ur speed.

  3. #3
    Join Date
    Apr 2003
    Posts
    3
    Thanks, but i was thinking about client methods to improve the performance of inserts such as batches. But i've got no knowledge of advanced methods. The actual problem is that one request is sent by INSERT of a row and we have roughly 1 million inserts to do.

    Originally posted by avneesh
    One method you can try is:: Define cluster indexes on each table such that new inserts are distributed on multiple pages. Not only on last page.
    Multiple inserts on last page will slow down ur speed.

  4. #4
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    1. SET NOCOUNT ON
    removes useless "(1 row(s) affected)" messages (server feedback)

    2. Create SPs for the insert
    SPs are precompiled and therefore faster.

Posting Permissions

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