Many-to-Many Self Joins ?

Hi Folks,
I need to implement a many-to-many self join for a table of acronyms ie each
acronym may _reference_ one or more other acronyms and, in addition, each
acronym may _be referenced by_ one or more other acronyms. I tried to
utilize the code in
"http://www.tomjewett.com/dbdesign/dbdesign.php?page=recursive.php&imgsize=medium"
see DDL and data below . However the results I got from the query don't
appear correct. Can you see where I've gone wrong?
Thanks, Simon
CREATE TABLE [Acronyms] (
[Acronym_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Acronym] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Description] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
CONSTRAINT [PK_Acronyms] PRIMARY KEY CLUSTERED
(
[Acronym_ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Acronym_ID Acronym Description
1 ABC ABC
2 DEF DEF
3 XYZ XYZ
CREATE TABLE [Synonyms] (
[Acronym_ID] [int] NOT NULL ,
[Synonym_ID] [int] NOT NULL ,
[Start_Date] [datetime] NOT NULL ,
[End_Date] [datetime] NULL ,
CONSTRAINT [PK_Synonyms] PRIMARY KEY CLUSTERED
(
[Acronym_ID],
[Synonym_ID],
[Start_Date]
) ON [PRIMARY] ,
CONSTRAINT [FK_Synonyms_Acronyms] FOREIGN KEY
(
[Acronym_ID]
) REFERENCES [Acronyms] (
[Acronym_ID]
),
CONSTRAINT [FK_Synonyms_Acronyms1] FOREIGN KEY
(
[Synonym_ID]
) REFERENCES [Acronyms] (
[Acronym_ID]
)
) ON [PRIMARY]
GO
Acronym_ID Synonym_ID Start_Date End_Date
2 1 01-Jan-05
3 1 06-Jun-05
1 2 02-Feb-05
1 3 02-Feb-05
SELECT TOP 100 PERCENT A.Acronym AS Acronym, R.Acronym AS Synonym
FROM dbo.Acronyms A LEFT OUTER JOIN
dbo.Synonyms S INNER JOIN
dbo.Acronyms R ON S.Acronym_ID = R.Acronym_ID ON
A.Acronym_ID = S.Acronym_ID
ORDER BY A.Acronym
Results...
Acronym Synonym
ABC ABC
ABC ABC
DEF DEF
XYZ XYZ
Hi Simon,
What's your expected result? Could you clarify more? In the following
article, the author will show a simple example that traverses a hierarchy
and displays the output in indented format.
Working with hierarchical data in SQL Server databases
http://vyaskn.tripod.com/hierarchies..._databases.htm
BTW, In SQL Server 2005, we support Common Table Expression support
recursive directly.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Michael,
I don't believe my problem is simply hierarchical given that an entity can
have more than one parent
Simon
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:af9Cmm26FHA.1236@.TK2MSFTNGXA02.phx.gbl...
> Hi Simon,
> What's your expected result? Could you clarify more? In the following
> article, the author will show a simple example that traverses a hierarchy
> and displays the output in indented format.
> Working with hierarchical data in SQL Server databases
> http://vyaskn.tripod.com/hierarchies..._databases.htm
> BTW, In SQL Server 2005, we support Common Table Expression support
> recursive directly.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
|||Hi Simon,
I cannot see the image in link
"http://www.tomjewett.com/dbdesign/dbdesign.php?page=recursive.php&imgsize=m
edium", would you please clarify the structure of your samples more?
What's your expected result?
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Many-to-Many Self Joins ?

Hi Folks,
I need to implement a many-to-many self join for a table of acronyms ie each
acronym may _reference_ one or more other acronyms and, in addition, each
acronym may _be referenced by_ one or more other acronyms. I tried to
utilize the code in
"http://www.tomjewett.com/dbdesign/dbdesign.php?page=recursive.php&imgsize=m
edium"
see DDL and data below . However the results I got from the query don't
appear correct. Can you see where I've gone wrong?
Thanks, Simon
CREATE TABLE [Acronyms] (
[Acronym_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Acronym] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT N
ULL ,
[Description] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT
NULL ,
CONSTRAINT [PK_Acronyms] PRIMARY KEY CLUSTERED
(
[Acronym_ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Acronym_ID Acronym Description
1 ABC ABC
2 DEF DEF
3 XYZ XYZ
CREATE TABLE [Synonyms] (
[Acronym_ID] [int] NOT NULL ,
[Synonym_ID] [int] NOT NULL ,
[Start_Date] [datetime] NOT NULL ,
[End_Date] [datetime] NULL ,
CONSTRAINT [PK_Synonyms] PRIMARY KEY CLUSTERED
(
[Acronym_ID],
[Synonym_ID],
[Start_Date]
) ON [PRIMARY] ,
CONSTRAINT [FK_Synonyms_Acronyms] FOREIGN KEY
(
[Acronym_ID]
) REFERENCES [Acronyms] (
[Acronym_ID]
),
CONSTRAINT [FK_Synonyms_Acronyms1] FOREIGN KEY
(
[Synonym_ID]
) REFERENCES [Acronyms] (
[Acronym_ID]
)
) ON [PRIMARY]
GO
Acronym_ID Synonym_ID Start_Date End_Date
2 1 01-Jan-05
3 1 06-Jun-05
1 2 02-Feb-05
1 3 02-Feb-05
SELECT TOP 100 PERCENT A.Acronym AS Acronym, R.Acronym AS Synonym
FROM dbo.Acronyms A LEFT OUTER JOIN
dbo.Synonyms S INNER JOIN
dbo.Acronyms R ON S.Acronym_ID = R.Acronym_ID ON
A.Acronym_ID = S.Acronym_ID
ORDER BY A.Acronym
Results...
Acronym Synonym
ABC ABC
ABC ABC
DEF DEF
XYZ XYZHi Simon,
What's your expected result? Could you clarify more? In the following
article, the author will show a simple example that traverses a hierarchy
and displays the output in indented format.
Working with hierarchical data in SQL Server databases
http://vyaskn.tripod.com/hierarchie...r_databases.htm
BTW, In SQL Server 2005, we support Common Table Expression support
recursive directly.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Michael,
I don't believe my problem is simply hierarchical given that an entity can
have more than one parent
Simon
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:af9Cmm26FHA.1236@.TK2MSFTNGXA02.phx.gbl...
> Hi Simon,
> What's your expected result? Could you clarify more? In the following
> article, the author will show a simple example that traverses a hierarchy
> and displays the output in indented format.
> Working with hierarchical data in SQL Server databases
> http://vyaskn.tripod.com/hierarchie...r_databases.htm
> BTW, In SQL Server 2005, we support Common Table Expression support
> recursive directly.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi Simon,
I cannot see the image in link
"http://www.tomjewett.com/dbdesign/dbdesign.php?page=recursive.php&imgsize=m
edium", would you please clarify the structure of your samples more?
What's your expected result?
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.sql

Many-to-Many Self Joins ?

Hi Folks,
I need to implement a many-to-many self join for a table of acronyms ie each
acronym may _reference_ one or more other acronyms and, in addition, each
acronym may _be referenced by_ one or more other acronyms. I tried to
utilize the code in
"http://www.tomjewett.com/dbdesign/dbdesign.php?page=recursive.php&imgsize=medium"
see DDL and data below . However the results I got from the query don't
appear correct. Can you see where I've gone wrong?
Thanks, Simon
CREATE TABLE [Acronyms] (
[Acronym_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Acronym] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Description] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
CONSTRAINT [PK_Acronyms] PRIMARY KEY CLUSTERED
(
[Acronym_ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Acronym_ID Acronym Description
1 ABC ABC
2 DEF DEF
3 XYZ XYZ
CREATE TABLE [Synonyms] (
[Acronym_ID] [int] NOT NULL ,
[Synonym_ID] [int] NOT NULL ,
[Start_Date] [datetime] NOT NULL ,
[End_Date] [datetime] NULL ,
CONSTRAINT [PK_Synonyms] PRIMARY KEY CLUSTERED
(
[Acronym_ID],
[Synonym_ID],
[Start_Date]
) ON [PRIMARY] ,
CONSTRAINT [FK_Synonyms_Acronyms] FOREIGN KEY
(
[Acronym_ID]
) REFERENCES [Acronyms] (
[Acronym_ID]
),
CONSTRAINT [FK_Synonyms_Acronyms1] FOREIGN KEY
(
[Synonym_ID]
) REFERENCES [Acronyms] (
[Acronym_ID]
)
) ON [PRIMARY]
GO
Acronym_ID Synonym_ID Start_Date End_Date
2 1 01-Jan-05
3 1 06-Jun-05
1 2 02-Feb-05
1 3 02-Feb-05
SELECT TOP 100 PERCENT A.Acronym AS Acronym, R.Acronym AS Synonym
FROM dbo.Acronyms A LEFT OUTER JOIN
dbo.Synonyms S INNER JOIN
dbo.Acronyms R ON S.Acronym_ID = R.Acronym_ID ON
A.Acronym_ID = S.Acronym_ID
ORDER BY A.Acronym
Results...
Acronym Synonym
ABC ABC
ABC ABC
DEF DEF
XYZ XYZHi Simon,
What's your expected result? Could you clarify more? In the following
article, the author will show a simple example that traverses a hierarchy
and displays the output in indented format.
Working with hierarchical data in SQL Server databases
http://vyaskn.tripod.com/hierarchies_in_sql_server_databases.htm
BTW, In SQL Server 2005, we support Common Table Expression support
recursive directly.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Michael,
I don't believe my problem is simply hierarchical given that an entity can
have more than one parent
Simon
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:af9Cmm26FHA.1236@.TK2MSFTNGXA02.phx.gbl...
> Hi Simon,
> What's your expected result? Could you clarify more? In the following
> article, the author will show a simple example that traverses a hierarchy
> and displays the output in indented format.
> Working with hierarchical data in SQL Server databases
> http://vyaskn.tripod.com/hierarchies_in_sql_server_databases.htm
> BTW, In SQL Server 2005, we support Common Table Expression support
> recursive directly.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi Simon,
I cannot see the image in link
"http://www.tomjewett.com/dbdesign/dbdesign.php?page=recursive.php&imgsize=m
edium", would you please clarify the structure of your samples more?
What's your expected result?
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Many left joins and slow performance

I am combining several columns and tables and I am wondering if there is a way to improve performance, for instance can I write an implicit join version of the following code, rather than having these explicit joins?

objCmd = new OleDbCommand ("SELECT MAmunicipalities.*, MAcitytown.*, countiesalias1.countyname, countiesalias1.countylinktitle AS clink1, countiesalias2.countyname, countiesalias2.countylinktitle AS clink2, countiesalias3.countyname, countiesalias3.countylinktitle AS clink3 FROM (MAmunicipalities LEFT OUTER JOIN MAcitytown ON MAmunicipalities.citytown = MAcitytown.citytown) LEFT OUTER JOIN MAcounties AS countiesalias1 ON MAcitytown.county1 = countiesalias1.countyname LEFT OUTER JOIN MAcounties AS countiesalias2 ON MAcitytown.county2 = countiesalias2.countyname LEFT OUTER JOIN MAcounties AS countiesalias3 ON MAcitytown.county3 = countiesalias3.countyname WHERE MAmunicipalities.municipality='plymouth'", objConn);

Sadly, I have even more joins left to add as well as other columns to add to the SELECT statement and more aliases to add as well. Any thoughts would be greatly appreciated.

So far, this appears to be a five table JOIN. That in itself 'shouldn't be a preformance issue.

Judicious usage of TABLE aliases would make the code a bit more readible (and maintainable).

And it is widely considered a 'best practice' to specifically identify columns instead of using [ SELECT * ].

For Example:

Code Snippet

SELECT
m.*,
c.*,
c1.CountyName,
c1.CountyLinkTitle AS cLink1,
c2.CountyName,
c2.CountyLinkTitle AS cLink2,
c3.CountyName,
c3.CountyLinkTitle AS cLink3
FROM MaMunicipalities m
LEFT JOIN MaCityTown c
ON m.CityTown = c.CityTown
LEFT JOIN MaCounties c1
ON c.County1 = c1.CountyName
LEFT JOIN MaCounties c2
ON c.County2 = c2.CountyName
LEFT JOIN MaCounties c3
ON c.County3 = c3.CountyName
WHERE m.Municipality = 'plymouth'

Are there additional tables to JOIN, or is it additional JOINs to the same tables (like with MaCounties above)?

|||I have additional tables to join. Right now it is taking between 10 and 15 seconds for a page to load using the code that is posted. I have indexed columns within those tables which has helped somewhat but it is still taking way too long. I suspect that having varchars being joined instead of ints is also contributing to the problem. Would a stored procedure help?|||

A stored procedure may help some -but its doubtful that it would provide the kind of improvement you really need.

As you suspect, the major issue is most likely the varchar() fields used for the JOINs.

One of the most significant things that help with speed on JOINs is the 'width' of an index. An integer field is 4 bytes. A varchar() field is as many bytes as characters (nvarchar() is double that.) The 'wider' the index, the fewer entries on an index page, therefore the more pages that have to be 'crawled' through and read to find the data.

Ideally, your CityTown and CountyName values would be integer values (CityTownID and CountyNameID).

I suggest that you explore using the Database Tuning Advisor to get assistance in fine tuning the indexes.

Refer to Books Online, Topic: 'Database Engine Tuning Advisor'