Recently a friend of mine asked, that is it true that presence of extra tables in joins section of a query, will affect query performance. Extra tables means,tables which can be skipped from query without affecting query result. For example following query has extra tables (other than vendor and contact tables) in join section
USE AdventureWorks
GO
SELECT Vendor.Name,
Contact.Title,
Contact.FirstName,
Contact.MiddleName
FROM Person.Address AS a
INNER JOIN Purchasing.VendorAddress AS VendorAddress
ON a.AddressID = VendorAddress.AddressID
INNER JOIN Person.StateProvince AS StateProvince
ON StateProvince.StateProvinceID = a.StateProvinceID
INNER JOIN Person.CountryRegion AS CountryRegion
ON CountryRegion.CountryRegionCode = StateProvince.CountryRegionCode
INNER JOIN Purchasing.Vendor AS Vendor
INNER JOIN Purchasing.VendorContact AS VendorContact
ON VendorContact.VendorID = Vendor.VendorID
INNER JOIN Person.Contact AS Contact
ON Contact.ContactID = VendorContact.ContactID
INNER JOIN Person.ContactType AS ContactType
ON VendorContact.ContactTypeID = ContactType.ContactTypeID
ON VendorAddress.VendorID = Vendor.VendorID
Though this is NOT common to have extra tables in our usual queries but it could be possible in views. A view can be created with multiple tables and selecting columns from each joined table. And later on when we will query this view we can use only few columns in our select statement. So when we will execute above query SQL Server Query Analyzer will skip all those tables which are not part of game. Here is execution plan of above query.
Same query with more columns, pushing all tables in action.
SELECT Vendor.Name,
ContactType.Name AS ContactType,
Contact.Title,
Contact.FirstName,
Contact.MiddleName,
a.AddressLine1,
a.AddressLine2,
a.City,
StateProvince.Name AS StateProvinceName,
a.PostalCode,
CountryRegion.Name AS CountryRegionName,
Vendor.VendorID
FROM Person.Address AS a
INNER JOIN Purchasing.VendorAddress AS VendorAddress
ON a.AddressID = VendorAddress.AddressID
INNER JOIN Person.StateProvince AS StateProvince
ON StateProvince.StateProvinceID = a.StateProvinceID
INNER JOIN Person.CountryRegion AS CountryRegion
ON CountryRegion.CountryRegionCode = StateProvince.CountryRegionCode
INNER JOIN Purchasing.Vendor AS Vendor
INNER JOIN Purchasing.VendorContact AS VendorContact
ON VendorContact.VendorID = Vendor.VendorID
INNER JOIN Person.Contact AS Contact
ON Contact.ContactID = VendorContact.ContactID
INNER JOIN Person.ContactType AS ContactType
ON VendorContact.ContactTypeID = ContactType.ContactTypeID
ON VendorAddress.VendorID = Vendor.VendorID
If we create a view using our second query and use our view in following style then execution plan will be same to our first query.
SELECT Name,
Title,
FirstName,
MiddleName
FROM vw_MyView
Hence, SQL Server Query Analyser is quite smart and work on only those tables which are part of actual game and it doesn’t matter that extra tables are part of your query or a view.
Another brilliant post :)
ReplyDeleteRegards,
Syed Muhammad Yasir
I wonder if that's the same for MySQL. Is MySQL as intelligent as SQL Server to automatically ignore unnecessary tables in views?
ReplyDeleteAlthough, does this feature (ignoring such tables) have a cost? And can it be disabled?
I am not sure about MySQL, but SQL Server Optimizer (backbone of SQL Server) is quite intelligent and ignore unwanted tables itself. Its a must thing for optimizer so can't be disabled.
Delete