Why stored procedures centralize logic in MySQL - Performance Analysis

Time Complexity: Why stored procedures centralize logic

O(n)

Understanding Time Complexity

When we use stored procedures, we want to know how the time to run them changes as data grows.

We ask: How does the work inside a stored procedure scale with input size?

Scenario Under Consideration

Analyze the time complexity of this stored procedure that sums values from a table.

CREATE PROCEDURE SumValues()
BEGIN
  DECLARE total INT DEFAULT 0;
  DECLARE done BOOLEAN DEFAULT FALSE;
  DECLARE val INT;
  DECLARE cur CURSOR FOR SELECT value FROM numbers;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO val;
    IF done THEN
      LEAVE read_loop;
    END IF;
    SET total = total + val;
  END LOOP;
  CLOSE cur;
  SELECT total;
END;
    

This procedure reads all rows from the 'numbers' table and sums their values.

Identify Repeating Operations

Look for repeated actions inside the procedure.

• Primary operation: Looping through each row in the 'numbers' table.
• How many times: Once for every row in the table.

How Execution Grows With Input

The procedure does one addition per row, so more rows mean more additions.

Input Size (n)	Approx. Operations
10	10 additions
100	100 additions
1000	1000 additions

Pattern observation: The work grows directly with the number of rows.

Final Time Complexity

Time Complexity: O(n)

This means the time to run grows in a straight line with the number of rows processed.

Common Mistake

[X] Wrong: "Stored procedures always run instantly no matter data size."

[OK] Correct: Stored procedures still do work on data, so bigger data means more time.

Interview Connect

Understanding how stored procedures scale helps you explain how database logic handles growing data smoothly.

Self-Check

"What if the stored procedure called another procedure inside the loop? How would that affect time complexity?"