Aşağıdaki iki veri yapıları var.

İlközellikler listesi nesnesi üç katına uygulanır:

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"

İkincimiras , bir ağaç:

O1
    O2
        O4
    O3
        O5

Ya da bir ilişki olarak görülmesi:

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

Bu O2 olmanın mantığı O1; ve öncelik O3 O5 gelen ve sırasıyla O1, O2 O1; O4 - özellikleri ve O1; O3 - devralır.
NOT 1: Belirli bir nesnenin tüm çocuklar ya da tüm ebeveynler seçmek için etkili bir yol var. Bu, şu anda sol ve sağ dizin ile uygulanır, ama hierarchyid da işe yarayabilir. Bu şu anda önemli görünmüyor.
NOT 2: Var kaplanlar içinde yer olduğundan emin olun "Nesne" sütun her zaman içeren olası tüm nesneler, hatta ne zaman onlar gerçekten orda olmak zorunda (yani hiçbir ebeveyn ya da çocuk tanımlı). Bu da ciddi bir şekilde daha az bant genişliği yerine inner joins kullanmak için yapar outer joinler.

Amaç: Bir çift (Özellik, Değer) dikkate alındığında, dönüş o değeri bu özelliği olan üçlü ya da açıkça tanımlanmış bir ebeveynden gelen nesne.

NOT 1: Nesne üçlü (X,Y,Z) kabul edilen bir "üst" üçlü (A,B,C) ne zaman doğru ya da X = A X is a parent of A doğru (Y,B) (Z,C).
NOT 2Bir özelliği daha yakın bir üst üzerinde tanımlanan" aynı özellik daha uzak bir üst tanımlanmış. "geçersiz kılar
NOT 3: (A,B,C) iki veli (X1,Y1,Z1) ve (X2,Y2,Z2), (X1,Y1,Z1) olarak kabul edilir bir "yakın" üst:
(a) X2 X1, ya da bir ebeveyn
(b) X2 = X1 ve Y2 Y1, ya da bir ebeveyn
(c) X2 = X1 ve Y2 = Y1 ve Z1, Z2 bir ebeveyn

Diğer bir deyişle," soy üçe, sonra üçüncü bileşenleri ikinci bileşenleri o zaman.bu üçlü ilk bileşenleri tanımlanmıştır "yakınlığı Bu kural soy açısından üç katına unambigous kısmi bir düzen kurar.

Çifti verilen örnek için, (P1, olacaktır: "abc"),

 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

Üçlü () O2, O4, O5, bu listeden eksik olduğunu unutmayın. Bu özellik P1 açıkça üçlü () O2, O4, O5 için tanımlanan ve bu üçlü () O1, O2, O3 bu özelliğini devralmasını önler çünkü. Ayrıca üçlü () O4, O4, O5 da yok olduğunu unutmayın. Bu üçlü P1="" daha yakın, daha üst () O1, O2, O3 çünkü. (O2, O4, O5), 098 onun değerini alır çünkü.

Bunu yapmak için basit yolu şudur. İlk olarak, bir özellik tanımlanan her üçlü için mümkün olan tüm çocuk üç katına seçin:

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

Eğer bazı üçlü birkaç aileyi aynı özellik devralan bu sorgu çelişkili sonuçlar verecektir.ama hikayenin tamamı bu değil: Bu nedenle, ikinci adım bu sonuçlar çelişkili bir seçmek için:

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

Pencere fonksiyonu row_number() over( ... ) yapar: her benzersiz bir kombinasyonu nesneleri üç ve özelliği, bu sıralar tüm değerleri ile ata mesafeden üç ebeveynler değerdir miras, ve sonra ben sadece seçilmiş ilk sonuç listesi değerleri. Benzer bir etki GROUP BY ORDER BY ifadeleri ile elde edilebilir, ama sadece pencere işlevi, anlamsal temizleyici (onlar verim planları aynıdır yürütme) bulabilirsiniz. Nokta, katkıda atalarının en yakın seçmek için ihtiyacım var, ve o grup ve o grubun içinde sıralamak istiyorum.

Ve son olarak, şimdi sadece sonucu Özellik Değeri tarafından ayarlanan filtre yapabilirim.

Bu program çalışıyor. Çok güvenilir ve öngörülebilir. Çok güçlü olmak danışmanlığı görevi için kanıtlanmıştır.

Tek sorun şuawfuly yavaş.
Yedi tabloları birleştirme işleri yavaşlatıyor olabilir işaret olabilir, ama aslında bu darboğaz değildir.

SQL Management Studio dan alıyorum gerçek yürütme planı (SQL Profiler) göre, darboğaz sıralama. Sorun, tatmin penceremin işlevi, sunucu olduğuna göre sırala Children1.Id, Children2.Id, Children3.Id, tp.Property, Parents1.[depthInTheTree] descending, Parents2.[depthInTheTree] descending, Parents3.[depthInTheTree] descending ve olmaz dizinleri kullanabilirsiniz, çünkü bu değerler bir çapraz birleştirme birkaç tablo.

DÜZENLEME:Michael Buen önerisine göre (teşekkürler Michael), here sqlfiddle için tüm bulmaca attılar. Bir bakın yürütme planı bu Sıralama işlemi, hesaplar için 2 bütün sorgu ve büyüyecek olan sayı toplam satır, çünkü tüm diğer işlemleri dizinler kullanın.

Genellikle bu gibi durumlarda dizinli görünüm kullanmak istiyorsunuz, ama bu durumda, dizin oluşturulmuş görünümler altı var edemiyor kendini birleşimler içerir, çünkü değil.

Aklıma gelen tek yol şu ana kadar Nesneleri masanın altı kopyalarını oluşturmak ve katılır, dizinli görünüm sağlayan, böylece onları kullanın.
Zaman kesmek böyle bir azalma olacak bu nereden çıktı şimdi? Umutsuzluk başlar.

3 Olası cevaplar var.

Sorunuza burada sql keman: http://sqlfiddle.com/#!3/7c7a0/3/0

Cevabımı burada sql keman: http://sqlfiddle.com/#!3/5d257/1

Uyarı:

1. Sorgu Çözümleyicisi yeterli değil- Cevaplar sorgu planlarını özgün sorgu daha pahalı olduğu için reddedildi fark ettim. Analiz rehberi. Veri kümesi, donanım ve kullanma durumu gerçek bağlı olarak, daha pahalı sorgular sonuçları daha hızlı, daha az pahalı olanlar daha dönebilirsiniz. Test ortamında.
2. Sorgu Çözümleyicisi etkisizdirEğer "en pahalı adım" bir sorgu, genellikle sorgunuz için hiç fark etmez. bu kaldırmak için bir yol bulursanız bile
3. Sorgu değişiklikler nadiren tek başına/tasarım sorunları hafifletmek şema- Bazı cevaplar tetikler ve ek tablolar gibi şema düzeyinde değişiklikler dahil çünkü reddedildi. Optimizasyon karşı karmaşık bir sorgu bu sorun benim beklentileri ile temel tasarım ile veya güçlü bir işaret. Hoşunuza gitmeyebilir, ama bu sorun, sorgu düzeyinde çözülebilir olmadığını kabul etmek zorunda kalabilirsiniz.
4. () Dizinli görünüm içeremez row_number tümcesi ./partitition - Nesneleri masa altı kopyalarını oluşturarak iç birleşim, geçici bir sorun önerdiğin var dizinli görünüm oluşturmak için izin vermek için yeterli değil. this sqlfiddle bunu denedim. Eğer bu son "dizin" deyimi, "sıralama veya toplu penceresi işlev içerir." çünkü bir hata alırsınız oluşturmanız Yorumsuz eğer

Çalışma Cevapları:

1. Row_number yerine Katılmak Left()- Sol ağacında geçersiz alt edilmiş sonuçlar çıkarmak için birleştirme kullanan bir sorgu kullanabilirsiniz. Çıkarma sonunda "" bu sorgu, aslında seni rahatsız eden bu tür kaldırır! ' dan sipariş Bu sorgu için yürütme planı hala orijinalden daha pahalı, ama #1 Yukarıda Yasal Uyarı bakın.
2. Sorgu bir parçası için dizinli görünüm- Ciddi bir sorgu büyü (this technique bağlı olarak) kullanarak, sorgu bir parçası için oluşturulmuş görünüm yarattım. Bu görünüm, orijinal soru sorgu geliştirmek veya #1 cevaplamak için kullanılabilir.
3. İyi oluşturulmuş bir tabloya gerçekleştirmek- Bunu başka biri cevap önerdi, ama iyi açıkladı olmayabilir. Tabii eğer sonuç kümesi çok büyük ya da yapıyorsun çok sık güncellemeleri için kaynak tablolar, gerçekleşen sonuçları ve sorgu kullanarak bir tetikleyici onları muhafaza etmek güncel de gayet iyi bir şekilde bu sorunu çözmek için biraz sorun. Sorgunuz için bir görünüm oluşturduktan sonra, yeterince kolay bu seçenek test edilebilir. Yeniden #2 tetik hızlandırmak ve daha da geliştirmek, daha sonra zaman içinde cevap verebilir. (Oluşturma hakkında konuşuyorsunaltısenin tabloların kopyalarını, bu ilk deneyin. Değer verdiğin seçmek için performansı mümkün olduğunca iyi olacağı garanti.)

Burada sqlfiddle benim cevap şema bölümü var

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

Sqlfiddle benim cevap kısmı sorgu:

-------- 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