Constraints and query plans

Does having enforced foreign key constraints between tables help query
performance? In our reporting database we have many views with multiple
joins so that report writing is easier. But with each additional join the
optimizer generally scans or seeks the index on all joined tables whether or
not the query requests columns from the table. Currently there are no
foreign key constraints, by adding and enforcing them would the queries
produce better plans?
Thanks,
Danny
It depends on the query, but in some cases the optimizer does take advantage
of constraints. Also, you may want to consider indexing some of your FK
columns.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Danny" <djscroggins@.verizon.net> wrote in message
news:iqbUf.3553$4N1.230@.trnddc06...
Does having enforced foreign key constraints between tables help query
performance? In our reporting database we have many views with multiple
joins so that report writing is easier. But with each additional join the
optimizer generally scans or seeks the index on all joined tables whether or
not the query requests columns from the table. Currently there are no
foreign key constraints, by adding and enforcing them would the queries
produce better plans?
Thanks,
Danny
|||Danny
> Does having enforced foreign key constraints between tables help query
> performance?
Actually NO. However it is a good practice to create an index on FK column
and then it does improve perfomance.
FK is a logical concept. It prevents from an unexpectred deletion for
example.
Please read an article about FK in the BOL get a whole picture.
"Danny" <djscroggins@.verizon.net> wrote in message
news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>
|||Can you give me a basic example of where the optimizer would take advantage
of a foreign key constraint? I understand creating indexes on the colums.
In any cases does it decide not to seek or scan an index because of a
constraint is in place? Or is it that the optimizer has more information
for find the optimal plan where as with just indexes it may stop and choose
a plan that is good enough?
Our views get very complex due to the number of joins. When a query has
more than about six joins the number of potential plans is really large and
sometimes the resulting plan is not optimal. We are hoping that in 2005 the
optimizer does a better job with many joins.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23foIBzaTGHA.792@.TK2MSFTNGP10.phx.gbl...
> It depends on the query, but in some cases the optimizer does take
> advantage
> of constraints. Also, you may want to consider indexing some of your FK
> columns.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Danny" <djscroggins@.verizon.net> wrote in message
> news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or
> not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>
|||IIRC, doing a WHERE EXISTS/NOT EXISTS can be expedited with a FK in some
circumstances. Here's an example. Run the following script with Show
Execution Plan turned on (Ctrl+K):
use tempdb
go
select
*
into
Orders
from
Northwind.dbo.Orders
select
*
into
OrderDetails
from
Northwind.dbo.[Order Details]
alter table Orders
add
constraint PK_Orders primary key (OrderID)
alter table OrderDetails
add
constraint PK_OrderDetails primary key (OrderID, ProductID)
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
alter table OrderDetails
add
constraint FK1_OrderDetails foreign key (OrderID) references Orders
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
the last two SELECT's are identical, but the second one has a lower query
cost.
Also, CHECK constraints do make a difference in partitioned views, since
only the tables whose CHECK constraints satisfy the search criteria are
tapped.
In 2005, there are plan guides that may be of assistance to you:
http://msdn2.microsoft.com/en-us/library/ms190417(en-US,SQL.90).aspx
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Danny" <djscroggins@.verizon.net> wrote in message
news:lWkUf.8672$I7.2391@.trnddc03...
Can you give me a basic example of where the optimizer would take advantage
of a foreign key constraint? I understand creating indexes on the colums.
In any cases does it decide not to seek or scan an index because of a
constraint is in place? Or is it that the optimizer has more information
for find the optimal plan where as with just indexes it may stop and choose
a plan that is good enough?
Our views get very complex due to the number of joins. When a query has
more than about six joins the number of potential plans is really large and
sometimes the resulting plan is not optimal. We are hoping that in 2005 the
optimizer does a better job with many joins.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23foIBzaTGHA.792@.TK2MSFTNGP10.phx.gbl...
> It depends on the query, but in some cases the optimizer does take
> advantage
> of constraints. Also, you may want to consider indexing some of your FK
> columns.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Danny" <djscroggins@.verizon.net> wrote in message
> news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or
> not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>
|||Sorry about that but the example I gave you doesn't produce the desired
result. (I was comparing the query cost of the FK build with the SELECT.)
The rest of the commentary still stands. I'll see if I can conjure up some
code.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O9PnB1gTGHA.6048@.TK2MSFTNGP11.phx.gbl...
IIRC, doing a WHERE EXISTS/NOT EXISTS can be expedited with a FK in some
circumstances. Here's an example. Run the following script with Show
Execution Plan turned on (Ctrl+K):
use tempdb
go
select
*
into
Orders
from
Northwind.dbo.Orders
select
*
into
OrderDetails
from
Northwind.dbo.[Order Details]
alter table Orders
add
constraint PK_Orders primary key (OrderID)
alter table OrderDetails
add
constraint PK_OrderDetails primary key (OrderID, ProductID)
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
alter table OrderDetails
add
constraint FK1_OrderDetails foreign key (OrderID) references Orders
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
the last two SELECT's are identical, but the second one has a lower query
cost.
Also, CHECK constraints do make a difference in partitioned views, since
only the tables whose CHECK constraints satisfy the search criteria are
tapped.
In 2005, there are plan guides that may be of assistance to you:
http://msdn2.microsoft.com/en-us/library/ms190417(en-US,SQL.90).aspx
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Danny" <djscroggins@.verizon.net> wrote in message
news:lWkUf.8672$I7.2391@.trnddc03...
Can you give me a basic example of where the optimizer would take advantage
of a foreign key constraint? I understand creating indexes on the colums.
In any cases does it decide not to seek or scan an index because of a
constraint is in place? Or is it that the optimizer has more information
for find the optimal plan where as with just indexes it may stop and choose
a plan that is good enough?
Our views get very complex due to the number of joins. When a query has
more than about six joins the number of potential plans is really large and
sometimes the resulting plan is not optimal. We are hoping that in 2005 the
optimizer does a better job with many joins.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23foIBzaTGHA.792@.TK2MSFTNGP10.phx.gbl...
> It depends on the query, but in some cases the optimizer does take
> advantage
> of constraints. Also, you may want to consider indexing some of your FK
> columns.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Danny" <djscroggins@.verizon.net> wrote in message
> news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or
> not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>
|||And here it is! :-) Basically, I just changed the NOT EXISTS to EXISTS.
In the query plan, note that the SELECT after the FK has been added does not
refer to the Orders table at all:
select
*
into
Orders
from
Northwind.dbo.Orders
select
*
into
OrderDetails
from
Northwind.dbo.[Order Details]
alter table Orders
add
constraint PK_Orders primary key (OrderID)
alter table OrderDetails
add
constraint PK_OrderDetails primary key (OrderID, ProductID)
go
select
*
from
OrderDetails od
where exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
alter table OrderDetails
add
constraint FK1_OrderDetails foreign key (OrderID) references Orders
go
select
*
from
OrderDetails od
where exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
drop table OrderDetails, Orders
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eXmNc6gTGHA.2656@.TK2MSFTNGP10.phx.gbl...
Sorry about that but the example I gave you doesn't produce the desired
result. (I was comparing the query cost of the FK build with the SELECT.)
The rest of the commentary still stands. I'll see if I can conjure up some
code.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O9PnB1gTGHA.6048@.TK2MSFTNGP11.phx.gbl...
IIRC, doing a WHERE EXISTS/NOT EXISTS can be expedited with a FK in some
circumstances. Here's an example. Run the following script with Show
Execution Plan turned on (Ctrl+K):
use tempdb
go
select
*
into
Orders
from
Northwind.dbo.Orders
select
*
into
OrderDetails
from
Northwind.dbo.[Order Details]
alter table Orders
add
constraint PK_Orders primary key (OrderID)
alter table OrderDetails
add
constraint PK_OrderDetails primary key (OrderID, ProductID)
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
alter table OrderDetails
add
constraint FK1_OrderDetails foreign key (OrderID) references Orders
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
the last two SELECT's are identical, but the second one has a lower query
cost.
Also, CHECK constraints do make a difference in partitioned views, since
only the tables whose CHECK constraints satisfy the search criteria are
tapped.
In 2005, there are plan guides that may be of assistance to you:
http://msdn2.microsoft.com/en-us/library/ms190417(en-US,SQL.90).aspx
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Danny" <djscroggins@.verizon.net> wrote in message
news:lWkUf.8672$I7.2391@.trnddc03...
Can you give me a basic example of where the optimizer would take advantage
of a foreign key constraint? I understand creating indexes on the colums.
In any cases does it decide not to seek or scan an index because of a
constraint is in place? Or is it that the optimizer has more information
for find the optimal plan where as with just indexes it may stop and choose
a plan that is good enough?
Our views get very complex due to the number of joins. When a query has
more than about six joins the number of potential plans is really large and
sometimes the resulting plan is not optimal. We are hoping that in 2005 the
optimizer does a better job with many joins.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23foIBzaTGHA.792@.TK2MSFTNGP10.phx.gbl...
> It depends on the query, but in some cases the optimizer does take
> advantage
> of constraints. Also, you may want to consider indexing some of your FK
> columns.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Danny" <djscroggins@.verizon.net> wrote in message
> news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or
> not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>
|||Actually, YES:
http://www.microsoft.com/technet/abo...ps_122104.mspx
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23fbPQzaTGHA.4140@.TK2MSFTNGP10.phx.gbl...
Danny
> Does having enforced foreign key constraints between tables help query
> performance?
Actually NO. However it is a good practice to create an index on FK column
and then it does improve perfomance.
FK is a logical concept. It prevents from an unexpectred deletion for
example.
Please read an article about FK in the BOL get a whole picture.
"Danny" <djscroggins@.verizon.net> wrote in message
news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>
|||In a large reporting environment, the differences between foreign key
constraints when using views is usually negligible.
In other words, the solution I think you are using is a bunch of large
canned views showing a gazillion columns, and then reports pick and
choose teh data and columns they really need from that view.
These views are VERY slow as the optimizer has a tough time figuring
out which of the gazillion indexes to utilize to get the right data.
The next step is to pass parameters to a stored procedure for
frequently used, particularly slow queries. By simply moving the code
from a view to a stored procedure, speed will come back.
In the longer run, if you can afford it, OLAP is the BEST reporting
solution for analysts. It is sooooo much faster, it is unreal, but
there is a learning curve for all involved.
|||Thanks.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23iS339sTGHA.4900@.TK2MSFTNGP12.phx.gbl...
> Actually, YES:
> http://www.microsoft.com/technet/abo...ps_122104.mspx
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23fbPQzaTGHA.4140@.TK2MSFTNGP10.phx.gbl...
> Danny
> Actually NO. However it is a good practice to create an index on FK column
> and then it does improve perfomance.
> FK is a logical concept. It prevents from an unexpectred deletion for
> example.
> Please read an article about FK in the BOL get a whole picture.
>
> "Danny" <djscroggins@.verizon.net> wrote in message
> news:iqbUf.3553$4N1.230@.trnddc06...
>