Time Complexity: Why UPDATE needs caution
O(n)
0
0
Why UPDATE needs caution in SQL - Performance Analysis
Understanding Time Complexity
When we run an UPDATE command in a database, it changes data in one or more rows.
We want to understand how the time it takes grows as the data size grows.
Scenario Under Consideration
Analyze the time complexity of the following SQL UPDATE statement.
UPDATE employees
SET salary = salary * 1.1
WHERE department = 'Sales';
This code increases the salary by 10% for all employees in the Sales department.
Identify Repeating Operations
Look for repeated work done by the database engine.
- Primary operation: Scanning rows to find those matching the condition.
- How many times: Once for each row in the employees table.
How Execution Grows With Input
As the number of employees grows, the database checks more rows to find matches.
| Input Size (n) | Approx. Operations |
|---|---|
| 10 | About 10 row checks |
| 100 | About 100 row checks |
| 1000 | About 1000 row checks |
Pattern observation: The work grows roughly in direct proportion to the number of rows.
Final Time Complexity
Time Complexity: O(n)
This means the time to complete the UPDATE grows linearly with the number of rows in the table.
Common Mistake
[X] Wrong: "UPDATE runs instantly no matter how big the table is."
[OK] Correct: The database must check each row to see if it matches the condition, so more rows mean more work and more time.
Interview Connect
Understanding how UPDATE scales helps you write efficient queries and avoid surprises in real projects.
Self-Check
"What if we add an index on the department column? How would that change the time complexity?"