Importing too many records respectively in a database

Hi,
I have read a large number of files (with infringing formats and a number of different records) from one directory and I want to enter the obtained data (including more than 40 million records) into the database through the ODBC driver, but after a while (about 8 hours) ) Processing speed and entering in the database is very slow.
Can you help me how to enter every 5 million records, for example, in a table in the database?

Parse

Macros

Database Connection

Accepted answers

All comments

Qiu

@Saraabdi955

Maybe we can use a batch macro to commit the date every 5million?

danilang

Hi @Saraabdi955

We had this problem with one of our processes. Data updates were taking upwards of 10 hours. On investigating why it was so slow when using the Insert/append methods available with standard OLE/ODBC, we found that Alteryx performs primary key checks while inserting the records. When we looked at what was happening on the database session, we found that Alteryx generates a stored proc based on the primary key of the table and calls this to insert the records performing updates/inserts depending on the values passed. This method is used even if you delete all the records first. This causes the process to slow down as more and more records are added to the table since the stored proc has to wait on the table indexing to complete before adding each new record

To get around this, you can use the bulk import features available with SQL Server and Oracle. If you're using one of these, you can set up an output tool to use bulk import

In the Data Sources section pick Bulk option that corresponds to your database

Choose the data source and the the table. You'll end up with a config that looks like this

With this method, you loose the Update: Warn/Error on Update and Update:Insert If New option(green box), but you get the option to set your transaction sizes(blue)

A couple things to note using this method

1. You have to manage the Update/Insert if New option within your workflow. Since Alteryx isn't calling the stored proc in the background, you'll have to determine how to handle updates to existing records If you don't have many updates you can use a standard ODBC connection to update these and a bulk connection to append the new ones. If you have many updates it may be faster to use the bulk connection with Delete Data and Append to push all the records to the table

2. You'll need "Alter" permission on the table(in SQL Server, anyway) if you're using the Delete Data & Append option. To get the maximum speed, this method performs a Truncate on the table which isn't allowed if you only have write access to the data.