how to improve performance in this query?

Hey guys,

Here's my situation:

I have a table called lets say 'Tree', as illustred bellow:

Tree
====
TreeId (integer)(identity) not null
L1(integer)
L2(integer)
L3(integer)
...
L10(integer)

The combination of the values of L1 thru L10 is called a "Path" , and
L1 thru L10 values are stored in a second table lets say called
'Leaf':

Leaf
====
LeafId (integer)(identity) not null
LeatText varchar(2000)

Here's my problem:

I need to lookup for a given keyword in each path of the tree table,
and return each individual column for the paths that match the
criteria. Here's the main idea of how I have this now.

SELECT TreeId,L1,L2,...,L10, GetText(L1) + GetText(L2) as L2text + ...
+ GetText(L10) AS PathText
INTO #tmp FROM Tree //GetText is a lookup function for the Leaf table

SELECT L1,GetText(L1),L2,GetText(L2),...,L10,GetText(L10) FROM #tmp
WHERE
CharIndex(@.keyword,a.pathtext) > 0

Does anyone would know a better,smart, more efficient way to
accomplish this task? :)

Thks,On 1 Nov 2004 08:23:44 -0800, Silvio wrote:

>Does anyone would know a better,smart, more efficient way to
>accomplish this task? :)

Hi Silvio.

Yep. Improve your table design. You should normalize down to at least
third normal form. That emans that the repeating group (L1 ... L10) in the
Leaf table has to go in it's own table:

CREATE TABLE Paths
(TreeID int NOT NULL REFERENCES Trees,
PathNo int NOT NULL CHECK (PathNo BETWEEN 1 AND 10),
LeafID int NOT NULL REFERENCES Leaves,
PRIMARY KEY (TreeID, PathNo)
)

If the same leaf can't occur twice in a tree, you could also add a
UNIQUE(TreeID, LeafID) constraint, or make that the primary key. If the
order of the leaves in a tree is unimportant, you can leave out the PathNo
column.

>I need to lookup for a given keyword in each path of the tree table,
>and return each individual column for the paths that match the
>criteria.

Probably something like this:

SELECT P.PathNo, L.LeafText
FROM Paths AS P
INNER JOIN Leaves AS L
ON L.LeafNo = P.LeafNo
WHERE EXISTS (SELECT *
FROM Paths AS P2
INNER JOIN Leaves AS L2
ON L2.LeafNo = P2.LeafNo
WHERE P2.TreeID = P.TreeID
AND CHARINDEX(@.keyword, L2.LeafText) > 0)
(untested)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)