File Transfer is extremely slow between Large CSV file to MySql

Hi Folks,

I am looking for help with my assignment. The assignment is that I should transfer all the rows in a CSV file which has half a million records(500K rows) into a single table in a Database. Since I have MySql installed in my system, I am trying to transfer those records into it. But the transfer is extremely slow. Could you please provide any suggestions for making the transfer fast or any other ideas? Thanks in advance.

Input : CSV File (86MB) (500K records)

Output: Single table in MySql

ODBC driver: Mysql ODBC 8.0 Unicode Driver (64bit)

Accepted answers

danilang

Hi @Jayasuryan

Unfortunately, the output method you've selected "Delete Data and Append" violates point 6, since deleting all the data effectively truncates the table. It also violates Point 2, since between the time you delete the data and reinsert all the values, the table will be incomplete. Any other parallel process that tries to access the data in this incomplete table will be blocked by the lack of data.

I see two potential ways to satisfy all these criteria and still have a workflow that run acceptable fast.

1. Alteryx only solution. You'll have to work within the limitations imposed by "Update: Insert if New" to stop from blocking other applications from accessing the data. Simply passing all the records to the database is extremely slow as you mentioned, so use the speed of the Alteryx engine to your advantage. A way to do this is to read all the data from both the csv and the MySQL table and compare all the fields joined on primary key, to find only the records that have changed. Only write these changed records back to the database.

2. Hybrid solution. Create a temp table in your database with the same structure as your main table and create an after insert trigger that updates the records in the main table based on the values inserted into the temp one. Use "Delete Data and Append" to dump the data into the temp table. The database will then update the main table as quickly as possible.

Dan

All comments

danilang

Hi @Jayasuryan

This could be caused by the method that you're using in the output tool. If you just need to get all the records into an empty database make sure you use "Create Table" This will add the records to a new table in the database with no validation. If you use "Update: Insert if New" on a table that contains records, MySQL has to perform the following process for each record.

1. find the record using the primary key

2. if it exists, update the records. if not create a new record.

With 500K records already in your database, this can take quite a while.

Dan

Jayasuryan

Hi, danilang,

First of all thanks for your reply.

Yes, you are right, I have created an empty table with one of the columns as Primary key. This is because the assignment mandates this rule (Please find the points to achieve below). In the 'output options' present in the output tool, I have chosen as 'Delete Data and Append'.

Points to achieve

Your code should follow concept of OOPS
Support for regular non-blocking parallel ingestion of the given file into a table. Consider thinking about the scale of what should happen if the file is to be processed in 2 mins.
Support for updating existing products in the table based on `sku` as the primary key.
All 500k rows to be ingested into a single table
An aggregated table on above rows with `name` and `no. of products` as the columns
Ingest the same file at least 2 times, without truncating the products table.

danilang

Hi @Jayasuryan

I see two potential ways to satisfy all these criteria and still have a workflow that run acceptable fast.

Dan

604c6bff826fd3151089

Hii

I hope you are doing well.

Please share the solution of your assignment, all the 5 questions you have mentioned in our replies.

I am facing the same problem. Thanks in advance

Quick Links

This months top contributors

mceleavey 383

AkimasaKajitani 382

mbarone 337

Hollingsworth 335

LanisC 335