Writing to SQL - Very Slow

Question

Hi All,

I have a workflow that writes millions of records to a SQL table (using the Delete Data and Append option, and OleDb Database file format). The very last step of writing to SQL often takes hours to complete.

Does anyone know of any ways to make that faster? From some preliminary research, I saw one person suggest writing to a temp table and then writing from that temp table to SQL using a post-SQL statement. As someone technically challenged, I've never had to do anything other than plainly writing to a SQL table with an output tool, so I'm a bit out of my element trying to figure that out.

So if anyone could provide some more insight into or resources to figure out the above approach, OR provide another potential solution for speeding up the write to SQL process, that'd be much appreciated!

Tyler_Rooks · Answer

@caltang

Can you elaborate on how to do this?  Perhaps tool by tool?

I'm not sure I follow how this will speed things up, but willing to give it a try.

Thanks!!

danilang · Answer

Hi @AlterRexRyan

The issue with using oleDB is that it writes using a very small batch size.  If you're using Update: Insert if New, it actually writes one record at a time.  If you have lots of data and are writing to a blank table, use the SQL bulk load option

This writes using configurable batch sizes and is much faster than the OleDB equivalent.  The only downsides are that you have to manage your primary key values in the workflow itself to ensure that you don't get violations and you need truncate permission on the table in question if you use Delete Data and Append

Dan

apathetichell · Answer

What kind of "SQL" are you writing to? How is your ODBC configured? Are you writing In-DB or via output data?