none
SSIS works faster with SQL as compare to other DB

    Question

  • Hi,

    I am using SSIS with SQL Server and Vertica. I am performing my test with 1.2 Million Rows. Driver is ADO.NET and no transformation is done in my test. I am only moving data from source to destination.

    I recently found when I use source and destination as SQL, it took 19 seconds to move all data from source to destination but when I use Vertica as source and destination, it took 86 Seconds.

    Can someone guide me about how to find out the reason that is making this transmission slow and how to improve the performance when using source and destination as Vertica.

    Thanks

    Friday, April 21, 2017 9:06 AM

Answers

  • Thanks COZYROC for responding. So by that logic does it mean that SSIS is inserting in bulk in SQL server while it is inserting row by row in Vertica DB?

    I tried changing destination to BulkTransfer but it gave me same performance which means that even though we change BulkTransfer parameter value to True in SSIS with Vertica, it didn't take effect.

    Vikas,

    You have to start using the correct terminology. The destination component is the one responsible in what mode the data is being inserted bulk or row by row. I'm not exactly sure where you set the BulkTransfer parameter value, but if you are not seeing performance difference, this means the data is still inserted row by row.


    SSIS Tasks Components Scripts Services | http://wwww.cozyroc.com/

    • Marked as answer by VikasGarg Monday, April 24, 2017 11:12 AM
    Monday, April 24, 2017 11:00 AM

All replies

  • Hi,

    I would recommend you check the commercial COZYROC ODBC Destination component. It supports the Vertica database and provides 400x better performance compared to regular insert.


    SSIS Tasks Components Scripts Services | http://wwww.cozyroc.com/

    Friday, April 21, 2017 3:41 PM
  • Hi,

    Thanks COZYROC, for responding my thread.

    I have installed Vertica Client Driver for SSIS which also allows me to make connection in SSIS with Vertica.

    Now after I make my connection, I have compared the performance between SSIS with Vertica & SSIS with SQL.

    I found SSIS is working faster with SQL Server.

    Is it the architecture of SSIS behind this performance variation?

    Thanks;

    Vikas Garg

    Monday, April 24, 2017 6:00 AM
  • Vikas,

    It is not the architecture of the SSIS behind the performance difference. It is the SSIS destination component you use to load data. The regular SSIS component you use only inserts rows one-by-one. COZYROC's ODBC Destination component can insert records in bulk. That's why there is a performance difference.


    SSIS Tasks Components Scripts Services | http://wwww.cozyroc.com/

    Monday, April 24, 2017 10:37 AM
  • Thanks COZYROC for responding. So by that logic does it mean that SSIS is inserting in bulk in SQL server while it is inserting row by row in Vertica DB?

    I tried changing destination to BulkTransfer but it gave me same performance which means that even though we change BulkTransfer parameter value to True in SSIS with Vertica, it didn't take effect.

    Monday, April 24, 2017 10:56 AM
  • Thanks COZYROC for responding. So by that logic does it mean that SSIS is inserting in bulk in SQL server while it is inserting row by row in Vertica DB?

    I tried changing destination to BulkTransfer but it gave me same performance which means that even though we change BulkTransfer parameter value to True in SSIS with Vertica, it didn't take effect.

    Vikas,

    You have to start using the correct terminology. The destination component is the one responsible in what mode the data is being inserted bulk or row by row. I'm not exactly sure where you set the BulkTransfer parameter value, but if you are not seeing performance difference, this means the data is still inserted row by row.


    SSIS Tasks Components Scripts Services | http://wwww.cozyroc.com/

    • Marked as answer by VikasGarg Monday, April 24, 2017 11:12 AM
    Monday, April 24, 2017 11:00 AM
  • Thanks :)
    Monday, April 24, 2017 11:13 AM