I have 2 tables Staging & Main Table both has the columns SKU and Price. Records are inserted into staging table from there depending on the record its decided whether new record is to be inserted or update an existing. I have written multiple queries which are working fine but its taking longer than normal. What I am doing is:
Staging Table where data initially inserted into
+----------+--------------+-----------+-------+---------------------------------------------------------------------------------------------------------------------------+
| indexid | Persisted | sku | price | Logic |
+----------+--------------+-----------+-------+---------------------------------------------------------------------------------------------------------------------------+
| 1 | No | 4567456 | 10.99 | This row doesn't exists in main table so need to be inserted and update the persisted to NewProduct |
| 2 | No | 5463454 | 5.99 | This row exists and price matching then this simply need to be updated the persisted to PriceMatched |
| 3 | No | 5645654 | 4.75 | This row exists but price not matching, so new row to be inserted, updated existing record in both tables to PriceUpdated |
| 4 | PriceUpdated | 2222222 | 3.00 | |
| 5 | NewProduct | 5555555 | 4.00 | |
| 6 | PriceMatched | 7685765 | 5.00 | |
| 7 | PriceUpdated | 6574567 | 3.99 | |
+----------+--------------+-----------+-------+---------------------------------------------------------------------------------------------------------------------------+
+---------+--------------+---------+-------+
| indexid | status | sku | price |
+---------+--------------+---------+-------+
| 1 | Active | 5635674 | 10.99 |
| 2 | Active | 5463454 | 5.99 |
| 3 | Active | 5645654 | 2.75 |
| 4 | PriceUpdated | 2222222 | 3.00 |
| 5 | PriceUpdated | 5555555 | 4.00 |
| 6 | PriceMatched | 7685765 | 5.00 |
| 7 | PriceUpdated | 6574567 | 3.99 |
+---------+--------------+---------+-------+
If Product not exist in main table then insert new record and update the staging table with persisted = 'NewProduct'
I am selecting all the records from staging table where persisted = 'No' Then Insert new record
UPDATE stagingTable SET persisted = 'NewProduct' WHERE indexid = '${indexID}';
Else Check if existing product price matching then update persisted = 'PriceMatched'
UPDATE stagingTable SET persisted = 'PriceMatched' WHERE indexid = '${indexID}' ;
If price not matched then insert new record into Main Table and update existing record status = 'PriceUpdated' And staging table persisted = 'PriceUpdated'
UPDATE mainTable SET productstatus = 'PriceUpdated' WHERE indexid = '${indexID}';
UPDATE stagingTable SET persisted = 'PriceUpdated' WHERE indexid = '${indexID}';
Insertions are working fine but updates are taking quite long 10000 records taking around 15-20 Minutes I am inserting and updating by batch insert and update.
This is the logic I have