Geek Sync | SQL Server Performance: The Myth of Table Joins

Performance Tuning is a complicated subject and when there are more multiple tables involved, tuning can get even more complex. DBAs and SQL developers often argue about the significance of join and how it plays its role in performance tuning.

Join IDERA and Pinal Dave on Wednesday, September 6 at 11 AM CT as he demystifies some of the most popular myths related to SQL Server joins, indexes, and performance. Pinal will discuss the difference between logical and physical joins, column order within joins, and how to optimize queries with multiple joins. He will also explain when the order of tables and columns matter when it comes to server performance. This will be an interactive Geek Sync you will not want to miss!

You can view Pinal's slides here. You can view the Geek Sync recording here.

You can view Pinal's scripts below:

 

1 - Ancient-ANSI.sql
USE AdventureWorks2014
GO
-- The Ancient Way
SELECT *
FROM Sales.SalesOrderDetail sod, Production.Product p
WHERE p.ProductID = sod.ProductID
GO
-- The ANSI Way
SELECT *
FROM Sales.SalesOrderDetail sod
INNER JOIN Production.Product p 
		ON p.ProductID = sod.ProductID
GO

2 - AllJoins.sql
USE AdventureWorks2014
GO
-- Loop Join
SELECT *
FROM Sales.SalesOrderDetail sod
INNER JOIN Production.Product p on p.ProductID = sod.ProductID
WHERE sod.UnitPrice < 1.37
--WHERE sod.UnitPrice BETWEEN 1121 AND 1213
GO
-- Merge Join
SELECT *
FROM Sales.SalesOrderDetail sod
INNER JOIN Production.Product p on p.ProductID = sod.ProductID
WHERE sod.UnitPrice < 2
GO
-- Hash Join
SELECT *
FROM Sales.SalesOrderDetail sod
INNER JOIN Production.Product p on p.ProductID = sod.ProductID
WHERE sod.UnitPrice < 3
GO