SQL Query Complexity
Structured Query Language (SQL) is the foundation for interacting with relational databases, making it an essential skill for developers, data analysts, and database administrators. In relational databases, data is stored in tables consisting of rows and columns, where each column holds data of a specific type.
Writing efficient SQL queries is essential when working with large datasets, as poorly optimized queries can significantly slow down performance and consume unnecessary resources.
Example Query:
SELECT * FROM employee WHERE id = 77;
The execution of this query can vary based on the SQL engine’s optimization capabilities, and the performance can differ significantly based on the indexing and query plan used:
- O(1) Complexity: Achieved through a hash index on the ‘id’ column or caching the result of ‘id = 77′ from a previous query.
- O(n) Complexity: Involves scanning each row in the table, which becomes highly inefficient for large datasets.
- O(log(n)) Complexity: Utilizes a sorted ‘id’ index to perform a binary search, requiring approximately 36 lookups for a table with 100 billion rows.
The complexity of the query totally depends upon the SQL engine how it will process the query. If our employee table has 100000000000 rows of data and we need to find out the row where employee id is 77. If we scan the whole table then it would take a long time. If we sort the table and do a binary search on this table then we need about 36 lookups (log base 2 of 100000000000 is ~36) to find out our value. But to sort the table would take a while. It totally depends upon the optimiser of the sql engine.
To learn more about SQL engine query processing, click this link.
Best Practices for Writing Efficient SQL Queries
Here, we mentioned Things to keep in mind while writing good query:
1. Limit your result
When you can not avoid filtering down your select statement, then you can consider limiting your result. You can limit your result by using limit, top Example –
SELECT TOP 3 FROM employee
SELECT* FROM employee limit 10
2. Simplify Complex Queries
Don’t make the query to be more complex. Try to keep them simple and efficient.
Example: Consider the following query:
SELECT * FROM employee WHERE id = 77 or id = 85 or id = 69
So, you can replace the operator by using IN.
SELECT* FROM employee WHERE id in (77, 85, 69)
3. Avoid Using SELECT * in Joins
Using ‘SELECT *‘ is particularly wasteful when tables are joined, as it retrieves every column from every table, including duplicates. Specify only the columns you need to conserve server, database, and network resources.
4. Utilize Aggregate Functions
Instead of processing data row by row, use aggregate functions like COUNT(), AVG(), and others to summarize data. Aggregates can dramatically reduce the time and resources required to process large datasets.
