Writing to SQL - Very Slow

[AlterRexRyan]

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!

[gawa]

hi, @AlterRexRyan 

Found some article which might help you 
https://knowledge.alteryx.com/index/s/article/How-to-troubleshoot-slow-DB-speed

In addition to the workaround described in the above link;

Unnecessary(unused) index defined in SQL database should be all deleted. When writing to SQL DB, it always comes along with re-organization of the existing index, which makes writing speed slower as the number of index defined,

[caltang]

Not sure if it helps, but why not add an intermediate step between output and to DB? Write an output to YXDB, then crunch that YXDB in another workflow to connect to In-DB tools. Stream the YXDB data into SQL Server, and don't run anything else. That should help I think.

Beyond that, try @gawa's suggestions. 

[apathetichell]

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

[danilang]

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

[Tyler_Rooks]

@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!!