Эффективное управление наследованием

Ответ 1

У меня есть 3 возможных ответа.

Скрипт sql для вашего вопроса находится здесь: http://sqlfiddle.com/#!3/7c7a0/3/0

Скрипт sql для моего ответа находится здесь: http://sqlfiddle.com/#!3/5d257/1

Предупреждения:

• Анализатор запросов недостаточно.. Я заметил, что ряд ответов был отклонен, потому что их планы запросов более дороги, чем исходный запрос. Анализатор - только руководство. В зависимости от фактического набора данных, аппаратного обеспечения и варианта использования более дорогие запросы могут возвращать результаты быстрее, чем менее дорогие. Вы должны протестировать в своей среде.
• Анализатор запросов неэффективен - даже если вы найдете способ удалить "самый дорогой шаг" из запроса, это часто не имеет никакого отношения к вашему запросу.
• Только изменения запроса редко устраняют проблемы схемы/дизайна.. Некоторые ответы были отклонены, поскольку они связаны с изменениями уровня схемы, такими как триггеры и дополнительные таблицы. Комплексные запросы, которые противостоят оптимизации, являются сильным признаком того, что проблема связана с базовым дизайном или с моими ожиданиями. Возможно, вам это не понравится, но вам, возможно, придется признать, что проблема не разрешима на уровне запросов.
• Индексированное представление не может содержать предложение row_number()/partitition. Работа над проблемой самосоединения путем создания шести экземпляров таблицы объектов недостаточно, чтобы вы могли создать предложенный вами индексированный вид, Я попробовал это в this sqlfiddle. Если вы раскомментируете этот последний оператор "create index", вы получите сообщение об ошибке, потому что ваше представление "содержит функцию ранжирования или агрегатного окна".

Ответы на вопросы:

• Левая регистрация вместо row_number(). Вы можете использовать запрос, который использует левые соединения для исключения результатов, которые были переопределены ниже в дереве. Удаление окончательного "заказа по" из этого запроса фактически удаляет сортировку, которая вас преследовала! План выполнения этого запроса по-прежнему дороже оригинала, но см. Отказ от ответственности № 1 выше.
• Индексированное представление для части запроса. Используя некоторую серьезную магию запросов (на основе этот метод), я созданный индексированный вид для части запроса. Это представление может использоваться для улучшения исходного вопроса или ответа # 1.
• Актуализировать в хорошо индексированную таблицу. Кто-то предложил этот ответ, но они, возможно, не объяснили это хорошо. Если ваш результирующий набор очень велик или вы очень часто обновляете исходные таблицы, то актуализация результатов запроса и использование триггера, чтобы поддерживать их в актуальном состоянии, - прекрасный способ обойти эту проблему. Создав представление для вашего запроса, достаточно проверить этот параметр. Вы можете повторно использовать ответ # 2, чтобы ускорить запуск, а затем еще больше улучшить его с течением времени. (Вы говорите о создании шести копий своих таблиц, сначала попробуйте это. Это гарантирует, что производительность для выбранного вами вопроса будет максимально возможной.)

Вот часть моего ответа от 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

Запросить часть моего ответа из 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

Ответ 2

Вы можете ускорить это, материализовывая соединение в индексированной таблице, скажем, joinresult. Это имеет недостаток в необходимости пространства и экономии на диске. Но это имеет то преимущество, что можно использовать индекс для медленной части.

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)

Убедитесь, что joinresult имеет индекс в [O1, O2, O3, Property, O1D, O2D, O3D] и очистите его перед запуском. Тогда

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

Ответ 3

Пробовали ли вы индекс (или установить pk) сначала столбец "Значение", второй столбец "Свойство", третий столбец "Object1", четвертый столбец "Object2" и столбец "Object3"? Я предполагаю, что "Значение" является более ограничительным, чем "Свойство".

Я также предполагаю, что у вас есть столбец идентификатора в качестве первичного ключа и отношение внешнего ключа между ParentId и Id.

Как выполняется этот запрос?:

    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;

Ответ 4

Иерархические запросы, то есть WITH RECURSIVE ... или запатентованные эквиваленты, такие как CONNECT BY являются вашим другом в этом случае.

Рецепт для решения вашей конкретной проблемы: Начните с отпуска и поднимитесь на корневое агрегирование и исключая все, что было найдено уже.

Ответ 5

Я предполагаю, что ваш стол довольно большой. Отсюда медленность. В этом случае я также предполагаю, что у вас есть несколько свойств (от 2 до многих). В этом случае я предлагаю вам переместить "where property = 'P1" внутри CTE. Это позволит отфильтровать значительную часть данных, создающих ваш запрос так быстро, как быстрее, чем количество свойств.

Что-то вроде: http://sqlfiddle.com/#!3/7c7a0/92/0

Ответ 6

Кэширование - это ключ для ускорения запроса. Это уменьшает расчеты, которые вы должны сделать. Вы хотите создать индекс, потому что хотите CACHE, и сохраните РАБОТУ. Ниже приведены две возможности для этого.

Вариант 1

База данных SQL сортируется из-за вашей функции окон. И вы говорите, что оконная функция слишком медленная, как есть.

Я не знаю, как хорошо это будет работать, но это может сработать.

Вместо сортировки по нескольким столбцам вы можете попробовать сортировать по одному столбцу - "близость".

Теперь определим близость как некоторое абстрактное целое число. Вместо вашей функции окон вы можете иметь следующий SQL:

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 closeness DESC
        )
        as InheritancePriority
    from
    ... (see above)
)
where InheritancePriority = 1

близость может быть столбцом, определенным в таблице TriplesAndProperties. Для каждого объекта вы можете определить его "близость", так как расстояние оно от корня node (O1). Тогда мы можем определить closeness(tuple) = closeness(Object1)*100+closeness(Object2)*10+closeness(Object3)

Таким образом, кортеж с самым большим корнем - это то, что вы хотите.

Чтобы избежать сортировки, вам просто нужно убедиться, что закрытость проиндексирована.

Вариант 2

Я ОЧЕНЬ уверен, что это сработает.

Определите таблицу TriplesAndProperties для столбцов: Object1, Object2, Object3, Property, Value, Effective_Object1, Effective_Object2, Effective_Object3, Closeness.

Обратите внимание, что здесь я также определяю близость как столбец.

Когда вы вставляете/обновляете кортеж в свою таблицу (X, Y, Z), вместо этого вы хотите вставить:

(X,Y,Z,Property,Value,X,Y,Z,0)
(X,Y,Z,Property,Value,X,Y,Z.child,1)
(X,Y,Z,Property,Value,X,Y,Z.grandchild,2)
(X,Y,Z,Property,Value,X,Y.child,Z,10)
(X,Y,Z,Property,Value,X,Y.child,Z.child,11)
(X,Y,Z,Property,Value,X,Y.child,Z.grandchild,12)
(X,Y,Z,Property,Value,X,Y.grandchild,Z,20)
(X,Y,Z,Property,Value,X,Y.grandchild,Z.child,21)
(X,Y,Z,Property,Value,X,Y.grandchild,Z.grandchild,22)
...
...

Это означает, что вместо вставки/обновления/уничтожения одной строки в вашей таблице вы вставляете до ~ 20 строк. Это не так уж плохо.

Тогда ваш запрос ОЧЕНЬ ЛЕГКО.

Вы просто скажете:

SELECT * FROM
    (
    SELECT Effective_Object1, Effective_Object2, Effective_Object3, Property, Value,
        row_number() over( 
            partition by Effective_Object1, Effective_Object2, Effective_Object3, Property
            order by Closeness DESC
        ) AS InheritancePriority FROM TriplesAndProperties
     ) WHERE InheritancePriority = 1;

В этом случае вы должны убедиться, что близость проиндексирована, вы можете просто индексировать по кортежу (Effective_Object1, Effective_Object2, Effective_Object3, Property, Closeness).

В обоих случаях у вас есть некоторое количество кеширования, которое представляет собой данные, которые не добавляют никакой дополнительной информации как таковой, но которая кэширует определенное количество расчет или .