Иерархическая структура данных Дизайн (Nested Sets)

голоса
4

Я работаю над дизайном для иерархической структуры базы данных , которая моделирует каталог , содержащий продукты (это похоже на этот вопрос ). Платформа базы данных SQL Server 2005 и каталог является довольно большим (750000 продукты, 8,500 каталог секций более 4 -х уровней) , но является относительно статичным (перезагружается один раз в день) , и поэтому мы только о производительности READ.

Общая структура иерархии каталога является: -

  • Уровень 1 Раздел
    • Уровень 2 Раздел
      • Уровень 3 Раздел
        • Уровень 4 Раздела (продукты связаны здесь)

Мы используем Уплотненный Устанавливает шаблон для сохранения уровней иерархии и хранения продуктов, которые существуют на этом уровне в отдельной связанной таблице. Таким образом, упрощенная структура базы данных будет

CREATE TABLE CatalogueSection
(
    SectionID INTEGER,
    ParentID INTEGER,
    LeftExtent INTEGER,
    RightExtent INTEGER
)

CREATE TABLE CatalogueProduct
(
    ProductID INTEGER,
    SectionID INTEGER
)

У нас есть дополнительное усложнение в том, что у нас есть около 1000 отдельных групп клиентов, которые могут или не могут видеть все продукты в каталоге. Из-за этого нам нужно создать отдельный «копию» иерархии каталога для каждой группы клиентов, так что при просмотре каталога, они видят только свою продукцию, и они также не видят каких-либо разделов, которые пусты.

Чтобы облегчить это, мы поддерживать таблицу количества продуктов на каждом уровне иерархии «свернутой» из раздела ниже. Таким образом, даже если продукты только непосредственно связаны с низким уровнем иерархии, они засчитываются весь путь вверх по дереву. Структура этой таблицы

CREATE TABLE CatalogueSectionCount
(
    SectionID INTEGER,
    CustomerGroupID INTEGER,
    SubSectionCount INTEGER,
    ProductCount INTEGER
)

Таким образом, на проблемы производительности очень беден на верхних уровнях иерархии. Общий запрос , чтобы показать «10 лучших» продукты в выбранном разделе каталога (и все дочерние разделы) занимает где - то в районе 1 минуты до завершения. В нижних отделах в иерархии он быстрее , но все еще не достаточно хорошо.

Я поставил индексы (в том числе покрывающих индексов, где это применимо) на все основные таблицы, запустить его через анализатор запросов, настройки индекса мастер и т.д., но все еще не может получить его, чтобы выполнить достаточно быстро.

Я задаюсь вопросом, является ли дизайн в корне ошибочным или является ли это потому, что у нас есть такой большой набор данных? У нас есть разумный сервер разработки (3.8 ГГц Xeon, 4 Гб оперативной памяти), но это просто не работает :)

Спасибо за любую помощь

Джеймс

Задан 10/12/2008 в 11:28
источник пользователем
На других языках...                            


3 ответов

голоса
6

Используйте таблицу закрытия. Если ваша основная структура родитель-ребенок с полями ID и ParentID, то структура таблицы закрытия является ID и DescendantID. Другими словами, таблица закрытия является предком-потомок таблицы, где каждый возможный предок связан со всеми потомками. Вы можете включать в себя поле LevelsBetween, если вам нужно. Закрытие реализации таблиц обычно включают автореферентную запись, т.е. идентификатор 1 является предком потомка ID 1 с LevelsBetween нуля.

Пример: Родитель / Ребенок
ParentID - ID
1 - 2
1 - 3
3 - 4
3 - 5
4 - 6

Предок / Потомок
ID - DescendantID - LevelsBetween
1 - 1 - 0
1 - 2 - 1
1 - 3 - 1
1 - 4 - 2
1 - 6 - 3
2 - 2 - 0
3 - 3 - 0
3 - 4 - 1
3 - 5 - 1
3 - 6 - 2
4 - 4 - 0
4 - 6 - 1
5 - 5 - 0

Таблица предназначена для устранения рекурсивных соединений. Нажимаешь нагрузку рекурсивной объединятся в цикле ETL, что вы делаете, когда вы загружаете данные один раз в день. Это смещает его в сторону от запроса.

Кроме того, она позволяет иерархии переменного уровня. Вы не застрянете на 4.

Наконец, она позволяет прорезать продукты в не листовых узлах. Много каталогов создать «Разные» ведра на более высоких уровнях иерархии, чтобы создать лист-узел для присоединения изделий к. Вам не нужно делать это, поскольку промежуточные узлы включены в замыкании.

Что касается индексации, то я бы кластерный индекс по ID / DescendantID.

Теперь для выполнения запроса. Это берет кусок из, но не все. Вы упомянули «Top 10». Это предполагает ранжирование по множеству фактов, которые вы не упомянули. Нам нужны детали, чтобы помочь настроить те. Кроме того, это получает только получает секции листа на уровне, а не продукты. По крайней мере, вы должны иметь индекс на вашем CatalogueProduct, что заказы по SectionID / ProductID. Я хотел бы заставить Раздел к продукту присоединяется быть петля присоединяется на основе мощности предоставленной вами. Отчет о разделе каталога будет идти к столу закрытия, чтобы получить потомство (с помощью кластерного индекса поиска). Этот список потомков будет затем использоваться, чтобы получить продукты из CatalogueProduct с использованием индекса по петлевой индекс стремится. Затем с этими продуктами, вы получите факты, необходимые для выполнения ранжирования.

Ответил 10/12/2008 в 17:55
источник пользователем

голоса
0

Может это можно вычислить ProductCount и SubSectionCount после нагрузки каждый день?
Если данные меняются только один раз в день , конечно , имеет смысл рассчитать эти цифры , то, даже если требуется некоторая денормализация.

Ответил 10/12/2008 в 16:18
источник пользователем

голоса
0

Вы могли бы быть в состоянии решить эту проблему групп клиентов с ролями и treeId, но вы должны будете предоставить нам запрос.

Ответил 10/12/2008 в 12:24
источник пользователем

Cookies help us deliver our services. By using our services, you agree to our use of cookies. Learn more