examples of idioms What to choose for performance SubQuery or Joins Part 62

Text version of the video
http://csharp-video-tutorials.blogspo...

Slides
http://csharp-video-tutorials.blogspo...

All SQL Server Text Articles
http://csharp-video-tutorials.blogspo...

All SQL Server Slides
http://csharp-video-tutorials.blogspo...

All Dot Net and SQL Server Tutorials in English
https://www.youtube.com/user/kudvenka...

All Dot Net and SQL Server Tutorials in Arabic
https://www.youtube.com/c/KudvenkatAr...

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.

examples of idioms What to choose for performance SubQuery or Joins Part 62
340 Likes	340 Dislikes
100,377 views views	524K followers
Education	Upload TimePublished on 12 Jan 2013

Related keywords

asp.net core docker,wcf vs web api,ado.net core,sql server management studio,webkinz,webadvisor,craigslist nj,wcf one piece,asp.net core 3,ajax players,performance hall ankara,examples of personification,weber grill parts,webtoon,tutorials by hugo,csharp corner,mvc design pattern,asp.net machine account,servers for minecraft,asp.net core dependency injection,ado.net tutorial,performance art,services angular,better business bureau ny,performance bond nedir,ajax ontario,example resume,better call saul,performance power akülü vidalama,asp.net mvc tutorial,examples of alliteration,asp.net cos'è,csharp assembly,tutorialspoint python,sql join,services briefcase,asp.net core web api,ajax jquery,wccftech,craigslist ny,performance power l tipi promo akülü vidalama,asp.net zero,csharp foreach,server memes,sql date format,services online,chase,examples of culture,performance power,serverless architecture,server resume,wcf c#,server books,tutorialspoint javascript,mvcc connect,ado.net mysql,services technologies gps,better than us netflix,ajax deadpool,server jobs,cvs,website,mvc tutorial,costco hours,wcf service application,examples of proteins,tutorialspoint spring,serverless,wcf soap,sql server connection string,sql server tutorial,wcf cat,wcf test client,cool math games,performance test 9,services & training hse colombia sas,servicestack,citibank,asp.net core identity,sql union,ajax parking,sql database,asp.net core logging,mvconnect,asp.net guida,cunyfirst,wcf nba,csharp download,wcfi foundation,csharp online,wcf authentication,tutorials near me,better now,http://asp.net,server 2019,ado.net descargar,web of dreams,chernobyl,serverminer,ajax cleaner,ado.net visual studio 2019,webassign,ado.net vs entity framework,better off dead,ado.net visual studio 2017,csharp list,sql like,asp.net mvc,asp.net core tutorial,sqlite,wcfm,ajax roster,mvc architecture,performance jant,performance lastik,http://ado.net,asp.net core mvc,ajax soccer,sql server configuration manager,server hosting,examples of parasitism,wcfi,ajax dish soap,capital one,server rack,tutorialspoint html,csharp interface,performance maximizer,craigslist,sql server download,webroot,tutorialspoint reactjs,ajax request,wcf dragon ball,bettercloud,asp.net core 2.2,tutorialspoint python 3,sql developer,webster,sql server date format,services transmission company sas,sql group by,asp.net core signalr,services manager,mvc framework,ajax paving,mvc near me,examples of irony,better than us,performance evaluation,examples of figurative language,tutorialspoint spring boot,mvc map,csharp online compiler,asp.net download,sql between,ado.net c# pdf,wcf 2019 nba,services tag dell,better than sex mascara,csharp switch,ado.net ventajas y desventajas,csharpstar,performance task,wcf tutorial,sql server 2016 download,examples of adjectives,betterment,tutorialspoint,ajax meaning,csharp-video-tutorials.blogspot,tutorials dojo,csharp string format,central park 5,performance test,ado.net c#,asp.net core github,server status,ajax fc,server jobs nyc,asp.net core swagger,sql formatter,credit karma,services group,better now lyrics,sql server 2008,server error in '/' application,services windows,asp.net core 3.0,sql injection,wcf ria services,performance ne demek,tutorialspoint c#,calculator,ado.net entity data model,sql insert,tutorialspoint tableau,services google play apk,sqlyog,asp.net core 3 release date,sql server,server job description,tutorials by a,servicenow,performance bond,examples of lipids,webcam,mvc hours,webmd symptom,csharp array,csharp enum,ajax call,asp.net core 2. guida completa per lo sviluppatore,asp.net core,server pro,better homes and gardens,server status ffxiv,webcrims,cheap flights,asp.net core hosting,services sas,examples of metaphors,tutorialspoint java,tutorialspoint java compiler,examples of adverbs,webmd,sql server 2014 download,csharp to json,college football,ado.net dataset,csharp dictionary,better world books,examples of tone,cnn,website builder,better things,better mortgage,tutorialspoint sql,asp.net web api,server side rendering,weber grills,sql server 2014,performance task metu,sql server 2016,sql server 2017,example synonym,mvc nj,sql server 2012,tutorialspoint spark,ado.net oracle,asp.net core download,csharp to vb.net,sql server 2019,webster bank,better lyrics,performance management,webstaurant,examples of resignation letters,tutorialsystems,ajax post,services fms publish announcement,services.msc no abre,ajax jersey,csharp operator,asp.net core razor pages,server duties,asp.net core environment variables,csharp random,better help,century 21,examples of carbohydrates,sql server data tools,services consultores,services consulting,mvcsd,mvcsc,services.msc,ado.net pdf,asp.net core configuration,ajax marvel,performance marketing,sql update,asp.net tutorial,mvc medical,example of mutualism,ado.net entity data model visual studio 2019,performance power matkap,wcfs international curriculum,better staten island,performance hall,mvc2,ado.net entity data model visual studio 2017,better synonym,chase online,wcf api,costco,server jobs near me,webex,sql meaning,sql tutorial,sql server express,sql commands,sql server 2008 r2,better business bureau,ado.net entity framework,ajax tavern,example cover letter,sql server versions,tutorialsteacher,ajax javascript,better business bureau nj,services desk,ajax greek,csharp tutorial,mvc pattern,ado.net sql server,ado.net connection,asp.net identity,mvcu,example of commensalism,asp.net core middleware,sql server management studio 2014,wcf web service,mvc webadvisor,web store,mvcc,webmail,mvci,mvctc,sql server management studio 2012,