Multiple JOINs on one table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Multiple JOINs on one table

Author

Topic

Joe Rose
Starting Member

3 Posts

Posted - 2007-08-13 : 15:38:57

I'v got one table of persons' profile info from which I wish to select records based on varying criteria. Performance is the issue. Here's several SQL queries as constructed by my `aspx` form and their performance. The Person table has 14+ million records BTW.

Two tables
1. Groups - contains the comment data for each person in an Event set. Place, Date, type of Event.
2. Persons = contains the details specific to each person Names, age, relational to group set, etc.

High preformance results:
(Using the Father's surname and givenname)

SELECT Grp.Country, Grp.County, Grp.Parish, Grp.EventYear, Grp.EventMon, Grp.EventDay, Grp.EventDescr, Children.Surname, Children.GivenName AS 'Child', Children.Relation AS Gender, Fathers.GivenName AS FatherGiven, Fathers.Surname AS FatherSurname, Fathers.GivenName +' ' + Fathers.Surname AS 'Father', Mothers.GivenName AS MotherGiven, Mothers.Surname AS MotherSurname, Mothers.GivenName +' ' + Mothers.Surname AS 'Mother', Mothers.Relation, Grp.Notes, Children.Group_ID, Children.IGI_Record FROM IGI.dbo.IGIPerson Children LEFT OUTER JOIN IGI.dbo.IGIGroup Grp ON Children.Group_ID = Grp.Group_ID LEFT OUTER JOIN IGI.dbo.IGIPerson Fathers ON Grp.Group_ID = Fathers.Group_ID LEFT OUTER JOIN IGI.dbo.IGIPerson Mothers ON Grp.Group_ID = Mothers.Group_ID WHERE Fathers.Surname = @FatherSurname AND (Fathers.GivenName = @FatherGiven OR Fathers.GivenName = '???') AND Children.Surname = @FatherSurname AND Fathers.Relation = 'Father' AND Mothers.Relation = 'Mother' AND (Children.Relation = 'Son' OR Children.Relation = 'Daughter') AND Grp.Country = 'England' ORDER BY Grp.EventYear, Grp.Country, Grp.County, Grp.Parish, Children.Father, Children.Mother

* This should return as a flat recordset of the possible children where the father surname and givenname matches for selected country. Works great!

Low performance results:
(Added the mother's givenname to the above)

SELECT Grp.Country, Grp.County, Grp.Parish, Grp.EventYear, Grp.EventMon, Grp.EventDay, Grp.EventDescr, Children.Surname, Children.GivenName AS 'Child', Children.Relation AS Gender, Fathers.GivenName AS FatherGiven, Fathers.Surname AS FatherSurname, Fathers.GivenName +' ' + Fathers.Surname AS 'Father', Mothers.GivenName AS MotherGiven, Mothers.Surname AS MotherSurname, Mothers.GivenName +' ' + Mothers.Surname AS 'Mother', Mothers.Relation, Grp.Notes, Children.Group_ID, Children.IGI_Record FROM IGI.dbo.IGIPerson Children LEFT OUTER JOIN IGI.dbo.IGIGroup Grp ON Children.Group_ID = Grp.Group_ID LEFT OUTER JOIN IGI.dbo.IGIPerson Fathers ON Grp.Group_ID = Fathers.Group_ID LEFT OUTER JOIN IGI.dbo.IGIPerson Mothers ON Grp.Group_ID = Mothers.Group_ID WHERE Fathers.Surname = @FatherSurname AND (Fathers.GivenName = @FatherGiven OR Fathers.GivenName = '???') AND (Mothers.GivenName = @MotherGiven OR Mothers.GivenName = '???') AND Children.Surname = @FatherSurname AND Fathers.Relation = 'Father' AND Mothers.Relation = 'Mother' AND (Children.Relation = 'Son' OR Children.Relation = 'Daughter') AND Grp.Country = 'England' ORDER BY Grp.EventYear, Grp.Country, Grp.County, Grp.Parish, Children.Father, Children.Mother

* This should return as a flat recordset the possible children where the father AND mother matches provided names. Performance sucks!

Addendum:
High preformance results:
(Using the Father's givenname and Mother's surname and givenname)

SELECT Grp.Country, Grp.County, Grp.Parish, Grp.EventYear, Grp.EventMon, Grp.EventDay, Grp.EventDescr, Children.Surname, Children.GivenName AS 'Child', Children.Relation AS Gender, Fathers.GivenName AS FatherGiven, Fathers.Surname AS FatherSurname, Fathers.GivenName +' ' + Fathers.Surname AS 'Father', Mothers.GivenName AS MotherGiven, Mothers.Surname AS MotherSurname, Mothers.GivenName +' ' + Mothers.Surname AS 'Mother', Mothers.Relation, Grp.Notes, Children.Group_ID, Children.IGI_Record FROM IGI.dbo.IGIPerson Children LEFT OUTER JOIN IGI.dbo.IGIGroup Grp ON Children.Group_ID = Grp.Group_ID LEFT OUTER JOIN IGI.dbo.IGIPerson Fathers ON Grp.Group_ID = Fathers.Group_ID LEFT OUTER JOIN IGI.dbo.IGIPerson Mothers ON Grp.Group_ID = Mothers.Group_ID WHERE (Fathers.GivenName = @FatherGiven OR Fathers.GivenName = '???') AND Mothers.Surname = @MotherSurname AND (Mothers.GivenName = @MotherGiven OR Mothers.GivenName = '???') AND Children.Surname = @MotherSurname AND Fathers.Relation = 'Father' AND Mothers.Relation = 'Mother' AND (Children.Relation = 'Son' OR Children.Relation = 'Daughter') AND Grp.Country = 'England' ORDER BY Grp.EventYear, Grp.Country, Grp.County, Grp.Parish, Children.Father, Children.Mother

* * This should return as a flat recordset the possible children where the father givenname AND mother surname and givenname matches provided names. Works great!

I have looked at the indexing and see nothing I can put my finger on.

Is my basic construct wrong here? Will provide more info if required!

This web App services thousands of genealogy researchers.

Joe

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-13 : 16:16:42

what indexes are you already using on this table
and what columns they are on.

please list them all

Ashley Rhodes

Joe Rose
Starting Member

3 Posts

Posted - 2007-08-13 : 16:47:31

Hi Ashley;

1.Groups
a. Group_ID (int key)
b. Country (nchar)
c. EventDescr (nchar)

2. Persons:

a. PersonID (int key)
b. Group_ID (int)
b. Surname (nchar)
b. Relation (nchar)

Did you note I updated my original post?

When I use the Father's Surname & Givenname it works just fine.
When I use the Mother's Surname & Givenname it works just fine.
When I use the Father's Surname & Givenname and Mother's Surname it works just fine. (Mother's could be filed under their Nee. OR their Married Surname)

When I use the Father's Givenname & Mother's Surname & Givenname it works just fine.
When I use the Mother's Givenname & Father's Surname & Givenname it times out @240.

Me thinks it has something to do with how I'm doing the joining and their precedence in the plan. ie If the father has one or both names it's a left join and if the mother has one or both names is it a right join or something like that?

I can post the aspx code segment if that would help.

Form requires at least one surname!

Joe

Ifor
Aged Yak Warrior

700 Posts

Posted - 2007-08-14 : 06:45:03

1. Your OUTER JOINs are a waste of time. Fathers.Relation = 'Father', Mothers.Relation = 'Mother'
and Grp.Country = 'England' will get rid of any NULLs produced by the OUTER JOINs. If you want all the
children in IGIPerson you will need to put the filters in the ON clause. (I will assume you really want
INNER JOINS as that will give the same results as your current queries.)

2. Comparing the join to Mothers in the first two queries:

FROM IGI.dbo.IGIPerson Children
    JOIN IGI.dbo.IGIGroup Grp
        ON Children.Group_ID = Grp.Group_ID
            AND Grp.Country = 'England'
    JOIN IGI.dbo.IGIPerson Fathers
        ON Children.Group_ID = Fathers.Group_ID
            AND Children.Surname = Fathers.Surname
            AND Fathers.Relation = 'Father'
            AND (Fathers.GivenName = @FatherGiven OR Fathers.GivenName = '???')
    JOIN IGI.dbo.IGIPerson Mothers
        ON Grp.Group_ID = Mothers.Group_ID
            AND Mothers.Relation = 'Mother'
            -- 2nd query only
            AND (Mothers.GivenName = @MotherGiven OR Mothers.GivenName = '???')
WHERE Children.Surname = @FatherSurname
    AND (Children.Relation = 'Son' OR Children.Relation = 'Daughter')

It would seem that Group_ID is the clustered index on IGIPerson. This means that the index on:
- PersonID covers PersonID, Group_ID.
- Surname covers Surname, Group_ID.
- Relation, covers Relation, Group_ID.
(If the clustered index is not Group_ID then the index on Surname must be very selective in order to get the speed.)
ie In the first query the JOIN to Mothers in covered by the index on Relation.
The second query has to get both relataion and GivenName so probably a table scan is done as I doubt the index on Relation is very selective.
If you want to speed up the second query try something like dropping the index on Relation and creating an index on Relation, GivenName.
(As with all index alterations this may effect performance elsewhere in your system.)

Joe Rose
Starting Member

3 Posts

Posted - 2007-08-14 : 16:16:34

Thanks!

That was some help.

The real problem is this:

Using:
SELECT Count(*) as Recs, Mothers.GivenName, Mothers.Surname, Mothers.Relation
FROM IGI.dbo.IGIPerson Mothers
JOIN IGI.dbo.IGIGroup Grp ON Grp.Group_ID = Mothers.Group_ID
AND Grp.Country = 'England'
WHERE
(Mothers.GivenName = 'Susanna' OR Mothers.GivenName = '???')
AND Mothers.Relation = 'Mother'
GROUP BY Mothers.GivenName, Mothers.Surname, Mothers.Relation

Got this:
(7474 row(s) affected)

It's obvious that I been to re-think the user selectable options in my aspx code.

Somewhat related to the above....

Does anyone know if a SQL process (job) can be killed off from asp.net? The idea is to put this into my web forms so that if a user triggers some other eventhandler while a long running query is underway that the new postback code would kill the previous SQL job.
I have looked at the sp's and scripts other folks have written to kill all the processes on a DB but I have found nothing that specifically kills a process that web session has already started.

Of course this may not be possible if the ID of ther first process cannot be trapped during the sqlDataSource's selecting event. I will be looking at this after this message.

Joe

mattyblah
Starting Member

49 Posts

Posted - 2007-08-14 : 22:01:41

every process can be killed (AFAIK). you just have to find the appropriate one (process). of course, the topic of killing processes is another discussion...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources