performance_testing.sql

If (Exists (select * from information_schema.tables  where table_name = 'tblProductSales')) 
Begin Drop Table tblProductSales End

If (Exists (select * from information_schema.tables where table_name = 'tblProducts'))
 Begin Drop Table tblProducts End

Create Table tblProducts 
( [Id] int identity primary key, [Name] nvarchar(50), [Description] nvarchar(250) )

Create Table tblProductSales 
( Id int primary key identity, 
ProductId int foreign key references tblProducts(Id),
 UnitPrice int, 
 QuantitySold int )

DECLARE @Id INT
SET @Id =1
WHILE(@Id<=300000)
BEGIN
	INSERT INTO tblProducts
	VALUES('Product-'+CAST(@Id AS VARCHAR(20)), 'Product-'+CAST(@Id AS VARCHAR(20))+'-Description')
	PRINT @Id
	SET @Id = @Id+1
END

SELECT * FROM tblProducts

DECLARE @Count INT = 1
WHILE(@Count<=450000)
BEGIN
INSERT INTO tblProductSales
VALUES(
ROUND((100000-1)*RAND()+1,0),
ROUND((100-1)*RAND()+1,0),
ROUND((10-1)*RAND()+1,0)
)
SET @Count = @Count+1
END
PRINT @Count


CHECKPOINT; 
GO 
DBCC DROPCLEANBUFFERS; -- Clears query cache
Go
DBCC FREEPROCCACHE; -- Clears execution plan cache
GO

Select Id, Name, Description
from tblProducts
where ID IN
(
 Select ProductId from tblProductSales
)

Select distinct tblProducts.Id, Name, Description
from tblProducts
inner join tblProductSales
on tblProducts.Id = tblProductSales.ProductId


--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.