Why stored procedures are needed in SQL - Performance Analysis

Time Complexity: Why stored procedures are needed

O(n)

Understanding Time Complexity

We want to understand how using stored procedures affects the time it takes to run database tasks.

Specifically, we ask: How does the work grow when using stored procedures compared to running many separate queries?

Scenario Under Consideration

Analyze the time complexity of this stored procedure example.

CREATE PROCEDURE UpdateUserStatus()
BEGIN
  UPDATE users SET status = 'active' WHERE last_login > NOW() - INTERVAL 30 DAY;
  UPDATE users SET status = 'inactive' WHERE last_login <= NOW() - INTERVAL 30 DAY;
END;

CALL UpdateUserStatus();
    

This procedure updates user statuses in one call instead of multiple separate queries.

Identify Repeating Operations

Look at what repeats inside the procedure.

• Primary operation: Scanning the users table twice for updates.
• How many times: Each update scans all users once, so two scans total.

How Execution Grows With Input

As the number of users grows, the time to scan the table grows too.

Input Size (n)	Approx. Operations
10	About 20 row accesses (2 scans x 10 rows)
100	About 200 row accesses
1000	About 2000 row accesses

Pattern observation: The work grows roughly in direct proportion to the number of users.

Final Time Complexity

Time Complexity: O(n)

This means the time to run the procedure grows linearly as the number of users increases.

Common Mistake

[X] Wrong: "Stored procedures always make queries run faster because they are pre-written."

[OK] Correct: Stored procedures group commands but still process data; if the data grows, the time grows too. They help organize work but don't change how much data is processed.

Interview Connect

Understanding how stored procedures affect time helps you explain how databases handle tasks efficiently. This skill shows you think about both code and data size, which is important in real projects.

Self-Check

"What if the stored procedure used indexes to find users instead of scanning the whole table? How would the time complexity change?"