*******************************************************
I've got a structure which looks vaguely like this:
[ANCESTORS]
Grandparent
Parent
Child
If limit by grandparents, then I only get the lineage for that particular grandparent. I.e.:
SELECT *
FROM [ANCESTORS]
(some sortof joins here )
Where Grandparent.Name ='Cybill'
This would return all of the children of 'Cybill' and their children. Now, if I use the following query:
SELECT *
FROM [ANCESTORS]
(some sortof joins here )
Where Child.Name ='Jean'
This would return Jean's parents + the parents of Jean's parents (Jean's grandparents).
Likewise, if I enter:
SELECT *
FROM [ANCESTORS]
(some sortof joins here )
Where Parent.Name ='Ron'
Then I would get Ron's parents and also his children.
*******************************************************
So, as you can see, at first it appears that I'm after a LEFT JOIN - meaning that the grandparents don't need to have child records to be returned, but, then it turns out that I need INNER JOINS - to limit grandparents when I choose children.
Can anybody see my dilemma here?
Mark-up ASP.net posts here
MarkItUp.com... no. I don't. I've done something like this before with someone, and we didn't go about that method. :(
What's the problem with doing inner joins?|||Because, with Inner JOINs I am relying upon the existance of children to be able to return the grandparents.|||I actually have one table "Ancestors" and also a separate linking table which contains the relationships:
[Ancestors] ( id int, Name varchar )
[Relationships] ( id, fkid )
What I need to be able to do is to write a single query which can span "up to 7" lines of descendency, that is:
Great-Great-Great-Grandparent
Great-Great-Grandparent
Great-Great-Grandparent
Great-Grandparent
Grandparent
Parent
Child
I just have absolutely no idea how to write a single sql query which could filter on one or more levels but ensure referential integrity down the line. In other words, I can specify a child which would trace up the tree in a single line, or, specify a Great-Great-Great-Grandparent which would span out from a single point and would show all the way down to leaf nodes regardless of which level they finished at.|||Can we use a UDF? :-)|||Yes, I'm using SqlServer 2K.
I have finally tuned the stored procedure "program" enough to get my - previously 14 hour (give or take 100 milliseconds) - query down to sub-20 seconds. This is good enough to ship to the client so, I'm going to go with what I have for now.|||20 seconds? I'm still not believing that to be good enough. :) but if it's good enough for the client, it's good enough I guess.
If you're using any temp tables or what not, try indexing them before you apply data to them. I trimmed a procedure that executed in 12 seconds to 2.|||KraGiE, I agree, 20 seconds is a long time to wait, but, it's working so that's a much better position than I had 2 days ago.
I do have # temp tables, about 12 of them actually (more on that later), and my final optimization was to add indexes and optimization hints in the appropriate places, this reduced a 2 minute 20 second query to sub 30 seconds.
As for the 12 temp tables, this, to me was a fair indicator that the schema of the database was wrong to begin with so now, I 'm actually pushing to have the db schema altered too. I'm hoping that this will give me enough of a foothold to be able to achieve the necessary remaining improvements|||Personally, I'm a big fan of int based look up tables. :) Well, in cases like this, I'm fond of them because you can branch them out as far as you want without having just 'add a new field'.
I also think loops are my best friend when used properly.
What I meant by the indexes (if this is what you're doing already, then I'm a redundant moron) is ...
|||Yeh, that's pretty much what I'm doing already re: the indexes ( although who am I to call you a redundant moron :P ) except that I create the index only after population and, therefore also set the fill factor to 100% - but, for the purposes of an illustration, yes, that's pretty much what I do.|||awesome. In some cases, I'll put the index in before I start populating if the data's going to be enormous. It sets a bit faster from my experience.
SET NOCOUNT ONCREATE TABLE #TempTable
(
tableID int IDENTITY,
TableField varchar(50)
SomeOtherVar int
)
GOCREATE INDEX idx_Temp
ON #TempTable ( tableID )
GOINSERT INTO #TempTable
SELECT
TableID,
TableField,
SomeOtherVar
FROM
RealTable
-- WHERE Your CriteriaGO
-- Do Other Calculations
TRUNCATE TABLE #TempTable
DROP INDEX idx_Temp
DROP TABLE #TempTableGO
-- Yes Terri, I space my sql in procedures and QA this way normally, and I'm just lazy when I post here. :)
