It is faster to delete + insert or select + update

Question

It is faster to delete + insert or select + update

Navigation

#1 by (-2 votes)

4

I'm building an application that will receive data from several different ERPs every day.

But I have the following problem, the next day the ERP can send me the same data that did or did not change + some new data. And I need to insert these new data and update the others if there is any change, and delete old data that will no longer be used.

There are about 15,000 records per day of each company, and I intend this application to be used by + - about 200 companies on average.

Thinking about it, when I receive this information daily, I'd better check it one by one to see if it had any changes and if so, update them (select + update).

Or every day I delete all the information and insert again (delete + insert).

What is the fastest way to handle great information every day? If anyone has a similar experience and knows of a different solution, I am also interested.

Thank you!

php mysql

asked by anonymous 14.10.2017 / 09:21

1 answer

C: Change color of letters (multiple colors on one screen) Select in MySQL with an array

score -2 · Answer 1

This is a very common procedure used by billing offices to broadcast large customer lists. We call this "Charge."

Every day two different systems can talk by sending and receiving this data because one of them is performing an attempt to synchronize with the source database. The process consists of the following:

The database that attempts to synchronize must have two identical tables in its layout, one for input data and one for historical data. Both must have all the necessary fields for the information and two more fields of control: The first is the name of the data source, example: "company_origem". The second field is the data entry date, for example: "entry_date". The date field will work as a parameter for comparing history table data to new data with data.

The routine that should be used is the insertion of the records in the table entry "history table", then perform a comparison with the history table in order to separate the new content. This way you can differentiate the records.

The data that is located different must be inserted into the history table and after the procedure the input table must be cleaned for a new data load.

Using the "company_name" and "entry_data" fields, you can differentiate the data from all sources and thus obtain greater integrity of the information that is received. So you get a "Load" report through the quantities of records that are received daily by each source and date.