0

I have two tables: costumer and phone. The costumer table has about 2 million rows and the phone table has about 5 million rows.

everyday this table is updated trough a PHP application. we receive an excel datasheet with 5000 new records(costumers and phones).

The application check if the costumers and their phones in the sheet are already registered, so it updates or inserts the new records.

The query takes a long time to execute, I believe it happens because of the multiples checks I have to do in the phone table. Is there a way to speed up the things?

Masivuye Cokile
  • 4,754
  • 3
  • 19
  • 34
gbadini
  • 11
  • 1
  • 2
    Performance questions should include `EXPLAIN ANALYZE` and some information about table size, index, current time performance, desire time, etc. `Slow` is a relative term and we need a real value to compare. [**How do I obtain a Query Execution Plan?**](http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan) – Juan Carlos Oropeza May 04 '17 at 14:40
  • What version of SQL are you using? What is your hardware profile? Do you have an `Execution Plan` to show where the bottlenecks are? Are the tables indexed properly? Indexed too much? – SS_DBA May 04 '17 at 14:43
  • Share your execution plans using [Paste The Plan @ brentozar.com](https://www.brentozar.com/pastetheplan/) here are the instructions: [How to Use Paste the Plan](https://www.brentozar.com/pastetheplan/instructions/). – SqlZim May 04 '17 at 15:04

1 Answers1

0

The most efficient way to do this is :

  1. Bring all records into the system and put them in a temporary table say customerTemp. Build indexes on this table.
  2. Use INNER JOIN between temp table and production table (say customer), inside an update query to update all existing records.
  3. Then from this temp table insert the records to production table using INSERT query with the difference records( temp table RIGHT JOIN to prod table and WHERE clause to filter out prod table records which are NOT NULL).
  4. All checks can be done on the temporary table.
DhruvJoshi
  • 17,041
  • 6
  • 41
  • 60