Gestion de l'héritage avec neutralisation efficace

Question

J'ai les deux structures de données.

D'abord, une liste de propriétés appliquées à l'objet triples:

Object1  Object2  Object3 Property  Value
     O1       O2       O3       P1  "abc"
     O1       O2       O3       P2  "xyz"
     O1       O3       O4       P1  "123"
     O2       O4       O5       P1  "098"

Deuxièmement, un arbre d'héritage:

O1
    O2
        O4
    O3
        O5

Ou est perçu comme une relation:

Object    Parent
    O2        O1
    O4        O2
    O3        O1
    O5        O3
    O1      null

La sémantique de cet être que O2 hérite des propriétés de O1; O4 - de O2 et O1; O3 - de O1; et O5 - de O3 et O1, dans l'ordre de préséance.
NOTE 1: j'ai un moyen efficace pour sélectionner tous les enfants ou tous les parents d'un objet donné. C'est actuellement mis en œuvre avec la gauche et la droite, index, mais hierarchyid pourrait également fonctionner. Cela ne semble pas important maintenant.
NOTE 2: j'ai tiggers en place assurez-vous que "l'Objet" de la colonne contient toujours tous les objets possibles, même quand ils n'ont pas vraiment d'être là (c'est à dire n'ont pas de parent ou enfants). Cela rend possible l'utilisation d' inner joins plutôt que sévèrement moins effiecient outer joins.

L'objectif est: étant Donné une paire de (Propriété, Valeur), le retour de tous les objets triples qui ont cette propriété avec la valeur soit définie de manière explicite ou héritées d'un parent.

NOTE 1: Un objet triple (X,Y,Z) est considéré comme un "parent" de triple (A,B,C) quand il est vrai que soit X = A ou X is a parent of A, et la même chose est vraie pour (Y,B) et (Z,C).
NOTE 2: UNE propriété définie sur un plus proche parent "remplace" la même propriété définie sur un plus lointain parent.
REMARQUE 3: Lorsque (A,B,C) a deux parents, (X1,Y1,Z1) et (X2,Y2,Z2), alors (X1,Y1,Z1) est considéré comme un "plus proche parent" lorsque:
(a) X2 est un parent de X1, ou
(b) X2 = X1 et Y2 est un parent de Y1, ou
(c) X2 = X1 et Y2 = Y1 et Z2 est un parent de Z1

En d'autres termes, la "proximité" dans l'ascendance pour les triplets est définie sur la base des premiers éléments de triples tout d'abord, puis sur la deuxième composants, puis sur la troisième composante. Cette règle établit une unambigous commande partielle de triples dans les termes de l'ascendance.

Par exemple, pour la paire de (P1, "abc"), l'ensemble de résultats de triples seront:

 O1, O2, O3     -- Defined explicitly
 O1, O2, O5     -- Because O5 inherits from O3
 O1, O4, O3     -- Because O4 inherits from O2
 O1, O4, O5     -- Because O4 inherits from O2 and O5 inherits from O3
 O2, O2, O3     -- Because O2 inherits from O1
 O2, O2, O5     -- Because O2 inherits from O1 and O5 inherits from O3
 O2, O4, O3     -- Because O2 inherits from O1 and O4 inherits from O2
 O3, O2, O3     -- Because O3 inherits from O1
 O3, O2, O5     -- Because O3 inherits from O1 and O5 inherits from O3
 O3, O4, O3     -- Because O3 inherits from O1 and O4 inherits from O2
 O3, O4, O5     -- Because O3 inherits from O1 and O4 inherits from O2 and O5 inherits from O3
 O4, O2, O3     -- Because O4 inherits from O1
 O4, O2, O5     -- Because O4 inherits from O1 and O5 inherits from O3
 O4, O4, O3     -- Because O4 inherits from O1 and O4 inherits from O2
 O5, O2, O3     -- Because O5 inherits from O1
 O5, O2, O5     -- Because O5 inherits from O1 and O5 inherits from O3
 O5, O4, O3     -- Because O5 inherits from O1 and O4 inherits from O2
 O5, O4, O5     -- Because O5 inherits from O1 and O4 inherits from O2 and O5 inherits from O3

Notez que le triple (O2, O4, O5) est absent de cette liste. C'est parce que la propriété P1 est définie de manière explicite pour le triple (O2, O4, O5) et cela empêche que le triple d'hériter de la propriété de (O1, O2, O3). Notez également que le triple (O4, O4, O5) est également absent. C'est parce que triple hérite de sa valeur de P1="098" de l' (O2, O4, O5), parce que c'est un proche parent de (O1, O2, O3).

La façon simple de le faire est la suivante. Tout d'abord, pour chaque triplet qu'une propriété est définie sur, sélectionnez tous les possibles enfant triples:

select Children1.Id as O1, Children2.Id as O2, Children3.Id as O3, tp.Property, tp.Value
from TriplesAndProperties tp

-- Select corresponding objects of the triple
inner join Objects as Objects1 on Objects1.Id = tp.O1
inner join Objects as Objects2 on Objects2.Id = tp.O2
inner join Objects as Objects3 on Objects3.Id = tp.O3

-- Then add all possible children of all those objects
inner join Objects as Children1 on Objects1.Id [isparentof] Children1.Id
inner join Objects as Children2 on Objects2.Id [isparentof] Children2.Id
inner join Objects as Children3 on Objects3.Id [isparentof] Children3.Id

Mais ce n'est pas toute l'histoire: si certaines triple hérite de la même propriété de plusieurs parents, cette requête donnera des résultats contradictoires. Donc, la deuxième étape consiste à sélectionner l'une de ces résultats contradictoires:

select * from
(
    select 
        Children1.Id as O1, Children2.Id as O2, Children3.Id as O3, tp.Property, tp.Value,
        row_number() over( 
            partition by Children1.Id, Children2.Id, Children3.Id, tp.Property
            order by Objects1.[depthInTheTree] descending, Objects2.[depthInTheTree] descending, Objects3.[depthInTheTree] descending
        )
        as InheritancePriority
    from
    ... (see above)
)
where InheritancePriority = 1

La fenêtre de la fonction row_number() over( ... ) est le suivant: pour chaque combinaison unique d'objets triple et de la propriété, il trie toutes les valeurs ancestrales de la distance à partir de la triple pour les parents que la valeur est héritée de, et puis je ne sélectionnez la première de la liste de valeurs. Un effet similaire peut être obtenu avec un GROUP BY et ORDER BY des déclarations, mais je viens de trouver la fonction de fenêtre sémantiquement plus propres (les plans d'exécution de leurs résultats soient identiques). Le point est, j'ai besoin de choisir le plus proche de contribuer ancêtres, et pour cela j'ai besoin de les grouper et de les trier au sein du groupe.

Et enfin, maintenant, je peux simplement filtrer le résultat d'une Propriété et de la Valeur.

Ce schéma fonctionne. Très fiable et prévisible. Il s'est avéré être très puissant pour la gestion des tâches qu'il implémente.

Le seul problème est, il est awfuly lent.
On pourrait dire à la jointure de sept tables peut-être ralentir les choses, mais n'est en réalité pas le goulot d'étranglement.

Selon le plan d'exécution j'obtiens à partir de SQL Management Studio (ainsi que le générateur de profils SQL), le goulot d'étranglement est le tri. Le problème est, afin de satisfaire ma fenêtre de fonction, le serveur a de trier par Children1.Id, Children2.Id, Children3.Id, tp.Property, Parents1.[depthInTheTree] descending, Parents2.[depthInTheTree] descending, Parents3.[depthInTheTree] descending, et il n'existe pas d'indices qu'il peut utiliser, car les valeurs proviennent d'une jointure croisée de plusieurs tables.

EDIT: Par Michael Buen suggestion (merci Michel), j'ai posté le puzzle entier à sqlfiddle ici. On peut voir dans le plan de l'exécution de l'opération de Tri représentent 32% de la totalité de la requête, et qui va croître avec le nombre total de lignes, car toutes les autres opérations d'utiliser les index.

Habituellement dans de tels cas, je voudrais utiliser une vue indexée, mais pas dans ce cas, parce que les vues indexées ne peut pas contenir de l'auto-jointures, dont il y en a six.

La seule façon que je peux penser à ce jour est de créer des six exemplaires de la table d'Objets et de les utiliser pour la rejoint, permettant ainsi une vue indexée.
N'a que le temps est venu que je doit en être réduit à ce genre de hacks? Le désespoir s'installe.

Answer 1

J'ai 3 réponses possibles.

Le sql de violon pour votre question est ici: http://sqlfiddle.com/#!3/7c7a0/3/0

Le sql de violon pour ma réponse est ici: http://sqlfiddle.com/#!3/5d257/1

Avertissements:

1. L'Analyseur de requêtes n'est pas assez - je remarquer un certain nombre de réponses ont été rejetés en raison de leurs plans de requête sont plus chers que la requête d'origine. L'Analyseur est le seul guide. Selon l'ensemble de données, de matériel et de cas d'utilisation, de plus en plus de requêtes cher peut retourner des résultats plus rapides, moins chers. Vous avez à tester dans votre environnement.
2. L'Analyseur de requêtes est inefficace - même si vous trouvez un moyen de supprimer les "plus cher de l'étape" à partir d'une requête, souvent, elle ne fait aucune différence pour votre requête.
3. Requête seuls changements rarement atténuer schéma/dessin de questions - réponses à Certaines questions ont été rejetées parce qu'elles impliquent de schéma niveau des changements tels que les déclencheurs et les tables supplémentaires. Des requêtes complexes qui résistent à l'optimisation sont un signe fort que le problème est avec la conception sous-jacente ou à mes attentes. Si vous n'aimez pas, mais vous pourriez avoir à accepter que le problème n'est pas résoluble au niveau de la requête.
4. La vue indexée ne peut pas contenir de row_number()/partitition clause - Travail autour de l'auto-jointure problème par la création de six copies d'objets de la table n'est pas assez pour vous permettre de créer de la vue indexée comme vous l'avez suggéré. Je l'ai essayé dans cette sqlfiddle. Si vous supprimez la dernière instruction "create index", vous obtiendrez une erreur parce que votre point de vue "contient un classement ou d'une agrégation de la fenêtre de fonction".

De Travail Les Réponses:

1. Left Join au lieu de la fonction row_number() - Vous pouvez utiliser une requête qui utilise la gauche se joint à exclure les résultats qui ont été redéfinies plus bas dans l'arbre. Retrait de la enfin "order by" à partir de cette requête supprime réellement le genre qui a la plaie de vous! Le plan d'exécution de cette requête est toujours plus cher que l'original, mais voir Avertissement n ° 1 ci-dessus.
2. La vue indexée partie de la requête - en Utilisant un certain sérieux de la requête de la magie (basé sur cette technique), j'ai créé une vue indexée pour la partie de la requête. Ce point de vue peut être utilisé pour améliorer à la question d'origine de la requête ou de la réponse #1.
3. Actualiser dans un bien indexé table - Quelqu'un d'autre a suggéré cette réponse, mais ils ne peuvent pas expliqué, c'est bien. À moins que votre résultat est de très grande taille ou vous faites très fréquemment à jour les tables de source, la réalisation de résultats d'une requête et l'aide d'un déclencheur pour les garder à jour est un parfait moyen de contourner ce genre de problème. Une fois que vous créez une vue de votre requête, il est assez facile de tester cette option. Vous pouvez réutiliser réponse #2 pour accélérer le déclencheur, puis de l'améliorer au fil du temps. (Vous parlez de la création de six copies de vos tables, essayez dans un premier temps. Il garantit que la performance de la sélection que vous aimez, c'est aussi bon que possible.)

Ici, c'est le schéma d'une partie de ma réponse à partir de sqlfiddle:

Create Table Objects
(
    Id int not null identity primary key,
    LeftIndex int not null default 0,
    RightIndex int not null default 0
)

alter table Objects add ParentId int null references Objects

CREATE TABLE TP
(
    Object1 int not null references Objects,
    Object2 int not null references Objects,
    Object3 int not null references Objects,
    Property varchar(20) not null,
    Value varchar(50) not null
)


insert into Objects(LeftIndex, RightIndex) values(1, 10)
insert into Objects(ParentId, LeftIndex, RightIndex) values(1, 2, 5)
insert into Objects(ParentId, LeftIndex, RightIndex) values(1, 6, 9)
insert into Objects(ParentId, LeftIndex, RightIndex) values(2, 3, 4)
insert into Objects(ParentId, LeftIndex, RightIndex) values(3, 7, 8)

insert into TP(Object1, Object2, Object3, Property, Value) values(1,2,3, 'P1', 'abc')
insert into TP(Object1, Object2, Object3, Property, Value) values(1,2,3, 'P2', 'xyz')
insert into TP(Object1, Object2, Object3, Property, Value) values(1,3,4, 'P1', '123')
insert into TP(Object1, Object2, Object3, Property, Value) values(2,4,5, 'P1', '098')

create index ix_LeftIndex on Objects(LeftIndex)
create index ix_RightIndex on Objects(RightIndex)
create index ix_Objects on TP(Property, Value, Object1, Object2, Object3)
create index ix_Prop on TP(Property)
GO

---------- QUESTION ADDITIONAL SCHEMA --------
CREATE VIEW TPResultView AS
Select O1, O2, O3, Property, Value
FROM
(
    select Children1.Id as O1, Children2.Id as O2, Children3.Id as O3, tp.Property, tp.Value,

    row_number() over( 
        partition by Children1.Id, Children2.Id, Children3.Id, tp.Property
        order by Objects1.LeftIndex desc, Objects2.LeftIndex desc, Objects3.LeftIndex desc
    )
    as Idx

    from tp

    -- Select corresponding objects of the triple
    inner join Objects as Objects1 on Objects1.Id = tp.Object1
    inner join Objects as Objects2 on Objects2.Id = tp.Object2
    inner join Objects as Objects3 on Objects3.Id = tp.Object3

    -- Then add all possible children of all those objects
    inner join Objects as Children1 on Children1.LeftIndex between Objects1.LeftIndex and Objects1.RightIndex
    inner join Objects as Children2 on Children2.LeftIndex between Objects2.LeftIndex and Objects2.RightIndex
    inner join Objects as Children3 on Children3.LeftIndex between Objects3.LeftIndex and Objects3.RightIndex
) as x
WHERE idx = 1 
GO

---------- ANSWER 1 SCHEMA --------

CREATE VIEW TPIntermediate AS
select tp.Property, tp.Value 
    , Children1.Id as O1, Children2.Id as O2, Children3.Id as O3
    , Objects1.LeftIndex as PL1, Objects2.LeftIndex as PL2, Objects3.LeftIndex as PL3    
    , Children1.LeftIndex as CL1, Children2.LeftIndex as CL2, Children3.LeftIndex as CL3    
    from tp

    -- Select corresponding objects of the triple
    inner join Objects as Objects1 on Objects1.Id = tp.Object1
    inner join Objects as Objects2 on Objects2.Id = tp.Object2
    inner join Objects as Objects3 on Objects3.Id = tp.Object3

    -- Then add all possible children of all those objects
    inner join Objects as Children1 WITH (INDEX(ix_LeftIndex)) on Children1.LeftIndex between Objects1.LeftIndex and Objects1.RightIndex
    inner join Objects as Children2 WITH (INDEX(ix_LeftIndex)) on Children2.LeftIndex between Objects2.LeftIndex and Objects2.RightIndex
    inner join Objects as Children3 WITH (INDEX(ix_LeftIndex)) on Children3.LeftIndex between Objects3.LeftIndex and Objects3.RightIndex
GO

---------- ANSWER 2 SCHEMA --------

-- Partial calculation using an indexed view
-- Circumvented the self-join limitation using a black magic technique, based on 
-- http://jmkehayias.blogspot.com/2008/12/creating-indexed-view-with-self-join.html
CREATE TABLE dbo.multiplier (i INT PRIMARY KEY)

INSERT INTO dbo.multiplier VALUES (1) 
INSERT INTO dbo.multiplier VALUES (2) 
INSERT INTO dbo.multiplier VALUES (3) 
GO

CREATE VIEW TPIndexed
WITH SCHEMABINDING
AS

SELECT tp.Object1, tp.object2, tp.object3, tp.property, tp.value,
    SUM(ISNULL(CASE M.i WHEN 1 THEN Objects.LeftIndex ELSE NULL END, 0)) as PL1,
    SUM(ISNULL(CASE M.i WHEN 2 THEN Objects.LeftIndex ELSE NULL END, 0)) as PL2,
    SUM(ISNULL(CASE M.i WHEN 3 THEN Objects.LeftIndex ELSE NULL END, 0)) as PL3,
    SUM(ISNULL(CASE M.i WHEN 1 THEN Objects.RightIndex ELSE NULL END, 0)) as PR1,
    SUM(ISNULL(CASE M.i WHEN 2 THEN Objects.RightIndex ELSE NULL END, 0)) as PR2,
    SUM(ISNULL(CASE M.i WHEN 3 THEN Objects.RightIndex ELSE NULL END, 0)) as PR3,
    COUNT_BIG(*) as ID
    FROM dbo.tp
    cross join dbo.multiplier M 
    inner join dbo.Objects 
    on (M.i = 1 AND Objects.Id = tp.Object1)
    or (M.i = 2 AND Objects.Id = tp.Object2)
    or (M.i = 3 AND Objects.Id = tp.Object3)
GROUP BY tp.Object1, tp.object2, tp.object3, tp.property, tp.value
GO

-- This index is mostly useless but required
create UNIQUE CLUSTERED index pk_TPIndexed on dbo.TPIndexed(property, value, object1, object2, object3)
-- Once we have the clustered index, we can create a nonclustered that actually addresses our needs
create NONCLUSTERED index ix_TPIndexed on dbo.TPIndexed(property, value, PL1, PL2, PL3, PR1, PR2, PR3)
GO

-- NOTE: this View is not indexed, but is uses the indexed view 
CREATE VIEW TPIndexedResultView AS
Select O1, O2, O3, Property, Value
FROM
(
    select Children1.Id as O1, Children2.Id as O2, Children3.Id as O3, tp.Property, tp.Value,

    row_number() over( 
        partition by tp.Property, Children1.Id, Children2.Id, Children3.Id
        order by tp.Property, Tp.PL1 desc, Tp.PL2 desc, Tp.PL3 desc
    )
    as Idx

    from TPIndexed as TP WITH (NOEXPAND)

    -- Then add all possible children of all those objects
    inner join Objects as Children1 WITH (INDEX(ix_LeftIndex)) on Children1.LeftIndex between TP.PL1 and TP.PR1
    inner join Objects as Children2 WITH (INDEX(ix_LeftIndex)) on Children2.LeftIndex between TP.PL2 and TP.PR2
    inner join Objects as Children3 WITH (INDEX(ix_LeftIndex)) on Children3.LeftIndex between TP.PL3 and TP.PR3
) as x
WHERE idx = 1 
GO


-- NOTE: this View is not indexed, but is uses the indexed view 
CREATE VIEW TPIndexedIntermediate AS
select tp.Property, tp.Value 
    , Children1.Id as O1, Children2.Id as O2, Children3.Id as O3
    , PL1, PL2, PL3    
    , Children1.LeftIndex as CL1, Children2.LeftIndex as CL2, Children3.LeftIndex as CL3    
    from TPIndexed as TP WITH (NOEXPAND)

    -- Then add all possible children of all those objects
    inner join Objects as Children1 WITH (INDEX(ix_LeftIndex)) on Children1.LeftIndex between TP.PL1 and TP.PR1
    inner join Objects as Children2 WITH (INDEX(ix_LeftIndex)) on Children2.LeftIndex between TP.PL2 and TP.PR2
    inner join Objects as Children3 WITH (INDEX(ix_LeftIndex)) on Children3.LeftIndex between TP.PL3 and TP.PR3  
GO


---------- ANSWER 3 SCHEMA --------
-- You're talking about making six copies of the TP table
-- If you're going to go that far, you might as well, go the trigger route
-- The performance profile is much the same - slower on insert, faster on read
-- And instead of still recalculating on every read, you'll be recalculating
-- only when the data changes. 

CREATE TABLE TPResult
(
    Object1 int not null references Objects,
    Object2 int not null references Objects,
    Object3 int not null references Objects,
    Property varchar(20) not null,
    Value varchar(50) not null
)
GO

create UNIQUE index ix_Result on TPResult(Property, Value, Object1, Object2, Object3)


--You'll have to imagine this trigger, sql fiddle doesn't want to do it
--CREATE TRIGGER tr_TP
--ON TP
--  FOR INSERT, UPDATE, DELETE
--AS
--  DELETE FROM TPResult
-- -- For this example we'll just insert into the table once
INSERT INTO TPResult 
SELECT O1, O2, O3, Property, Value 
FROM TPResultView

Requête d'une partie de ma réponse à partir de sqlfiddle:

-------- QUESTION QUERY ----------
-- Original query, modified to use the view I added
SELECT O1, O2, O3, Property, Value 
FROM TPResultView
WHERE property = 'P1' AND value = 'abc'
-- Your assertion is that this order by is the most expensive part. 
-- Sometimes converting queries into views allows the server to
-- Optimize them better over time.
-- NOTE: removing this order by has no effect on this query.
-- ORDER BY O1, O2, O3
GO

-------- ANSWER 1  QUERY ----------
-- A different way to get the same result. 
-- Query optimizer says this is more expensive, but I've seen cases where
-- it says a query is more expensive but it returns results faster.
SELECT O1, O2, O3, Property, Value
FROM (
  SELECT A.O1, A.O2, A.O3, A.Property, A.Value
  FROM TPIntermediate A
  LEFT JOIN TPIntermediate B ON A.O1 = B.O1
    AND A.O2 = B.O2
    AND A.O3 = B.O3
    AND A.Property = B.Property
    AND 
    (
      -- Find any rows with Parent LeftIndex triplet that is greater than this one
      (A.PL1 < B.PL1
      AND A.PL2 < B.PL2
      AND A.PL3 < B.PL3) 
    OR
      -- Find any rows with LeftIndex triplet that is greater than this one
      (A.CL1 < B.CL1
      AND A.CL2 < B.CL2
      AND A.CL3 < B.CL3)
    )
  -- If this row has any rows that match the previous two cases, exclude it
  WHERE B.O1 IS NULL ) AS x
WHERE property = 'P1' AND value = 'abc'
-- NOTE: Removing this order _DOES_ reduce query cost removing the "sort" action
-- that has been the focus of your question.   
-- Howeer, it wasn't clear from your question whether this order by was required.
--ORDER BY O1, O2, O3
GO

-------- ANSWER 2  QUERIES ----------
-- Same as above but using an indexed view to partially calculate results

SELECT O1, O2, O3, Property, Value 
FROM TPIndexedResultView
WHERE property = 'P1' AND value = 'abc'
-- Your assertion is that this order by is the most expensive part. 
-- Sometimes converting queries into views allows the server to
-- Optimize them better over time.
-- NOTE: removing this order by has no effect on this query.
--ORDER BY O1, O2, O3
GO

SELECT O1, O2, O3, Property, Value
FROM (
  SELECT A.O1, A.O2, A.O3, A.Property, A.Value
  FROM TPIndexedIntermediate A
  LEFT JOIN TPIndexedIntermediate B ON A.O1 = B.O1
    AND A.O2 = B.O2
    AND A.O3 = B.O3
    AND A.Property = B.Property
    AND 
    (
      -- Find any rows with Parent LeftIndex triplet that is greater than this one
      (A.PL1 < B.PL1
      AND A.PL2 < B.PL2
      AND A.PL3 < B.PL3) 
    OR
      -- Find any rows with LeftIndex triplet that is greater than this one
      (A.CL1 < B.CL1
      AND A.CL2 < B.CL2
      AND A.CL3 < B.CL3)
    )
  -- If this row has any rows that match the previous two cases, exclude it
  WHERE B.O1 IS NULL ) AS x
WHERE property = 'P1' AND value = 'abc'
-- NOTE: Removing this order _DOES_ reduce query cost removing the "sort" action
-- that has been the focus of your question.   
-- Howeer, it wasn't clear from your question whether this order by was required.
--ORDER BY O1, O2, O3
GO



-------- ANSWER 3  QUERY ----------
-- Returning results from a pre-calculated table is fast and easy
-- Unless your are doing many more inserts than reads, or your result
-- set is very large, this is a fine way to compensate for a poor design
-- in one area of your database.
SELECT Object1 as O1, Object2 as O2, Object3 as O3, Property, Value 
FROM TPResult
WHERE property = 'P1' AND value = 'abc'
ORDER BY O1, O2, O3

Answer 2

Vous pouvez accélérer le processus en matérialisant la jointure dans une table indexée, par exemple joinresult. Cela a pour inconvénient de nécessiter de l’espace et de l’enregistrer sur le disque. Mais il a l'avantage de pouvoir utiliser un index pour la partie lente.

 insert into joinedresult
select Children1.Id as O1, Children2.Id as O2, Children3.Id as O3, tp.Property, tp.Value,Objects1.[depthInTheTree] as O1D,Objects2.[depthInTheTree] as O2D,Objects3. depthInTheTree]  as O3D from  ... (see above)
 

Assurez-vous que joinresult a un index sur [O1, O2, O3, Propriété, O1D, O2D, O3D] et effacez-le avant d'exécuter. ensuite

 select * from
(
    select 
    Children1.Id as O1, Children2.Id as O2, Children3.Id as O3, tp.Property, tp.Value,
    row_number() over( 
        partition by Children1.Id, Children2.Id, Children3.Id, tp.Property
        order by O1D descending, O2D descending, O3D descending
    )
    as InheritancePriority
    from joinedresult
)
where InheritancePriority = 1
 

Answer 3

Avez-vous essayé un index (ou défini le pk) avec la colonne "Valeur" en premier, la colonne "Propriété" en second, la colonne "Object1" en troisième, la colonne "Object2" en quatrième et la colonne "Object3" en cinquième? Je suppose que "valeur" est plus restrictif que "propriété".

Je suppose également que la colonne Id est définie en tant que clé primaire et qu'il existe une relation de clé étrangère entre ParentId et Id.

Comment cette requête fonctionne-t-elle ?:

     with 
    -- First, get all combinations that match the property/value pair.
    validTrip as (
        select Object1, Object2, Object3
        from TriplesAndProperties 
        where value = @value
            and property = @property
    ),
    -- Recursively flatten the inheritance hierarchy of Object1, 2 and 3.
    o1 as (
        select Id, 0 as InherLevel from Objects where Id in (select Object1 from validTrip)
        union all
        select rec.Id, InherLevel + 1 from Objects rec inner join o1 base on rec.Parent = base.[Object]
    ),
    o2 as (
        select Id, 0 as InherLevel from Objects where Id in (select Object2 from validTrip)
        union all
        select rec.Id, InherLevel + 1 from Objects rec inner join o2 base on rec.Parent = base.[Object]
    ),
    o3 as (
        select Id, 0 as InherLevel from Objects where Id in (select Object3 from validTrip)
        union all
        select rec.Id, InherLevel + 1 from Objects rec inner join o3 base on rec.Parent = base.[Object]
   )
    -- select the Id triple.
    select o1.Id, o2.Id, o3.Id N
    -- match every option in o1, with every option in o2, with every option in o3.
    from o1
        cross join o2
        cross join o3
    -- order by the inheritance level.
    order by o1.InherLevel, o2.InherLevel, o3.InherLevel;
 

Answer 4

Les requêtes hiérarchiques , à savoir WITH RECURSIVE ... ou des équivalents propriétaires tels que CONNECT BY sont vos amis dans ce cas.

La recette pour résoudre votre problème est la suivante: Commencez par le congé et allez à la racine en agrégeant et en excluant tout ce qui a déjà été trouvé.

Answer 5

Je suppose que votre table est assez grande. D'où la lenteur. Dans ce cas, je devine aussi que vous avez plusieurs propriétés (2 à plusieurs). Dans ce cas, je vous suggérerais de déplacer le "où propriété = 'P1'" à l'intérieur du CTE. Cela filtrerait une bonne partie des données, rendant votre requête aussi rapidement que le nombre de propriétés.

Quelque chose comme: http://sqlfiddle.com/#!3/7c7a0/92/0