Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2004

    Unanswered: ODBC Inserts are very slow


    I am new to the windows world. We use Informatica on UNIX for ETL process. We have a requirement to load approx. 200,000 rows to a MS SQL Server table . The table is not that big and it is a heap table (no indexes). Inserts are taking 69 rows/per minute. We are using DataDirect Closed 4.10 SQL Server ODBC driver.

    SQL Profiler tells us that is is doing a row by row processing and using sp_execute procedure.

    Is there a way we can speed up the ODBC process?

    -Thanks in advance

    SQL Server Version:
    Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

  2. #2
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    That would appear to be an older version of Informatica (pre-2003 installation). There was a problem that was fixed late in the year (2003-09 I think).

    As a work-around, have Informatica generate a flat text file (tab delimited is my preference, columnar is often simplest, CSV is tolerable). FTP that file to your NT machine, then use DTS or BCP to load the file into SQL Server. You should have no trouble getting 10000 rows per minute from a mid-range workstation, and could get 1000000 rows per minute on a sufficiently beefy server.


  3. #3
    Join Date
    Aug 2002
    If possible use DTS in SQL server which used BULK LOAD process for such load and for further information refer to the books online or website.
    --Satya SKJ
    Microsoft SQL Server MVP

  4. #4
    Join Date
    Dec 2004
    We performed a test with Informatica 7.x and same odbc drivers and it 4 times faster. --Thanks

Posting Permissions

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