Forum Moderators: coopster
I have a table with 350,000 records in it including fields like points and username. Every month i get given an update text file (250,000 records) that i upload into a new table.. This table has all the same fields as the previous.
The problem arises when i try to update one table with other. I need to add the points from the update table to the points in the original table where their ID's correspond. If their ID's do not correspond then i need to insert the data into the original table instead of updating.
I think that the sql would go something like this.
table1 = original table
table2 = update table
SELECT
table1.field1,
table1.field2,
table2.field1
WHERE
table1.field1 = table2.field1
(UPDATE
table1
SET
table1.field2 = (table1.field2 + table2.field2)
WHERE ID = table2.ID
)
I have bought some 3rd Party Software that actually does this, the problem is that it is very slow (its taking about 7 days to merge the update text file and the original table).... Im thinking that there must be a quicker way, that is why i am trying this.
Maybe if i did all the updates first from a single script and then, then did the inserts. Or maybe if i uploaded the text file to the server i am working on (it is a managed server an i have access to it via remote desktop) then this would speed it up.
Any hints tips appreciated as i have racked my brains for a way of speeding the update process up.
Thanks
you could split it up doing only reads the first time, getting the data you need. I am not sure if you have the needed ids already or you need to get them from your existing table, I will go with needing to get them.
1. upload the file to the server
2. run you update script
a. loop through each line in the file
b. select the id from the existing table using data you have
c. if the entry exists, write a full UPDATE query to a file
if it does not, write a full INSERT query to the file
3. use the file you wrote to update the table. This could be done 2 ways
a. you could loop through and fire off the queries one at a time
b. use something like LOAD DATA to get everything into the table (much faster)
4. empty the file
This is just off the top of my head but I have done many similar things and they work well. You can even have the initial script just leave the file there and have a cron that checks the filesize of that file on a regular basis and if it has data in it will run a LOAD DATA. Though that would be for higher frequency of updates and doesn't really apply for only once a month.
