Why optimize a query?
Many of us are feel the query that we wrote is final and once it works and provides the result then that query is the best.
Actually the Query optimization is not only the technique to make your query to fetch details or to 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 of data pulls 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 in a business point of view has a very large impact.
How to Optimize a Query?
Hope now you feel that optimizing a query is better and 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 using only the code that exactly applies to the logic, reusing the similar code or copying unwanted lines which may not create impact to your query but may take time for execution, so during code re usability always use only code that exactly required to fetch results for you.
- Avoid using functions in 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.
Example:
- 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 the Type Conversion during the 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, the values of the lower column has to be converted and this will take some time for converting for each of the row values.
Example:
- 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.
Example:
- SELECT rows FROM Student
-
- WHERE id = OBJECT_ID('dbo.Orders') AND indid < 2 [√]
This query will not do full scan of table rows, where if you need to check for any value for that condition available then this query may be useful, 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 operation when we use the distinct during the table join operations which has one to many relations then it’s not advisable.
Example:
- SELECT DISTINCT s.Dept, d.Dept FROM Student s, Department d
-
- Where s.Dept = d.Dept [X]
Example:
- 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 are joined and some conditional operations are compared and executed it’s not suggested to store the values in temp table and access the large quantum of values.
It’s always suggested to use the “View”, where the View has more benefits that the table operations are executed once during the query can be fetched during execution and can be used optimised.
Note: The temporary table may occupy the internal memory and that make the process execution slowness.
- Avoid Using Triggers
Trigger usages are the 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 Dead locks during query executions
The deadlock handling is not an easy job for the larger transactional data volumes, when we handle quite large data processing with multiple processes then we should be clear on dead lock occurrence scenarios and try to avoid it.
When you write any queries, views, functions and store procedures always follow the same order of access of the tables.
Always try to break the operations in transactions as small blocks for easy understanding in troubleshooting. Larger transaction blocks may create deadlocks or dependency and consume more operational and execution times.
- Avoid Using Locks during read 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 UNCOMMITED” 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.
Example:
- SELECT Name, ID, Address FROM Student WITH(NOLOCK) [√]
Thank you.
Read more articles on SQL Server: