CSV File Reader -> Database Performance

[rjkroll]

Hi,

Can anyone share any proper tips or channel setups that would allow me to read in large CSV files (5-10GB) and insert those records into a SQL database?

I have a File Reader that is technically working, but even a smaller CSV (1GB) with 100k records seems to take an abnormally long time, so I am worried about the actual large files.

My settings are:

• File Reader
• Source Queue Off
• Response None
• Process Batch: On
• Processing Threads: 10

Source Transformer has the following Javascript code:

var sql = "INSERT INTO [dbo].[test_table] SELECT ";
var columnsExceptCreatedDate = 16;
var fieldSeperator = "',";
var union = " UNION ALL SELECT ";
var rows =msg..row;
for each (var row in rows){
for(var i = 1;i<=columnsExceptCreatedDate;i+=1){
sql += "'" + String(row['column' + i]).replace(/'/g, "''") + fieldSeperator;
}
sql += "'" + DateUtil.getCurrentDate('MM/dd/yyyy HH:mm:ss:SSS') + "'"
sql += union;
}
sql = sql.substring(0,sql.length-union.length)
tmp = sql;

var dbConn = DatabaseConnectionFactory.createDatabaseConnection('net.sourceforge.jtds.jdbc.Driver','jdbc:jtds:sqlserver)

dbConn.executeUpdate(sql);

I am doing this correctly or is there a more efficient way people are using when dealing with large CSVs?

[pacmano1]

My tip is don't use mirth for this. It's far to slow. Use native command line database utilities for large data imports and exports. That is bcp in your case with MSSQL.

[tonygermano]

To attempt to give a mirth answer, you're running into two bottle necks. First, e4x gets exponentially slower the more children you add to a single element (rows, in the case of delimited text.)

Your choices here are to (in increasing amounts of work)

• batch process the delimited text file
• read the entire file as a Raw message and parse the string (requires reading the entire file into memory at once, mirth attachments should probably be used)
• Switch to a Javascript Reader, scan the folder yourself, pass the path and filename as the message, then stream and parse the file yourself in the transformer.

Once you are able to get the data, it looks like it is possible to do batch inserts to speed up the process over inserting each row individually. See

• https://stackoverflow.com/questions/3784197/efficient-way-to-do-batch-inserts-with-jdbc
• https://docs.microsoft.com/en-us/sql/connect/jdbc/performing-batch-operations?view=sql-server-ver15

Additionally, the microsoft driver has an option for rewriting batch operations (with limitations) to use the Bulk Copy API. I imagine if your data supports this use case it would be even faster. https://docs.microsoft.com/en-us/sql/connect/jdbc/use-bulk-copy-api-batch-insert-operation?view=sql-server-ver15

If you are using mirth batch processing, you could store your database connection in the globalChannelMap between messages and build up your batch insert across multiple messages. Then you could execute the batch after so many rows and at the end of the mirth batch.