What is Batch Processing in Databases
Introduction
Batch processing in databases involves executing a series of database operations as a single unit, often without immediate user interaction. It's used to handle large volumes of data efficiently and is typically scheduled during off-peak hours.
Practical SQL Examples
Batch Insertion
Insert multiple records into a table at once for efficiency:
INSERT INTO employees (employee_id, first_name, last_name, department)
VALUES
(101, 'Alice', 'Smith', 'HR'),
(102, 'Bob', 'Johnson', 'Engineering'),
(103, 'Charlie', 'Brown', 'Marketing');Batch Update
Update multiple records in a single operation:
UPDATE employees
SET salary = salary * 1.10
WHERE department = 'Engineering';Batch Deletion
Delete multiple records efficiently:
DELETE FROM accounts
WHERE status = 'inactive' AND last_login < DATE_SUB(CURRENT_DATE, INTERVAL 1 YEAR);Using Stored Procedures
Encapsulate complex logic in stored procedures for batch processing:
CREATE PROCEDURE update_employee_salaries(IN dept VARCHAR(50), IN increase DECIMAL(5,2))
BEGIN
UPDATE employees
SET salary = salary * (1 + increase)
WHERE department = dept;
END;Call the procedure:
CALL update_employee_salaries('Sales', 0.05);Scheduling Batch Jobs
SQL Server Example
Use SQL Server Agent to schedule jobs for automated execution.
MySQL Event Scheduler Example
Automate monthly reports with MySQL events:
CREATE EVENT monthly_sales_report
ON SCHEDULE EVERY 1 MONTH
DO
INSERT INTO sales_report (year_month, total_sales)
SELECT DATE_FORMAT(sale_date, '%Y-%m'), SUM(amount)
FROM sales
GROUP BY DATE_FORMAT(sale_date, '%Y-%m');This concise overview covers key aspects of batch processing in databases with practical SQL examples.
