Projeto hierárquica Estrutura de Dados (conjuntos aninhados)

votos
4

Eu estou trabalhando em um projeto para uma estrutura de banco de dados hierárquico que modela um catálogo contendo produtos (isto é semelhante a esta pergunta ). A plataforma de banco de dados é o SQL Server 2005 eo catálogo é bastante grande (750.000 produtos, 8.500 seções catálogo com mais de 4 níveis), mas é relativamente estática (recarregada uma vez por dia) e por isso estamos apenas preocupados com o desempenho de leitura.

A estrutura geral da hierarquia catálogo é: -

  • Nível 1 Seção
    • Nível 2 Seção
      • 3 Seção nível
        • Nível 4 Seção (produtos estão ligados a aqui)

Estamos usando o Nested Define padrão para armazenar os níveis de hierarquia e armazenagem dos produtos que existem nesse nível em uma tabela vinculada separado. Assim, a estrutura de banco de dados simplificada seria

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

CREATE TABLE CatalogueProduct
(
    ProductID INTEGER,
    SectionID INTEGER
)

Nós temos uma complicação adicional em que temos cerca de 1000 grupos de clientes distintos, que pode ou não pode ver todos os produtos no catálogo. Devido a isso, precisamos manter uma cópia separada da hierarquia catálogo para cada grupo de clientes para que quando eles navegar pelo catálogo, eles só vêem os seus produtos e eles também não vêem quaisquer seções que estão vazios.

Para facilitar isso, manter uma tabela do número de produtos em cada nível da hierarquia enrolado da seção abaixo. Assim, mesmo que os produtos são apenas directamente ligado ao nível mais baixo da hierarquia, eles são contados todo o caminho até a árvore. A estrutura desta tabela é

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

Assim, para o problema de desempenho é muito pobre nos níveis superiores da hierarquia. A consulta geral para mostrar os produtos top 10 na seção catálogo selecionado (e todas as seções criança) está tomando algum lugar na região de 1 minuto para concluir. No seções inferiores na hierarquia é mais rápido, mas ainda não é bom o suficiente.

Eu coloquei índices (incluindo índices que cobrem quando aplicável) em todas as mesas principais, executá-lo através do analisador de consulta, o índice de ajuste assistente etc, mas ainda não pode obtê-lo para executar rápido o suficiente.

Eu estou querendo saber se o projeto é fundamentalmente falho ou se é porque temos um grande conjunto de dados, tais? Temos um servidor de desenvolvimento razoável (3.8GHz Xeon, 4GB RAM), mas ele simplesmente não está funcionando :)

Obrigado por qualquer ajuda

James

Publicado 10/12/2008 em 11:28
fonte usuário
Em outras línguas...                            


3 respostas

votos
6

Use uma mesa de encerramento. Se a sua estrutura básica é um pai-filho com os campos ID e ParentID, então a estrutura para uma mesa de encerramento é ID e DescendantID. Em outras palavras, uma tabela de fecho é uma tabela antepassado-descendente, onde cada um possível antepassado está associado com todos os descendentes. Você pode incluir um campo LevelsBetween se você precisar. implementações tabela de fecho geralmente incluem registos auto-referenciar, ou seja, uma ID é um antepassado de ID descendente um com LevelsBetween de zero.

Exemplo: pai / filho
ParentID - ID
1 - 2
1 - 3
3 - 4
3 - 5
4 - 6

Ancestral / descendente
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 - o 4 - 0
4-6 - 1
5 - 5-0

A tabela visa eliminar recursiva junta. Você empurra a carga do recursiva juntar-se em um ciclo de ETL que você faz quando você carregar os dados uma vez por dia. Que muda-lo longe da consulta.

Além disso, ele permite hierarquias de nível variável. Você não vai ser preso a 4.

Finalmente, ele permite que você encaixar produtos em nós não-folha. Um monte de catálogos criar baldes "Diversos" nos níveis mais altos da hierarquia para criar uma folha-nó para anexar produtos para. Você não precisa fazer isso desde que nós intermediários estão incluídos no fechamento.

Na medida em que a indexação vai, eu faria um índice agrupado em ID / DescendantID.

Agora para o seu desempenho da consulta. Isso leva um pedaço mas não todos. Você mencionou um "Top 10". Isto implica escalão sobre um conjunto de fatos que você não tenha mencionado. Precisamos de detalhes para ajudar a ajustar os. Além disso, este só se obtém as seções de nível folha, e não os produtos. No mínimo, você deve ter um índice em seu CatalogueProduct que as ordens por sectionId / ProductID. Eu forçaria Seção para o Produto junta-se a ser laço junta-se com base na cardinalidade que você forneceu. Um relatório sobre uma seção catálogo iria para a mesa de encerramento para obter descendentes (usando um índice agrupado procurar). Essa lista de descendentes, então, ser usado para obter produtos de CatalogueProduct usando o índice pelo índice de loop procura. Então, com esses produtos, você deseja obter os elementos necessários para fazer o ranking.

Respondeu 10/12/2008 em 17:55
fonte usuário

votos
0

Poderia ser possível calcular a ProductCount e SubSectionCount após a carga a cada dia?
Se os dados forem mudando apenas uma vez por dia, certamente vale a pena calcular esses números, então, mesmo se é necessária alguma desnormalização.

Respondeu 10/12/2008 em 16:18
fonte usuário

votos
0

você pode ser capaz de resolver o problema grupos de clientes com papéis e treeId de mas você terá que fornecer-nos com a consulta.

Respondeu 10/12/2008 em 12:24
fonte usuário

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