i have trying now a lots of time, and i try to find a way there make sense to find out of my problem, but this problem stil make a lot perfumes issues.
i testing it of on a developer server ( 2 cores, 4gb ram ) but i still got issues problems, here is my stored procedure i have typed.
DROP PROCEDURE IF EXISTS insertDistributorProduct; DELIMITER $$ CREATE PROCEDURE insertDistributorProduct( distributorUUID VARCHAR(36), distributorTitle VARCHAR(256), distributorDescription TEXT, distributorSKU VARCHAR(128), manufacturer VARCHAR(64), manufacturerSKU VARCHAR(128), productEan BIGINT(20), productCostPrice INT(11), productStock INT(11), productStockExpected DATE ) BEGIN DECLARE _manufacturerUUID VARCHAR(36) DEFAULT NULL; DECLARE _distributorProductUUID VARCHAR(36) DEFAULT NULL; SET _manufacturerUUID = ( SELECT m.manufacturerUUID FROM manufacturer m WHERE LOWER(m.manufacturerTitle) = LOWER(manufacturer) LIMIT 1 ); -- If manufacturer not exists insert it IF ( _manufacturerUUID IS NULL ) THEN SET _manufacturerUUID = ( SELECT ma.manufacturerUUID FROM manufacturerAlias ma WHERE LOWER(ma.aliasTitle) = LOWER(manufacturer) LIMIT 1 ); -- _manufacturerUUID not found in manufacturer and _manufacturerAlias so insert IF ( _manufacturerUUID IS NULL ) THEN SET _manufacturerUUID = UUID(); INSERT INTO manufacturer(manufacturerUUID,manufacturerTitle,added) VALUES(_manufacturerUUID,manufacturer,NOW()); END IF; END IF; SET _distributorProductUUID = ( SELECT dp.distributorProductUUID FROM distributorProduct dp WHERE dp.distributorUUID = distributorUUID AND LOWER(dp.distributorSKU) = LOWER(distributorSKU) ); -- Update distributorProduct if its all ready found IF ( _distributorProductUUID IS NOT NULL ) THEN UPDATE distributorProduct dp SET dp.distributorTitle = distributorTitle, dp.distributorDescription = distributorDescription, dp.manufacturerUUID = _manufacturerUUID, dp.manufacturerSKU = manufacturerSKU, dp.productEan = productEan, dp.productCostPrice = productCostPrice, dp.productStock = productStock, dp.productStockExpected = productStockExpected, dp.updated = NOW(), dp.activated = 1 WHERE dp.distributorProductUUID = _distributorProductUUID LIMIT 1; -- Insert new product to distributorProduct if not exists. ELSE INSERT INTO distributorProduct (distributorProductUUID,distributorUUID,manufacturerUUID,distributorTitle,distributorDescription,distributorSKU,manufacturerSKU,productEan,productCostPrice,productStock,productStockExpected,added) VALUES(UUID(),distributorUUID,_manufacturerUUID,distributorTitle,distributorDescription,distributorSKU,manufacturerSKU,productEan,productCostPrice,productStock,productStockExpected,NOW()); END IF; END $$
My problem is that every time I get over 50-100k rows, this script takes longer and longer time, and I have to try to look up my index but still nothing happens.
how can I debug and perfume my database more? every day i need to run this producer like 1-1,4milion times between 4-5 hours max, its update all our products but right now it's useless.
The first time 60k rows takes 15min, and after that 60k more ( total 120k ) this time its taken like 30-45min to run, and I still don't know why it happened. What did I do wrong?
Everything I tested here is running in virtual box software, I don't have to test this on real hardware.