2

I have an app that has to import TONS of data from a remote source. From 500 to 1500 entries per call.

Sometimes some of the data coming in will need to replace data already stored in the dB. If I had to guess, I would say once in 300 or 400 entries would one need to be replaced.

Each incoming entry has a unique ID. So I am trying to figure out if it is more efficient to always issue a delete command based on this ID or to check if there is already an entry THEN delete.

I found this SO post where it talks about the heavy work a dB has to do to delete something. But it is discussing a different issue so I'm not sure if it applies here.

Community
  • 1
  • 1
Lee Loftiss
  • 3,035
  • 7
  • 45
  • 73
  • are you trying to do the work at the Application level or at the Database Level? what language do you use for your app and what is the database you are using? – Jaylen Oct 25 '14 at 00:10
  • It is a MySQL dB and I am using PHP. Not sure how to answer your other question. I am making all the dB calls from my application. – Lee Loftiss Oct 25 '14 at 00:17

2 Answers2

3

Each incoming entry has a unique ID. So I am trying to figure out if it is more efficient to always issue a delete command based on this ID or to check if there is already an entry THEN delete.

Neither. Use INSERT ... ON DUPLICATE KEY UPDATE ....

user207421
  • 305,947
  • 44
  • 307
  • 483
0

Since you are using MySQL and you have a unique key then let MySQL do the work.

You can use INSERT INTO..... ON DUPLICATE KEY UPDATE......

MySQL will try to insert a new record in the table, is the unique value exists in the table then MySQL will update all the field that you have set after the update

You can read more about the INSERT INTO..... ON DUPLICATE KEY UPDATE...... syntax on http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

Jaylen
  • 39,043
  • 40
  • 128
  • 221