A detailed Guide on sql query Optimization This article was published as a part of the

IN versus EXISTS
IN operator is more costly than EXISTS in terms of scans especially when the result of the subquery is a
large dataset. So we should try to use EXISTS rather than using IN for fetching results with a subquery.
Let us see this with an example,
SET STATISTICS TIME ON
SELECT 
ProductNumber,Name,Color 
FROM 
SalesLT.Product 
WHERE 
ProductID 
IN 
(SELECT 
ProductID 
FROM
SalesLT.ProductDescription)
SET STATISTICS TIME ON SELECT ProductNumber,Name,Color FROM SalesLT.Product WHERE EXISTS (SELECT ProductID
FROM SalesLT.ProductDescription)
We have executed the same query having a subquery with IN command and EXISTS commands and we
observe that the EXISTS command takes half of the time as compared to IN command and the number of
physical and logical scans is very low.
Loops versus Bulk insert/update
The loops must be avoided because it requires running the same query many times. Instead, we should opt
for bulk inserts and updates.

---

SET STATISTICS TIME ON DECLARE @Counter INT SET @Counter=1 WHILE ( @Counter <= 10) BEGIN PRINT 'The counter
value is = ' + CONVERT(VARCHAR,@Counter) INSERT INTO [SalesLT].[ProductDescription] ([Description] ,[rowguid]
,[ModifiedDate]) VALUES ('This is great' ,NEWID() ,'12/01/2010') SET @Counter = @Counter + 1 END
USE 
[AdventureWorksLT2019] 
GO 
SET 
STATISTICS 
TIME 
ON 
INSERT 
INTO 
[SalesLT].[ProductDescription]
([Description] ,[rowguid] ,[ModifiedDate]) VALUES ('This is great' ,NEWID() ,'12/01/2010'), ('New news'
,NEWID() ,'12/01/2010'), ('Awesome product.' ,NEWID() ,'12/01/2010'), .........., ('Awesome product.'
,NEWID() ,'12/01/2010') GO
As we have seen above bulk insert works faster than loop statements.
Conclusion
Some other things to keep in mind:
1. Avoid using correlated nested queries.
2. Avoid inner joins with Equality or OR conditions.
3. Check whether records exist before fetching them.
4. Use indexing properly, try to create more indexes for fetching composite columns.
5. Use Wildcards wisely.
. Try to use WHERE rather than HAVING. Only use HAVING for aggregated values.
So we learned that how minor changes in queries can improve the performance of the query drastically.
This will boost the performance of applications providing a better user experience. Keep all the guidelines
in mind while writing queries.
And finally, it does not go without saying,
Thanks for reading!
The media shown in this article are not owned by Analytics Vidhya and are used at the Author’s
discretion.
Article Url - 
https://www.analyticsvidhya.com/blog/2021/10/a-detailed-guide-on-sql-query-optimization/
Megha

---