Database Reference
In-Depth Information
create unique clustered index IDX_Positions_CompanyId_OnTime_RecId
on dbo.Positions(CompanyId, OnTime, RecId);
create nonclustered index IDX_Positions_CompanyId_DeviceId_OnTime
on dbo.Positions(CompanyId, DeviceId, OnTime);
Let's create the view that joins these tables, as shown in Listing 9-12.
Listing 9-12. Join elimination and multi-column foreign key constraints: View creation
create view dbo.vPositions(CompanyId, OnTime, RecId, DeviceId, DeviceName, Latitude, Longitude)
as
select p.CompanyId, p.OnTime, p.RecId, p.DeviceId, d.DeviceName, p.Latitude, p.Longitude
from dbo.Positions p join dbo.Devices d on
p.CompanyId = d.CompanyId and p.DeviceId = d.DeviceId;
Now let's run the select shown in Listing 9-13. This select returns the columns from the Positions table only, and
it produces the execution plan shown in Figure 9-5 .
Listing 9-13. Join elimination and multi-column foreign key constraints: Select from vPositions view
select OnTime, DeviceId, Latitude, Longitude
from dbo.vPositions
where CompanyId = @CompanyId and OnTime between @StartTime and @StopTime
Figure 9-5. Execution plan with multi-column foreign key constraints
Even with a foreign key constraint in place, you still have the join. SQL Server does not perform join elimination
when a foreign key constraint has more than one column. Unfortunately, there is very little you can do in such a
situation to perform join elimination. You can use the approach with the outer joins, although it is worth considering
querying the tables directly rather than using views in such a scenario.
Finally, SQL Server does not perform join elimination, even with single column foreign key constraints, when
tables are created in tempdb . You need to keep this in mind if you use tempdb as the staging area for the ETL processes
when you load the data from external sources and do some processing and data transformation before inserting it into
user database.
Search WWH ::




Custom Search