Convert *= / =* to outer joins (ANSI Join)

Hi,

I'm working on converting *= and =* to 'left outer join' and 'right outer join'.

I noticed the difference in behavior between the =* and the phrase "right outer join" and between *= and 'left outer join'. The result set is different. Here is an example:

select a.au_id, b.title, c.qty

from titleauthor a, titles b, sales c

where (a.title_id =* b.title_id)

and (a.title_id =* c.title_id)

I try to conver the above to:

select a.au_id, b.title, c.qty

from titleauthor a

right outer join titles b

on (a.title_id = b.title_id )

right outer join sales c

on (a.title_id = c.title_id )

The first results into 391 rows in pubs database of sql-server 2000 and the second produces 34 rows. It seems not that straight forward to convert.

The question is: If I want to get 391 row, what should I change/add in the second sql statement?

Thank you. Appreciate your help.

Joan

I think this:

select a.au_id, b.title, c.qty

from titleauthor a, titles b, sales c

where (a.title_id =* b.title_id)

and (a.title_id =* c.title_id)

Is actually not a right join between these tables directly, but actually more this query:

--changed the a b c aliases to just be the table for clarity
select titleauthor.au_id, titles.title, sales.qty
from sales
cross join titles
left outer join titleauthor
on titles.title_id = titleauthor.title_id
and sales.title_id = titleauthor.title_id

The commas can be translated directly to CROSS JOIN, with the where being the same. So in your query, since there is no link between sales and titles, it does a cross join. You can see this by looking at the plan:

Code Snippet

SET SHOWPLAN_ALL ON
GO

select a.au_id, b.title, c.qty
from titleauthor a, titles b, sales c
where (a.title_id =* b.title_id)
and (a.title_id =* c.title_id)

SET SHOWPLAN_ALL OFF
GO

|--Hash Match(Right Outer Join, HASH:([a].[title_id])=([b].[title_id]),
RESIDUAL:([pubs].[dbo].[titleauthor].[title_id] as
[a].[title_id]=[pubs].[dbo].[titles].[title_id] as [b].[title_id]
AND [pubs].[dbo].[titleauthor].[title_id] as
[a].[title_id]=[pubs].[dbo].[sales].[title_id] as [c].[title_id]))
|--Index Scan(OBJECT:([pubs].[dbo].[titleauthor].[titleidind] AS [a]))
|--Nested Loops(Inner Join)
|--Index Scan(OBJECT:([pubs].[dbo].[titles].[titleind] AS [b]))

|--Clustered Index Scan(OBJECT:([pubs].[dbo].[sales].[UPKCL_sales] AS [c]))

Note, no join criteria. Hopefully this helps, it is one of the reasons why the syntax was changed
|||

Hi,

Is it because the question is not clear?

Joan

|||You mean the query? It is clear enough, it is just a matter of what you are asking. The ANSI Style is far more clear to express queries as they are going to be/can be expressed.

The old style was ambiguous like this and was based on the concept that you (logically) first cross join each of the tables in the FROM, then for each row check the criteria. Works like a champ for INNER joins, but OUTER joins not so much.|||

Hi Louis,

Thank you so much!

I'll try to fix my query. I might have question later.

Thanks again.

Joan

|||

Hi Louis,

Here I get another example. The old code is as follows:

Select CU.Name, CI.Amount, CI.PolicyItemId, CI.timestamp, CU.CreditSurchargeId, CU.ValueType_ES ,CU.IsDefault, CU.IsModifiable, CU.IsCredit, CP.ObjectCategoryId, CU.Type_ES, CP.DisplayOrder ,CP.Amount, CP.Effectivedateid
from CreditSurchargePolicyLine CP, CreditSurchargeUnit CU, CreditSurchargePolicyLineItem CI
where CP.PolicyLineId = 4
and CP.ObjectSubjectId = 4
and CP.Status_ES = 'A'
and CP.CreditSurchargeId = CU.CreditSurchargeId
and CP.ObjectSubjectId = CU.ObjectSubjectId
and CU.CreditSurchargeId *= CI.CreditSurchargeId
and CI.PolicyItemId = 30153677
and CI.PolicyLineItemId = 1
and CP.ObjectCategoryId in (0, 1)
and CP.CreditSurchargeId <> 79
and CP.CreditSurchargeId <> 78
and CP.CreditSurchargeId <> 83
and CP.effectivedateID = 1

I converted as follows:

Select CU.Name, CI.Amount, CI.PolicyItemId, CI.timestamp, CU.CreditSurchargeId, CU.ValueType_ES, CU.IsDefault, CU.IsModifiable, CU.IsCredit, CP.ObjectCategoryId, CU.Type_ES, CP.DisplayOrder ,CP.Amount, CP.Effectivedateid
from CreditSurchargePolicyLine CP JOIN CreditSurchargeUnit CU ON CP.CreditSurchargeId = CU.CreditSurchargeId
AND CP.ObjectSubjectId = CU.ObjectSubjectId
LEFT OUTER JOIN CreditSurchargePolicyLineItem CI ON (CU.CreditSurchargeId = CI.CreditSurchargeId)
where CP.PolicyLineId = 4
and CP.ObjectSubjectId = 4
and CP.Status_ES = 'A'
and CI.PolicyItemId = 30153677
and CI.PolicyLineItemId = 1
and CP.EffectiveDateId = 1
and CP.ObjectCategoryId in (0, 1)
and CP.CreditSurchargeId <> 79
and CP.CreditSurchargeId <> 78
and CP.CreditSurchargeId <> 83

The old code returns 23 rows, which it should be. But the new code returns 11 rows, which is not correct. But I can't tell what is wrong with the new code.

Thank you!

Joan

|||

This query may fix your problem..

Code Snippet

Select
CU.Name
, CI.Amount
, CI.PolicyItemId
, CI.timestamp
, CU.CreditSurchargeId
, CU.ValueType_ES
, CU.IsDefault
, CU.IsModifiable
, CU.IsCredit
, CP.ObjectCategoryId
, CU.Type_ES
, CP.DisplayOrder
, CP.Amount
, CP.Effectivedateid
from
CreditSurchargePolicyLine CP

JOIN CreditSurchargeUnit CU ON
CP.CreditSurchargeId = CU.CreditSurchargeId
AND CP.ObjectSubjectId = CU.ObjectSubjectId

LEFT OUTER JOIN CreditSurchargePolicyLineItem CI ON
CU.CreditSurchargeId = CI.CreditSurchargeId
And CI.PolicyItemId = 30153677
And CI.PolicyLineItemId = 1

where
CP.PolicyLineId = 4
and CP.ObjectSubjectId = 4
and CP.Status_ES = 'A'
and CP.EffectiveDateId = 1
and CP.ObjectCategoryId in (0, 1)
and CP.CreditSurchargeId <> 79
and CP.CreditSurchargeId <> 78
and CP.CreditSurchargeId <> 83

|||

Thank you ManiD! Thank you all!

|||

You are welcome.

Remember when you use left/right outer join, if you want to apply any filter attach that filter on JOIN condition itself - rather than

at where clause.

-Mani.D

|||

>>Remember when you use left/right outer join, if you want to apply any filter attach that filter on JOIN condition itself - rather than at where clause.<<

In spirit this is usually right, but this isn't quite true. You have to be careful and cognizant about where to put FILTER criteria, but it can go either place. You just have to realize that:

In the JOIN clause, a condition is applied to the joining of the two sets of data. And from the left side of a LEFT join will be returned no matter what (or the right side of a RIGHT join or both sides of a FULL join for that matter

In the WHERE clause, the condition is applied the the set of rows produced from the FROM clause. So if a row was returned in the FROM clause as the result of an LEFT OUTER JOIN, if you then try to filter out the data by comparing data from the right table, all of the values will be NULL. So they will be filtered out unless you realize this.
sqlsql

Convert *= / =* to outer joins (ANSI compliant)

Hi,

I'm working on converting *= and =* to 'left outer join' and 'right outer join'.

I noticed the difference in behavior between the =* and the phrase "right outer join" and between *= and 'left outer join'. The result set is different. Here is an example:

select a.au_id, b.title, c.qty

from titleauthor a, titles b, sales c

where (a.title_id =* b.title_id)

and (a.title_id =* c.title_id)

I try to conver the above to:

select a.au_id, b.title, c.qty

from titleauthor a

right outer join titles b

on (a.title_id = b.title_id )

right outer join sales c

on (a.title_id = c.title_id )

The first results into 391 rows in pubs database of sql-server 2000 and the second produces 34 rows. It seems not that straight forward to convert.

The question is: If I want to get 391 row, what should I change/add in the second sql statement?

Thank you. Appreciate your help.

Joan

I think this:

select a.au_id, b.title, c.qty

from titleauthor a, titles b, sales c

where (a.title_id =* b.title_id)

and (a.title_id =* c.title_id)

Is actually not a right join between these tables directly, but actually more this query:

--changed the a b c aliases to just be the table for clarity
select titleauthor.au_id, titles.title, sales.qty
from sales
cross join titles
left outer join titleauthor
on titles.title_id = titleauthor.title_id
and sales.title_id = titleauthor.title_id

The commas can be translated directly to CROSS JOIN, with the where being the same. So in your query, since there is no link between sales and titles, it does a cross join. You can see this by looking at the plan:

Code Snippet

SET SHOWPLAN_ALL ON
GO

select a.au_id, b.title, c.qty
from titleauthor a, titles b, sales c
where (a.title_id =* b.title_id)
and (a.title_id =* c.title_id)

SET SHOWPLAN_ALL OFF
GO

|--Hash Match(Right Outer Join, HASH:([a].[title_id])=([b].[title_id]),
RESIDUAL:([pubs].[dbo].[titleauthor].[title_id] as
[a].[title_id]=[pubs].[dbo].[titles].[title_id] as [b].[title_id]
AND [pubs].[dbo].[titleauthor].[title_id] as
[a].[title_id]=[pubs].[dbo].[sales].[title_id] as [c].[title_id]))
|--Index Scan(OBJECT:([pubs].[dbo].[titleauthor].[titleidind] AS [a]))
|--Nested Loops(Inner Join)
|--Index Scan(OBJECT:([pubs].[dbo].[titles].[titleind] AS [b]))

|--Clustered Index Scan(OBJECT:([pubs].[dbo].[sales].[UPKCL_sales] AS [c]))

Note, no join criteria. Hopefully this helps, it is one of the reasons why the syntax was changed
|||

Hi,

Is it because the question is not clear?

Joan

|||You mean the query? It is clear enough, it is just a matter of what you are asking. The ANSI Style is far more clear to express queries as they are going to be/can be expressed.

The old style was ambiguous like this and was based on the concept that you (logically) first cross join each of the tables in the FROM, then for each row check the criteria. Works like a champ for INNER joins, but OUTER joins not so much.|||

Hi Louis,

Thank you so much!

I'll try to fix my query. I might have question later.

Thanks again.

Joan

|||

Hi Louis,

Here I get another example. The old code is as follows:

Select CU.Name, CI.Amount, CI.PolicyItemId, CI.timestamp, CU.CreditSurchargeId, CU.ValueType_ES ,CU.IsDefault, CU.IsModifiable, CU.IsCredit, CP.ObjectCategoryId, CU.Type_ES, CP.DisplayOrder ,CP.Amount, CP.Effectivedateid
from CreditSurchargePolicyLine CP, CreditSurchargeUnit CU, CreditSurchargePolicyLineItem CI
where CP.PolicyLineId = 4
and CP.ObjectSubjectId = 4
and CP.Status_ES = 'A'
and CP.CreditSurchargeId = CU.CreditSurchargeId
and CP.ObjectSubjectId = CU.ObjectSubjectId
and CU.CreditSurchargeId *= CI.CreditSurchargeId
and CI.PolicyItemId = 30153677
and CI.PolicyLineItemId = 1
and CP.ObjectCategoryId in (0, 1)
and CP.CreditSurchargeId <> 79
and CP.CreditSurchargeId <> 78
and CP.CreditSurchargeId <> 83
and CP.effectivedateID = 1

I converted as follows:

Select CU.Name, CI.Amount, CI.PolicyItemId, CI.timestamp, CU.CreditSurchargeId, CU.ValueType_ES, CU.IsDefault, CU.IsModifiable, CU.IsCredit, CP.ObjectCategoryId, CU.Type_ES, CP.DisplayOrder ,CP.Amount, CP.Effectivedateid
from CreditSurchargePolicyLine CP JOIN CreditSurchargeUnit CU ON CP.CreditSurchargeId = CU.CreditSurchargeId
AND CP.ObjectSubjectId = CU.ObjectSubjectId
LEFT OUTER JOIN CreditSurchargePolicyLineItem CI ON (CU.CreditSurchargeId = CI.CreditSurchargeId)
where CP.PolicyLineId = 4
and CP.ObjectSubjectId = 4
and CP.Status_ES = 'A'
and CI.PolicyItemId = 30153677
and CI.PolicyLineItemId = 1
and CP.EffectiveDateId = 1
and CP.ObjectCategoryId in (0, 1)
and CP.CreditSurchargeId <> 79
and CP.CreditSurchargeId <> 78
and CP.CreditSurchargeId <> 83

The old code returns 23 rows, which it should be. But the new code returns 11 rows, which is not correct. But I can't tell what is wrong with the new code.

Thank you!

Joan

|||

This query may fix your problem..

Code Snippet

Select
CU.Name
, CI.Amount
, CI.PolicyItemId
, CI.timestamp
, CU.CreditSurchargeId
, CU.ValueType_ES
, CU.IsDefault
, CU.IsModifiable
, CU.IsCredit
, CP.ObjectCategoryId
, CU.Type_ES
, CP.DisplayOrder
, CP.Amount
, CP.Effectivedateid
from
CreditSurchargePolicyLine CP

JOIN CreditSurchargeUnit CU ON
CP.CreditSurchargeId = CU.CreditSurchargeId
AND CP.ObjectSubjectId = CU.ObjectSubjectId

LEFT OUTER JOIN CreditSurchargePolicyLineItem CI ON
CU.CreditSurchargeId = CI.CreditSurchargeId
And CI.PolicyItemId = 30153677
And CI.PolicyLineItemId = 1

where
CP.PolicyLineId = 4
and CP.ObjectSubjectId = 4
and CP.Status_ES = 'A'
and CP.EffectiveDateId = 1
and CP.ObjectCategoryId in (0, 1)
and CP.CreditSurchargeId <> 79
and CP.CreditSurchargeId <> 78
and CP.CreditSurchargeId <> 83

|||

Thank you ManiD! Thank you all!

|||

You are welcome.

Remember when you use left/right outer join, if you want to apply any filter attach that filter on JOIN condition itself - rather than

at where clause.

-Mani.D

|||

>>Remember when you use left/right outer join, if you want to apply any filter attach that filter on JOIN condition itself - rather than at where clause.<<

In spirit this is usually right, but this isn't quite true. You have to be careful and cognizant about where to put FILTER criteria, but it can go either place. You just have to realize that:

In the JOIN clause, a condition is applied to the joining of the two sets of data. And from the left side of a LEFT join will be returned no matter what (or the right side of a RIGHT join or both sides of a FULL join for that matter

In the WHERE clause, the condition is applied the the set of rows produced from the FROM clause. So if a row was returned in the FROM clause as the result of an LEFT OUTER JOIN, if you then try to filter out the data by comparing data from the right table, all of the values will be NULL. So they will be filtered out unless you realize this.

conversion *= to left outer joins not yielding desired results

Hi

I have a query which works fine on sql 2000 with *= but doesn't yield the same when converted to 2005 using joins

Here is the original query

SELECT tmpex.pa_number, tmpex.big_deal_id, tmpex.custname, c.iso_code, c.name, pf.product_family_name,
ISNULL(dcts.tatsla, tscd.tatsla), tmpex.psm_contact
FROM country c, product_family pf,
ship_to_country sc, dealcountry_tatsla dcts, tatsla_countrydefaults tscd, #TAT_EXPORT_DEALVALUES tmpex
WHERE
c.id_country = sc.id_country
AND sc.id_deal = tmpex.id_deal

AND sc.id_country *= dcts.id_country

AND sc.id_deal *= dcts.id_deal
AND pf.id_product_family *= dcts.id_product_family
AND sc.id_country = tscd.id_country
AND pf.id_product_family = tscd.id_product_family

The Converted one is below

select tmpex.pa_number, tmpex.big_deal_id, tmpex.custname, c.iso_code, c.name, pf.product_family_name,
ISNULL(dcts.tatsla, tscd.tatsla), tmpex.psm_contact
from
ship_to_country sc INNER JOIN country c
ON sc.id_country = c.id_country
INNER JOIN #TAT_EXPORT_DEALVALUES tmpex
ON (sc.id_deal = tmpex.id_deal )
INNER JOIN tatsla_countrydefaults tscd1
ON (sc.id_country = tscd1.id_country)
LEFT OUTER JOIN
dealcountry_tatsla dcts
ON (sc.id_country = dcts.id_country and sc.id_deal=dcts.id_deal )
LEFT OUTER JOIN product_family pf
ON (pf.id_product_family = dcts.id_product_family)
INNER JOIN tatsla_countrydefaults tscd
ON (pf.id_product_family = tscd.id_product_family)

Please let me know whether I am missing something

The join query results in cartesian product

If I remember old style syntax correctly, the new version will looks like this:

SELECT tmpex.pa_number, tmpex.big_deal_id, tmpex.custname, c.iso_code, c.name, pf.product_family_name,
ISNULL(dcts.tatsla, tscd.tatsla), tmpex.psm_contact
FROM country c
inner join ship_to_country sc on c.id_country = sc.id_country
inner join #TAT_EXPORT_DEALVALUES tmpex on sc.id_deal = tmpex.id_deal
inner join tatsla_countrydefaults tscd on sc.id_country = tscd.id_country
inner join product_family pf on pf.id_product_family = tscd.id_product_family
left join dealcountry_tatsla dcts on sc.id_country = dcts.id_country
and sc.id_deal = dcts.id_deal and pf.id_product_family = dcts.id_product_family

Not sure it is equivalent, though, so thorough testing is highly recommended.|||Your Query seems to be correct. Have you checked the result set. Is there any flaw in that.|||No, I've not checked this - I have no underlying data to test on. If I had, I'd have no doubt

But since you have both the tables and the data, you always may test its correctness - either on MSSQL 2000 or, if all that you have is MSSQL 2005, on a separate database with compatibility level set to 80. In the latter case, both queries will work even on 2005.sqlsql

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,
DannyIt 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/abouttn/subscriptions/flash/tips/tips_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/abouttn/subscriptions/flash/tips/tips_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
>