Querying through views in SQL - Time & Space Complexity

Time Complexity: Querying through views

O(n)

Understanding Time Complexity

When we use views in SQL, we want to know how the time to get results changes as the data grows.

We ask: How does querying a view affect the work the database does?

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

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

SELECT * FROM RecentOrders WHERE CustomerID = 12345;

This code creates a view showing orders from the last 30 days, then queries it for a specific customer.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

• Primary operation: Scanning the Orders table to find recent orders.
• How many times: Once per query, the database checks each order to see if it is recent.

How Execution Grows With Input

As the number of orders grows, the database must check more rows to find recent ones.

Input Size (n)	Approx. Operations
10	About 10 checks
100	About 100 checks
1000	About 1000 checks

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

Final Time Complexity

Time Complexity: O(n)

This means the time to get results grows roughly in step with the number of rows in the Orders table.

Common Mistake

[X] Wrong: "Using a view makes the query instantly faster because it stores results."

[OK] Correct: Views do not store data by default; they run the underlying query each time, so the work depends on the original table size.

Interview Connect

Understanding how views affect query time helps you explain database behavior clearly and shows you think about efficiency in real situations.

Self-Check

"What if the view included an index on OrderDate? How would that change the time complexity?"