Optimized SQL query writing

Tips and Tricks

       1. Remove * (Star) from queries

Try not to use *(Star) short hand syntax in SELECT statement specially when a table containing large column list.

What SQL do internally?

If we use *(Star) then SQL will replace the star with column name at runtime but if we do not use *(star) then SQL will not need to do this extra work which saves a little time. You can check out the SQL Keywords Reference for a better understanding.

But if we need all the column then it is better to write * (star) as compared to the time it saves

   2. Remove Unnecessary columns from a query

Usually we do not need all the column in the UI/ Client side. Just remove the unnecessary columns from SELECT Statements.

       3. Try to avoid using NVARCHAR (MAX)

UserRoles table has a column name UserId which is nvarchar(128).

But in a stored procedure we have declared a parameter @UserId which is nvarchar(max) that is dealing with UserId column of UserRoles table which is incorrect.

      4. Try to avoid using Cursor

Cursors have poor performance.

How you can avoid cursor?

• The cursor can be written as a normal query in case of scenarios when the task needs to be performed repeatedly.
• Rewrite cursors as a derived query.
• Use temporary tables instead of cursors.
• Use table variable instead of cursors.
• Rewrite them as complex SQL Statements.

     5. Try to avoid writing DECLARE many times, try to write it one time

   6. Try to avoid writing Scalar-valued function inside SELECT

If the Scalar-valued function is small then it is okay but if the function is big then it is better to separate it from join. It works a sub query inside SELECT which makes the query slow.

    7. Use of DISTINCT

Do not use DISTINCT unless it is a business need. If we use DISTINCT in a big JOIN statement to retrieve unique value then at join query is defective. In this example we are using GROUP BY so there should not be any duplicate. Here we use DISTINCT also to remove duplicate. That means it’s a defective query.

      8. Try not to use IN in WHERE Clause

IN makes the query slower, instead we can use join.

9. Use Non Clustered index on those columns which are used inside WHERE Statement frequently

10. Instead of HAVING use WHERE to define the filters

HAVING statements are executed after WHERE. A WHERE statement is more effective if the goal is to filter a query based on conditions.

11. Guidelines for GROUP By

• Try not to use GROUP BY inside a big JOIN.
• Try not to use GROUP BY when we are dealing with a big amount/rows of data.
• Try not to use Group BY when we are dealing with a large number of column.
• Try not to use Group By when we are not using any aggregate functions, Use DISTINCT instead.

12. Try not to use VIEWS inside a large JOIN query.

Views makes a join slower, so we can retrieve the query from view and make it precise and use Temp table to link it with JOIN query.

13. Use joins with INNER JOIN (not WHERE)

We can write this query,

14. Replace OR with CASE WHEN Statement

15. Avoid writing large JOIN query. It will take so much time. Try to break it smaller join

Avoid large JOIN block unless it is extremely needed.

16. LIMIT can simplify query results

Using a LIMIT statement prevents taxing the production database with a large query, only to find out the query needs editing or refinement.

17. Stop using sub query inside SELECT statement

It will makes the query execution time significantly low.

We can we do?

1. Separate it from the main query and using TEMP table to link it with main query.
2. We can keep the data in the main table for example.

We can make a new column ChallanNo in Order table and we can keep ChallanNo when a challan entered against an order then we can directly use ChallanNo column instead of subquery.

18. At the beginning make a block of precise data

Let’s consider the example. We are interested to retrieve the shop inside this rectangle.

Now the shop locations are kept in Location table. So, if we directly use Location table inside JOIN statement then it will make the query slower. Because the location table may contain 100,000 rows of data.

We can make a precise block of Location data first inside that rectangle (maximum 500 data).

19. Do not use a big table inside a JOIN query. That will make the query slower

20. Check execution plan which block is taking time

Execution plan really helpful. We need to learn how to use it more efficiently.

Check out blog posts from our developers on Mechanism for Error Handling in MS SQL Server Transact-SQL and Fixing Update Statement Errors Caused by Foreign Key Constraints in SQL.

Feel free to leave any comments for clarification, changes, or improvements. Also, you can contact with iXora Solution expert teams for any consultation or inquiry from here.