I need to speed up my upload to a mySQL db. I am looking into LOAD DATA LOCAL INFILE '[FILENAME WITH //]' INTO TABLE [TABLENAME] FIELDS OPTIONALLY ENCLOSED BY '”' TERMINATED BY ',' LINES TERMINATED BY '\n'
but the data I have goes into two tables with a 1:1 relationship.
I'm pretty sure the general consensus is no in all cases so I was hoping to create a procedure that would do the work. The there is I don't know how to load the data and then call the SP.
Should I create a temporary table first, then load data into it, then call an SP? If so then I do i do the first step?
Thanks ^_^
Andy
What I have tried:
I used entity framework to add each item one by one, then I tried adding all of table a, updating the F_ID of table b's items then add all of table b. This was pretty slow and I only have about 70k rows. That doesn't seem a lot to me but it can take upwards of 15 mins.
I'd welcome other suggestions