BULK INSERT to SQL server
Posted: Thu May 15, 2008 12:16 pm
I have 43 gig file to be loaded to a table in SQL server 2005. There are about 2 billion records in there. Its a one-one mapping with no transformation. There are no keys on the table.
Using DSR and ODBC Stages the performace on it was very poor. The maximum that DS could do was around 2000 - 3000 rows per sec. And I am talking about 2 billion records to be loaded to a single table in less than 24 hours. This is just one load, but the biggest one. Hence I started using BULK INSERT of SQL server to bulk load data to the tables. The Query would be as below
BULK INSERT STG_FINANCIAL_COLLECTION_DATA
FROM #source file name#
WITH (FORMATFILE=#format file name#, BATCHSIZE=200000,ROWS_PER_BATCH=2000000000)
BATCHSIZE=200000 should commit the records in batches of 200000 and also clear the log. I ran this at the backend and found that after 200000 teh records get commited and log get cleared. But when the same query is run through DS using Dynamic RDBMS stage or the ODBC stage, we do not see the commits happening. The log gets filled up in teh first half hour, the load fails the "Rollback" kicks in. The same happens when the query is wrapped in a Stored Proc.
I was hoping to figure out why DS / DataDirect 5.2 SQL Server Wire Protocol is unable to process the commits in batches? The same query works through SQL Query Analyser, which is the client that we use for SQL server.
Using DSR and ODBC Stages the performace on it was very poor. The maximum that DS could do was around 2000 - 3000 rows per sec. And I am talking about 2 billion records to be loaded to a single table in less than 24 hours. This is just one load, but the biggest one. Hence I started using BULK INSERT of SQL server to bulk load data to the tables. The Query would be as below
BULK INSERT STG_FINANCIAL_COLLECTION_DATA
FROM #source file name#
WITH (FORMATFILE=#format file name#, BATCHSIZE=200000,ROWS_PER_BATCH=2000000000)
BATCHSIZE=200000 should commit the records in batches of 200000 and also clear the log. I ran this at the backend and found that after 200000 teh records get commited and log get cleared. But when the same query is run through DS using Dynamic RDBMS stage or the ODBC stage, we do not see the commits happening. The log gets filled up in teh first half hour, the load fails the "Rollback" kicks in. The same happens when the query is wrapped in a Stored Proc.
I was hoping to figure out why DS / DataDirect 5.2 SQL Server Wire Protocol is unable to process the commits in batches? The same query works through SQL Query Analyser, which is the client that we use for SQL server.