Why views are needed in SQL - Performance Analysis

Time Complexity: Why views are needed

O(n)

Understanding Time Complexity

We want to understand how using views affects the time it takes to get data from a database.

Specifically, we ask: How does the work grow when we use views compared to direct queries?

Scenario Under Consideration

Analyze the time complexity of this SQL view and query.

CREATE VIEW RecentOrders AS
SELECT OrderID, CustomerID, OrderDate
FROM Orders
WHERE OrderDate >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);

SELECT * FROM RecentOrders WHERE CustomerID = 123;
    

This code creates a view showing orders from the last 30 days, then selects orders for one customer.

Identify Repeating Operations

Look at what repeats when running the query.

• Primary operation: Scanning the Orders table to find recent orders.
• How many times: Once per query execution, filtering all rows from the last 30 days.

How Execution Grows With Input

As the number of orders grows, the work to find recent orders grows too.

Input Size (Orders in 30 days)	Approx. Operations
10	10 rows checked
100	100 rows checked
1000	1000 rows checked

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

Final Time Complexity

Time Complexity: O(n)

This means the time to get results grows linearly with the number of recent orders.

Common Mistake

[X] Wrong: "Using a view makes queries instant because it stores results like a table."

[OK] Correct: Views usually run the underlying query each time, so the work depends on the data size, not a stored snapshot.

Interview Connect

Understanding how views work helps you explain query performance clearly and shows you know how databases handle data behind the scenes.

Self-Check

"What if the view was indexed or materialized? How would that change the time complexity?"