SQL Query Optimization
|
Well when we say about the data driven world, database application become a crucial component of many top enterprises. And as we also aware that many of companies has adopt cloud to store there data, so optimizing queries become very important than ever for a company bottom line.
Here we will provide some effective techniques to accelerate SQL Query performance in this article. And there are number of ways to optimize SQL queries to perform faster which is given below:-
Table of Contents
1. Try to avoid use of wildcard characters
2. Increase Query Performance with Indexes
3. Use appropriate data types
4. Avoid subqueries
5. Use LIMIT or TOP keyword to number of rows
6. Avoid Use of "Select *"
7. Use EXISTS instead of IN
8. Use GROUP BY to group data
9. Use of Stored Procedures
10. Optimize the Database Design
11. Use of query Optimization Tool
12. Monitor Query Performance
|
(1)Try to avoid use of wildcard characters
|
When we say about the wildcard characters like % and _, in SQL, they will alow down your query performance. And whenever we use wildcard charcter, the database need to scan the entire table to find the relevant data to your search. And it is one of the main factor to avoid wildcard character to faster the performance and use them only when we absolutely necessary.
To understand this we will take an example for wild charcter. We have database and we need to find the last name of student start with letter "M". Below is the query we uses a wildcard character to find all matching records:-
This above query will work but will be slower than a query. So we can index to improved by adding index to lastname to column and query can be as follows to remove the uses of wildcard character.
This query will use the index on the last name column and will be faster than the previous query.
|
(2)Increase Query Performance with Indexes
|
By apply indexes to SQL query speed of queries can be increases and which enable the database to quickly find entries which fir to specific criteria. And when we say about the Indexing it is the process of mapping values of one or more columns from a table to such a unique value which make it easy to search for the rows which match a certain value or range of values.
And to enhance SQL queries we can create indexes on the columns which are frequently used in WHERE, JOIN and ORDER BY clauses. And if we create too many indexes, it can slow down data modification operations such as INSERT, Update and DELETE.
To Consider the trade-offs between write performance and read performance when deciding which columns to index and which types of indexes to use.
We will use the below query to find all orders made by specific customer:-
If there is no index on the table query will search the entire table for the entries which match the StudentId, And above query will take long time if the table contains lot of records. And to apply speed search you can create a index on StudentId column to improve the query:-
This above query will create index on the StudentId column of table_name table. And when we run the query, the database can quickly locate the rows which match the customer number using the index. This can improve query performance.
|
(3)Use appropriate data types
|
Well this is also main factor to improve the performance always use the appropriate data types for column. And to understand this we will take an example, using and integer data type for a column which contains numeric values which can make queries run faster than using a text data type. And by using the correct data types will also ensures data integrity and can prevent data conversion errors.
To understand we will take an example of one table where each row represemst the order details of a shopping store. Here table has column for orderId, customerId, OrderDate and the orderTotal.
Here orderTotal column will contains the numeric values. And if the orderTotal column is stored as text data type, queries which perform calculations on tthe order total will be slower than if the column was stored as numeric data type.
|
|
|
(4)Avoid subqueries
|
Another important aspects is subqueries, and this will slow down query performance. And this mainly when we used it in WHERE or HAVING clauses. And this is important to avoid subqueries and if possible then use JOINS or other techniques instead.
To understand this we will take an example to find all customer who placed order in last 30 days. We use the below query uses a subquery to find all order Id in last 30 days.
Above query will work but slower than query that we create by use of JOIN to get the relevant data. Below is the query by use JOIN to find all customer who placed the order in last 30 days:-
This above query will joins the customerTable and the orderTable which retrieves all customer information for those who have placed an order in last 30 days. And this above query is faster that we use previously with subquery.
|
(5)Use LIMIT or TOP keyword to number of rows
|
Well use of LIMIT or TOP clause must be used to restrict the number of rows returned in SQL queries. And there will be fewer data to process and return as a result.
To understand this we will take an example, let consider to find all customers who have placed an order in last 30 days. And if the return result is larger number of customer so will take long time. SO to avoid this we can optimize by use the LIMIT or TOP. And below query will limit the reult to 20 rows.
This query will return the top 20 rows which match the criteria and this will improve the query performance.
|
(6)Avoid Use of "Select *"
|
Use of "*" will slow down your query performance. SO not to use Select * statement as this will slow down query performance because this will return all the columns in table. And this will also inlcude all the column which is not needed in your application. So to optimize your SQL query it is important to put the column name in SQL query.
Well to understand this we will take a example of order table to find all customer who placed order in last 30 days. Below query will help you to selects all column from customerTable.
To optimize the query, we will modified the select statement and add column name with below query.
This query will only select the customerId, firstName, and lastName columns, which will improve query performance.
|
(7)Use EXISTS instead of IN
|
Here value is compared with a list of values returned by a subquery using the IN operator. And use of IN will slow down query performance becuase this will required the database to perfomr a full table scan on the subquery. And to optimize this SQL queries we use EXISTS operator instead of IN.
Above query uses IN to compare the customer ID with the list of customerIds returned by the subquery. To optimize the query, you can use EXISTS instead of IN:
This above query uses EXISTS to check if a matching row exists in the orderTable instead of using IN keyword. And this keyword will improve the query performance by avoiding a full table scan.
|
|
|
(8)Use GROUP BY to group data
|
We use GROUP BY to group rows based on one or more columns. And this can be useful when need to summarize data or perform aggregate functions on groups of data. And GROUP BY will slow down the performance if we use it unnecessarily. And to optimize SQL Queries you only use GROUP BY when it is necessary.
Below is the example use of GROUP BY to find the total number of orders placed by each of the customer:-
This above query uses GROUP BY to group rows by the customerId and count the number of orders placed by each customer. And to optimize the query we can use a subquery to retrieve the customer information and join it with the orderTable.
This above query uses a subquery to calculate the number of orders placed by each customer and then joins the result with the customerTable to retrieve the customer information. And to avoid this use of group By can improve the query performance.
|
(9)Use of Stored Procedures
|
When we say about the store procedure, these are precompiled SQL statements which are stored in the database. And this can be called from and application or by directly from a SQL query. By use of store procedure this can improve the query performance by reducing the amount of data which is sent in between the database and the application, and by reducing the amount of time required to compile and execute the SQL statements.
|
(10)Optimize the Database Design
|
Other important point to optimize the database design which can improve query performance. And this will includes always ensure the tables are properly normalized and indexes are used effectively. And in addition, it is important to ensure that the database is properly turned for the xpected workload and that it is configured ofr appropriate level of concurrency.
|
(11)Use of query Optimization Tool
|
Well there are number of query optimization tools that are available which can help to identify performance issue in SQL queries. And these tools will provide recommendations to improve the query performance, just like create indexing, rewriting queries or optimize the database design. And some of the popular query optimization tools which are given below:-
- (i)Microsoft SQL Server Query Optimizer
- (ii)Oracle SQL Developer
- (iii)MySQL Query Optimizer.
|
(12)Monitor Query Performance
|
Well when you are doing on some good amount of data or some big projects you must monitor query performance, which is an important step to optimize SQL queries. And by monitoring query performance, it is possible to identify performance issues and will make appropriate adjustments. And this will also include optimizing indexes, rewriting queries or adjust the database design. And to track the performance there are number of tools available in market which includes below tools:-
- (i)SQL Server Profiler
- (ii)Oracle Enterprise Manager
- (iii)MySQL ENterprise Monitor
|