What to choose for performance SubQuery or Joins Part 62

Text version of the video

Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.

Slides

All SQL Server Text Articles

All SQL Server Slides

All Dot Net and SQL Server Tutorials in English

All Dot Net and SQL Server Tutorials in Arabic

What to choose for performance - SubQuery or Joins

According to MSDN, in sql server, in most cases, there is usually no performance difference between queries that uses sub-queries and equivalent queries using joins. For example, on my machine I have
400,000 records in tblProducts table
600,000 records in tblProductSales tables

The following query, returns, the list of products that we have sold atleast once. This query is formed using sub-queries. When I execute this query I get 306,199 rows in 6 seconds
Select Id, Name, Description
from tblProducts
where ID IN
(
Select ProductId from tblProductSales
)

At this stage please clean the query and execution plan cache using the following T-SQL command.
CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
Go
DBCC FREEPROCCACHE;
GO

Now, run the query that is formed using joins. Notice that I get the exact same 306,199 rows in 6 seconds.
Select distinct tblProducts.Id, Name, Description
from tblProducts
inner join tblProductSales
on tblProducts.Id = tblProductSales.ProductId

Please Note: I have used automated sql script to insert huge amounts of this random data. Please watch Part 61 of SQL Server tutorial, in which we have discussed about this automated script.

According to MSDN, in some cases where existence must be checked, a join produces better performance. Otherwise, the nested query must be processed for each result of the outer query. In such cases, a join approach would yield better results.

The following query returns the products that we have not sold atleast once. This query is formed using sub-queries. When I execute this query I get 93,801 rows in 3 seconds
Select Id, Name, [Description]
from tblProducts
where Not Exists(Select * from tblProductSales where ProductId = tblProducts.Id)

When I execute the below equivalent query, that uses joins, I get the exact same 93,801 rows in 3 seconds.
Select tblProducts.Id, Name, [Description]
from tblProducts
left join tblProductSales
on tblProducts.Id = tblProductSales.ProductId
where tblProductSales.ProductId IS NULL

In general joins work faster than sub-queries, but in reality it all depends on the execution plan that is generated by SQL Server. It does not matter how we have written the query, SQL Server will always transform it on an execution plan. If sql server generates the same plan from both queries, we will get the same result.

I would say, rather than going by theory, turn on client statistics and execution plan to see the performance of each option, and then make a decision. In a later video session we will discuss about client statistics and execution plans in detail.

[anuragvashishtha]

I am loving it. The way you have build up the excitement for 'checking the Execution Plan' rather than going ahead with any pre-conceived notion about performance face-off between Joins and Sub Queries is really awesome. By clearing the 'cache' before judging either query you have ensured an equal battle-ground for Joins and Subquery and have taught an important lesson to those who have a keen interest in performance tuning. A Big Thanks for all your efforts.

[jillieduke]

You're videos are always so helpful. Thank you from Pennsylvania, U.S.A.

[Csharp-video-tutorialsBlogspot]

No Videos, on SQL Server jobs yet. Will record and upload as soon as I can.

[vikUSA]

This has been my doubt for years. Thanks so much for such a fantastic example and explanation.

[pallavhkust]

Damn! That was awesome. The sheer amount of content you have uploaded is great. Thank you for that. I can see the amount of effort and hard work you have put in. I will definitely donate once I start earning (student for now). I really appreciate the effort man! And you know, most of the people put out fancy videos with all the animation and stuff but in reality the content always win. :D

[malharjajoo]

This is a important doubt that many beginners have, thank you !

[bhavinshah]

Absolutely Awesome content and you have simplified at a very detailed level to understand.

[garimasingh]

awsome videos. thanks a lott for making such a great video .

[auroshisray]

I must say you have efficiently delivered the content.

[Trzbne]

You do not only teach us writing queries, but the practical way of thinking, too. Thank you very much for sharing this tutorial!

Could you please let me know, how do you switch back from results to the query editor window? There must be a shortcut...

[rahulmallik]

than's for the video. it helped me a lot. i also like join query

[rdg]

@Kudvenkat
Given that you've said the execution plan is the most important part, are  you still planning on creating a video on how to read the execution plan and client statistics?
I've viewed all your videos in this series, and it's a tremendous resource, Great Job!

[krzysztofs]

i can watch these videos even in a late night.

[shrikant]

Hi Sir, Thanks for the Explaining.TSQL Videos are really really helpful in understanding each concepts in details. I would like to know How can possible add column in specific position using alter commands ..?

[MrTrojis]

Does the execution plan and thus the performance depend upon the structure of the table? Say we had a very 'short and fat' table with few rows but many many columns versus a 'Tall and thin table' with many many rows but few columns. Which of these tables is more efficient for sql server to deal with?

A subquery I would imagine would work well with a 'short and fat' table since there are fewer rows to check, but a join would have a hard time because of all the columns
and vice versa for a 'tall and thin' table.

[cchelamkuri]

Find your videos very useful, please upload for reading execution plans

[pv]

Thanks for a good content. Could you please post link to the video you are referring to towards the end of this video on "how to interpret client stats and exec plan".

[reyrajesh]

Hi Venkat,
While we using DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE often in a remote server. Is there any damage will occur to the certain databases/server. Kindly suggest.?

[sanketnaik]

Hi sir, can u make a video for linq performance tuning

[vijayalakshmigogula]

Hi.. Could u pls tel me how the below scenario can be handled:
I have written the query (with joins and where conditions and subqueries)and saved it in my C drive (test.sql).
Now I want to read and execute that query and query results should be created and saved in D drive with new filename specified by us appended with date