May 13, 2013 at 8:37 am
It seems that join elimination is not occurring when a table has a foreign key to the table underlying a view. Is there something else I need to do for the optimizer to know the join to a view can be eliminated?
In our dimensional model, we have a view for DimApplicationDate that is based on DimDate, simply renaming DateKey to ApplicationDateKey. In my fact table I have a foreign key on ApplicationDateKey that references DateKey in DimDate. If I do not use ApplicationDate in my query, the join to DimDate is still not eliminated. This seems to be the case for all my virtual dimensions.
May 13, 2013 at 8:47 am
It could be that the constraint is not trusted. If you ever modified the constraint so it won't be checked and didn't changed it so it will be checked again or changed it so it will be active again but without the "with check" clause, then the constraint can not be trusted. In this case the optimizer won't take the constraint into consideration. You can check it with the objectproperty function. If this is the case, you should run alter statement that checks the constraint.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 13, 2013 at 12:30 pm
Thanks for the prompt reply. However, I am not sure that is the problem. When I look at the properties of the constraint, it says it is trusted. These are foreign keys, so we don't have separate check constraints. I was not familiar with what your answer spelled out, so I may have misinterpreted something.
Am I looking at something wrong? Here is what I see in Mgmt Studio
Check Existing Data on Creation or Re-Enabling = Yes
Enforce Foreign Key Constraint = Yes
The foreign setup is also looking at the proper table and key column.
May 14, 2013 at 3:26 am
I have to admit that I have no idea how to check it using the GUI, and I don't know where you got the data. In any case the code and the remarks bellow show you what I meant. Also notice that there could be other reasons for this behavior, but without other details (for example the view's defenetion, tables' size etc') it will be very hard to know the reason that you don't get the table elimination.
USE AdventureWorks
go
--The query does a join on 2 tables, but if you'll chec the query plan, you'll see that includes only 1.
--This is because there is a foreign key constraints between the tables, so there can not be a record
--in SalesOrderDetail without a corosponding record in SalesOrderHeader table
SELECT SOD.*
FROM Sales.SalesOrderDetail SOD inner join Sales.SalesOrderHeader SOH ON SOD.SalesOrderID = SOH.SalesOrderID
--check the constraint that is called FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID
exec sp_helpconstraint 'Sales.SalesOrderDetail'
--Now lets change the constraint and "deactivate" it. We can update or insert data that vaiolate the constraint.
alter table Sales.SalesOrderDetail nocheck constraint FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID
--Check the query plan now. It will be different, because the constraint can not be trusted anymore
SELECT SOD.*
FROM Sales.SalesOrderDetail SOD inner join Sales.SalesOrderHeader SOH ON SOD.SalesOrderID = SOH.SalesOrderID
--Enabling the constraint agian and checking the query plan again
alter table Sales.SalesOrderDetail check constraint FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID
go
--After we enabled the constraint again, we still have a query plan that checks both tables.
--This is because the constrain is still untrusted. We are not able to modify data that will
--violate the constraint, but we need to check if the data that is already in the talbe meets the
--constraint's criteria
SELECT SOD.*
FROM Sales.SalesOrderDetail SOD inner join Sales.SalesOrderHeader SOH ON SOD.SalesOrderID = SOH.SalesOrderID
--Checking if the constraint is trusted
--The constraint is not trusted
select objectproperty(object_id,'CnstIsNotTrusted')
from sys.objects
where name = 'FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID'
--Enabling the constraint agian, but this time also check the data that is already in the table
alter table Sales.SalesOrderDetail WITH CHECK check constraint FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID
go
--Now the query plan acesses only 1 table again
SELECT SOD.*
FROM Sales.SalesOrderDetail SOD inner join Sales.SalesOrderHeader SOH ON SOD.SalesOrderID = SOH.SalesOrderID
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 14, 2013 at 12:08 pm
When I check the objectproperty(object_id,'CnstIsNotTrusted'), I get a value of zero, which I presume means the constraint is trusted. I even ran the "CHECK check" alter statement and saw no changes. I do not see anything different about these foreign keys, except they are referencing the base table of a view.
Here is the view that is created, which just renames the DimGenericDate.GenericDateKey to ApplicationDateKey. Following that is the SQL used when ApplicationDateKey was added to the fact table, including the addition of the foreign key constraint.
CREATE view CustomFinal.DimApplicationDate
as
select
GenericDateKey as ApplicationDateKey
,DaysFromZero
,FullDate
,DateName
,DayOfWeek
,DayNameOfWeek
from CustomFinal.DimGenericDate
alter table Student.FactApplications
add ApplicationDateKey int not null default -1,
constraint fk_FactApplications_ApplicationDateKey
foreign key (ApplicationDateKey)
references Custom.DimGenericDate (GenericDateKey)
While coming up with all this code, I figured something else out as well. Assuming I am not using anything from DimApplicationDate, if my SELECT does a JOIN to DimApplicationDate on ApplicationDateKey, then the join to DImGenericDate is NOT eliminated. If I join to DimGenericDate on GenericDateKey = ApplicationDateKey, then the join IS eliminated.
For some reason, the join will not be eliminated when joining to a view.
May 14, 2013 at 12:41 pm
Fiddlesticks! I have confirmed that the join elimination does in fact work with a view, using the AdventureWorksDW2008 database. Unfortunately, the foreign key constraints look exactly the same in both databases. Now I need to find out what settings are effecting this behavior and check them in both databases. Or find some other reason it works in one but not the other. This behavior is definitely not working in my database. :angry:
-- Join to DimDate directly... this works. The join to DimDate is eliminated.
select dc.LastName, sum(f.SalesAmount)
from FactInternetSales f
join DimCustomer dc
on dc.CustomerKey = f.CustomerKey
join DimDate do
on do.DateKey = f.OrderDateKey
group by dc.LastName
-- Create a view that uses DimDate, renaming DateKey to OrderDateKey (to match name in fact table)
create view TestDimOrderDate AS
SELECT DateKey AS OrderDateKey,
FullDateAlternateKey,
DayNumberOfWeek
FROM DimDate
-- Now join to the new view, and DimDate is still eliminated
select dc.LastName, sum(f.SalesAmount)
from FactInternetSales f
join DimCustomer dc
on dc.CustomerKey = f.CustomerKey
join TestDimOrderDate do
on do.OrderDateKey = f.OrderDateKey
group by dc.LastName
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply