When to Avoid INSERT INTO SELECT: Alternative Methods for Efficient Data Insertion with Discounts in MariaDB

In SQL, combining an INSERT and SELECT statement into a single INSERT INTO SELECT can sometimes be inefficient. This happens because the database engine performs these operations differently compared to running them separately:

1. Read and Hold Data: During INSERT INTO SELECT, the SELECT portion retrieves data first. However, unlike a regular SELECT where the results are displayed, the database holds onto this data.
2. Insert One by One: For each row retrieved, the database then performs a separate INSERT operation to insert that single row into the target table.

Reasons for Slowness:

• Overhead: There's extra processing involved in managing the temporary result set from the SELECT before inserting each row.
• Locking: Each INSERT within the combined statement might acquire locks on the target table, causing delays if other operations need to access the same table.

Separate Statements are Faster:

When you run INSERT and SELECT separately, the database engine can potentially optimize them independently. The SELECT might leverage indexes for faster retrieval, and the INSERT might insert data in larger batches, improving efficiency.

Optimizing INSERT INTO SELECT:

• Consider Alternatives: In some cases, using temporary tables or bulk loading techniques might be faster than INSERT INTO SELECT.
• Analyze Table Statistics: Outdated table statistics can lead to suboptimal execution plans. Use ANALYZE TABLE to update them.
• Check for Implicit Conversions: If the data types between the source and target tables differ, implicit conversions can slow things down. Ensure compatible data types.

Slow INSERT INTO SELECT:

INSERT INTO DiscountedProducts (product_id, name, discounted_price)
SELECT product_id, name, price * 0.9 AS discounted_price
FROM Products;

This statement retrieves all products from Products, calculates a discounted price for each, and then inserts them one by one into DiscountedProducts.

Faster Separate Statements:

-- Step 1: Select data with discount calculation
SELECT product_id, name, price * 0.9 AS discounted_price
INTO OUTFILE '/tmp/discounted_products.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
FROM Products;

-- Step 2: Bulk insert from temporary file
LOAD DATA LOCAL INFILE '/tmp/discounted_products.csv'
INTO TABLE DiscountedProducts
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
IGNORE 1 LINES;

This approach is faster because:

1. Faster Data Retrieval: The SELECT writes the results with discounts directly to a temporary file, potentially using optimizations for data retrieval.
2. Bulk Loading: The LOAD DATA statement bulk inserts data from the file into the table, which can be more efficient than single-row inserts.

• Perform a single INSERT from the temporary table into the DiscountedProducts table.
• Use INSERT ... SELECT to populate the temporary table with the discounted prices from the Products table.
• Create a temporary table with the desired structure to hold the discounted product data.

This approach avoids the overhead of single-row inserts and leverages a single INSERT for the final transfer.

Example:

CREATE TEMPORARY TABLE TempDiscountedProducts (
  product_id INT,
  name VARCHAR(255),
  discounted_price DECIMAL(10,2)
);

INSERT INTO TempDiscountedProducts (product_id, name, discounted_price)
SELECT product_id, name, price * 0.9 AS discounted_price
FROM Products;

INSERT INTO DiscountedProducts (product_id, name, discounted_price)
SELECT * FROM TempDiscountedProducts;

DROP TEMPORARY TABLE TempDiscountedProducts;

Stored Procedures:

• Within the procedure, you can use separate SELECT and INSERT statements potentially with optimizations for reusability.
• Create a stored procedure that encapsulates the logic for calculating discounts and inserting data.

This approach allows for modularity and potentially better performance by pre-compiling the logic.

Example (Basic Structure):

DELIMITER //
CREATE PROCEDURE CreateDiscountedProducts()
BEGIN
  DECLARE discount DECIMAL(5,2) DEFAULT 0.1;

  INSERT INTO DiscountedProducts (product_id, name, discounted_price)
  SELECT p.product_id, p.name, p.price * discount
  FROM Products AS p;
END //
DELIMITER ;

CALL CreateDiscountedProducts();

Triggers:

• Within the trigger, calculate the discounted price and insert a new row into the DiscountedProducts table.
• Create a trigger on the Products table that fires on INSERT or UPDATE events.

This approach can be useful for maintaining consistency between the tables, but be cautious of potential performance overhead for frequent inserts/updates in Products.