Why optimize SQL query?
Many of us feel the query that we wrote is final and once it works and provides the result then that query is the best.
Actually, Query optimization is not only the technique to make your query fetch details or execute many CRUD operations but also they are the main scope to make the end user continue with your application.
It’s the same situation when we access some websites, and fetching data pull most of your time, then you may prefer to use other websites which get you all results instantly.
Optimization is not only to create impact technically but also from a business point of view has a very large impact.
How to Optimize SQL Query?
Hope now you feel that optimizing a query is better also there are a few things that you have to consider before writing a query or before deploying the same.
Avoid using “*” in select query
We have the habit of always writing a query using “*” in select operations.
Example
SELECT * FROM Students [X]
Where the select will fetch all the columns of the table, whenever there is a need for the column then use it, don’t fetch all the columns of the table. Always use column-specific names.
Example
SELECT ID, Name FROM Students [√]
Avoid using Copy Paste of Code randomly
We have the habit of copying the code from existing code or from online forums whenever required while copying please use only the code that exactly applies to the logic, reusing similar code or copying unwanted lines which may not create an impact on your query but may take time for execution, so during code reusability always use only code that exactly required to fetch results for you.
Avoid using functions in the Where Clause
When we have to execute any query there will be a conditional check using the where clause for filtering of data.
Example
SELECT Name, DOB FROM Students WHERE DOB < getdate() [X]
When we use any of the system-defined or user-defined functions in the where clause, then each time; i.e., each of the rows in the table will be checked against the function results which in turn execute the function each time. Obviously, this will take more time and we all know the parameterless function will always return the same values.
Better assign the function value to a variable and then use it in a where clause.
DECLARE @MyDate as date
SET @MyDate = GetDate()
SELECT Name, DOB FROM Students WHERE DOB < @MyDate [√]
Avoid using Joins between two types of columns
As all of you know Type Conversion during data manipulation is another place where a large amount of time will be consumed, when we are trying to join two types of columns then the other column has to be converted, and the values of the lower column has to be converted and this will take some time for converting for each of the row values.
SELECT Name FROM Student, Parents WHERE
Student.SchoolFees = Parents.KidsFees [X]
Student.SchoolFees - Int Value
Parents.KidsFees - Float Value
Avoid using COUNT(*) from tables for getting row count
We usually use the COUNT(*) to get the number of rows available in a table.
Example
SELECT COUNT(*) FROM Student [X]
This count(*) makes the full scan of the table, whenever the code is executed.
SELECT rows FROM Student
WHERE id = OBJECT_ID('dbo.Orders') AND indid < 2 [√]
Note
This query will not do a full scan of table rows, where if you need to check for any value for that condition available then this query may be useful, but not always provide the exact count of rows.
Avoid using DISTINCT when Join Tables
When we use the Distinct keyword it looks as if the query will be automatically optimized by the internal SQL engine, that is true when you use the Distinct for filtering operations when we use the distinct during the table join operations which has one too many relations then it’s not advisable.
SELECT DISTINCT s.Dept, d.Dept FROM Student s, Department d
Where s.Dept = d.Dept [X]
When we use the “Exists” query for fetching some operations during table join then it's better to compare it to the Distinct compares.
SELECT d.Dept FROM Department d Where
Exists ( SELECT ‘Y’ FROM Student s WHERE s.Dept = d.Dept ) [√]
Avoid Using Temp Tables
Using the temporary table is always an easy functionality for accessing the data values, but we should be careful about the right scenario to use the temp tables. When more than one table is joined and some conditional operations are compared and executed it’s not suggested to store the values in a temp table and access the large quantum of values.
It’s always suggested to use the “View”, where the View has more benefits in that the table operations are executed once during the query can be fetched during execution, and can be used optimized.
Note
The temporary table may occupy the internal memory and that makes the process execution slow.
Avoid Using Triggers
Trigger usages are an expensive process in the SQL, so try to avoid firing and executing the triggers. Don’t use the triggers with any constraints and also avoid using the same triggers for multiple CRUD operations.
Avoid Deadlocks during query executions
Deadlock handling is not an easy job for larger transactional data volumes, when we handle quite large data processing with multiple processes then we should be clear on deadlock occurrence scenarios and try to avoid it.
When you write any queries, views, functions, and store procedures always follow the same order of access to the tables.
Always try to break the operations in transactions into small blocks for easy understanding in troubleshooting. Larger transaction blocks may create deadlocks or dependencies and consume more operational and execution times.
Avoid Using Locks during the reading process
Lock of the tables may affect the other process to get impact; there is an alternative query to use “WITH (NOLOCK)” which is equivalent to “READ UNCOMMITTED” at transaction isolation.
This is also called a dirty read but this operation of using “With (NoLock)” can prevent the deadlock occurrences by multiple reads.
SELECT Name, ID, Address FROM Student WITH(NOLOCK) [√]
Conclusion
Hope this will help you to make your SQL optimized and helps to reduce the execution time to some extent.
Read more articles on SQL Server
Read More >>