EBarr's questions

Resumidamente
, quais fatores entram na seleção do otimizador de consulta do índice de uma exibição indexada?

Para mim, exibições indexadas parecem desafiar o que eu entendo sobre como o Optimizer escolhe índices. Já vi isso ser perguntado antes , mas o OP não foi muito bem recebido. Estou realmente procurando por guias , mas vou inventar um pseudo-exemplo e, em seguida, postar um exemplo real com muito DDL, saída, exemplos.

Suponha que eu esteja usando o Enterprise 2008+, entendawith(noexpand)

Pseudo Exemplo

Veja este pseudo exemplo: eu crio uma visão com 22 junções, 17 filtros e um pônei de circo que cruza um monte de tabelas de 10 milhões de linhas. Essa visão é cara (sim, com E maiúsculo) para se materializar. Vou SCHEMABIND e Indexar a exibição. Então um SELECT a,b FROM AnIndexedView WHERE theClusterKeyField < 84. Na lógica do Optimizer que me escapa, as junções subjacentes são executadas.

O resultado:

• Nenhuma dica: 4825 leituras para 720 linhas, 47 cpu em 76 ms e um custo estimado de subárvore de 0,30523.
• Com dica: 17 leituras, 720 linhas, 15 cpu em 4ms e um custo estimado de subárvore de 0,007253

Então, o que está acontecendo aqui? Eu tentei no Enterprise 2008, 2008-R2 e 2012. Por cada métrica que posso pensar em usar o índice da exibição é muito mais eficiente. Não tenho problema de detecção de parâmetro ou dados distorcidos, pois isso é ad hock.

Um exemplo real (longo)

A menos que você seja um pouco masoquista, provavelmente não precisa ou não quer ler esta parte.

A versão
Sim, empresa.

Microsoft SQL Server 2012 - 11.0.2100.60 (X64) 10 de fevereiro de 2012 19:39:15 Copyright (c) Microsoft Corporation Enterprise Edition (64 bits) no Windows NT 6.2 (Build 9200: ) (Hypervisor)

A vista

CREATE VIEW dbo.TimelineMaterialized    WITH SCHEMABINDING
AS
SELECT  TM.TimelineID,
        TM.TimelineTypeID,
        TM.EmployeeID,
        TM.CreateUTC,
        CUL.CultureCode,
        CASE 
           WHEN TM.CustomerMessageID    > 0 THEN TM.CustomerMessageID
           WHEN TM.CustomerSessionID    > 0 THEN TM.CustomerSessionID
           WHEN TM.NewItemTagID         > 0 THEN TM.NewItemTagID
           WHEN TM.OutfitID             > 0 THEN TM.OutfitID
           WHEN TM.ProductTransactionID > 0 THEN TM.ProductTransactionID
           ELSE 0 END  As HrefId,
        CASE 
          WHEN TM.CustomerMessageID    > 0 THEN IsNull(C.Name, 'N/A')   
          WHEN TM.CustomerSessionID    > 0 THEN IsNull(C.Name, 'N/A')
          WHEN TM.NewItemTagID         > 0 THEN IsNull(NI.Title, 'N/A')
          WHEN TM.OutfitID             > 0 THEN IsNull(O.Name, 'N/A')
          WHEN TM.ProductTransactionID > 0 THEN IsNull(PT_PL.NameLocalized, 'N/A')
                 END as HrefText

FROM       dbo.Timeline TM
INNER JOIN dbo.CustomerSession    CS    ON TM.CustomerSessionID    = CS.CustomerSessionID
INNER JOIN dbo.CustomerMessage    CM    ON TM.CustomerMessageID    = CM.CustomerMessageID
INNER JOIN dbo.Outfit             O     ON PO.OutfitID             = O.OutfitID
INNER JOIN dbo.ProductTransaction PT    ON TM.ProductTransactionID = PT.ProductTransactionID
INNER JOIN dbo.Product            PT_P  ON PT.ProductID            = PT_P.ProductID
INNER JOIN dbo.ProductLang        PT_PL ON PT_P.ProductID          = PT_PL.ProductID
INNER JOIN dbo.Culture            CUL   ON PT_PL.CultureID         = CUL.CultureID
INNER JOIN dbo.NewsItemTag        NIT   ON TM.NewsItemTagID        = NIT.NewsItemTagID
INNER JOIN dbo.NewsItem           NI    ON NIT.NewsItemID          = NI.NewsItemID
INNER JOIN dbo.Customer           C     ON  C.CustomerID = CASE 
                                             WHEN TM.TimelineTypeID = 1 THEN CM.CustomerID 
                                             WHEN TM.TimelineTypeID = 5 THEN CS.CustomerID
                                             ELSE 0 END

WHERE        CUL.IsActive = 1

índice agrupado

CREATE UNIQUE CLUSTERED INDEX PK_TimelineMaterialized  ON 
                   TimelineMaterialized (EmployeeID, CreateUTC, CultureCode, TimelineID)

SQL de teste

-- NO HINT - - -  - - -  - - -  - - -  - - - 
SELECT  *                 --yes yes, star is bad ...just a test example
FROM    TimelineMaterialized TM 
WHERE 
            TM.EmployeeID   = 2
        AND TM.CultureCode  = 'en-US'
        AND TM.CreateUTC    > '9/10/2012'
        AND TM.CreateUTC    < '9/11/2012'

-- WITH HINT - - -  - - -  - - -  - - -  - - - 
SELECT  *               
FROM    TimelineMaterialized TM with(noexpand)
WHERE 
            TM.EmployeeID   = 2
        AND TM.CultureCode  = 'en-US'
        AND TM.CreateUTC    > '9/10/2012'
        AND TM.CreateUTC    < '9/11/2012'

Resultado = 11 Linhas de Saída

Saída do Profiler
As 4 primeiras linhas não contêm nenhuma dica. As 4 linhas inferiores estão usando a dica.

Planos de execução
GitHub Gist para ambos os planos de execução no formato SQLPlan

Nenhum plano de Hint Execution - por que não usar o índice clusterizado que lhe dei, Sr. SQL? Está agrupado nos 3 campos de filtro. Experimente, você pode gostar.

Plano simples ao usar uma dica.

Eu me deparo com uma situação no banco de dados com bastante frequência em que uma determinada tabela pode FK para uma das várias tabelas pai diferentes. Já vi duas soluções para o problema, mas nenhuma é pessoalmente satisfatória. Estou curioso para saber quais outros padrões você viu por aí? Existe uma maneira melhor de fazer isso?

Um exemplo artificial
Digamos que meu sistema tenha Alerts. Os alertas podem ser recebidos para uma variedade de objetos -- Clientes, Notícias e Produtos. Um determinado alerta pode ser para um e apenas um item. Por qualquer motivo, os clientes, artigos e produtos estão em movimento rápido (ou localizados), portanto, o texto/dados necessários não podem ser inseridos nos alertas após a criação de um alerta. Dada essa configuração, vi duas soluções.

Observação: o DDL abaixo é para SQL Server, mas minha pergunta deve ser aplicável a qualquer DBMS.

Solução 1 -- Múltiplas FKeys anuláveis

Nesta solução, a tabela vinculada a uma das muitas tabelas possui várias colunas FK (para simplificar, o DDL abaixo não mostra a criação de FK). O BOM - Nesta solução, é bom que eu tenha chaves estrangeiras. A opção nula do FK torna isso conveniente e relativamente fácil de adicionar dados precisos. O RUIM A consulta não é boa porque requer N LEFT JOINS ou N UNION para obter os dados associados. No SQL Server, especificamente o LEFT JOINS impede a criação de uma exibição indexada.

CREATE TABLE Product (
    ProductID    int identity(1,1) not null,
    CreateUTC    datetime2(7) not null,
     Name        varchar(100) not null
    CONSTRAINT   PK_Product Primary Key CLUSTERED (ProductID)
)
CREATE TABLE Customer (
    CustomerID  int identity(1,1) not null,
    CreateUTC   datetime2(7) not null,
     Name       varchar(100) not null
    CONSTRAINT  PK_Customer Primary Key CLUSTERED (CustomerID)
)
CREATE TABLE News (
    NewsID      int identity(1,1) not null,
    CreateUTC   datetime2(7) not null,
    Name        varchar(100) not null
    CONSTRAINT  PK_News Primary Key CLUSTERED (NewsID)
)

CREATE TABLE Alert (
    AlertID     int identity(1,1) not null,
    CreateUTC   datetime2(7) not null,
    ProductID   int null,
    NewsID      int null,
    CustomerID  int null,
    CONSTRAINT  PK_Alert Primary Key CLUSTERED (AlertID)
)

ALTER TABLE Alert WITH CHECK ADD CONSTRAINT CK_OnlyOneFKAllowed 
CHECK ( 
    (ProductID is not null AND NewsID is     null and CustomerID is     null) OR 
    (ProductID is     null AND NewsID is not null and CustomerID is     null) OR 
    (ProductID is     null AND NewsID is     null and CustomerID is not null) 
)

Solução 2 -- Um FK em cada Tabela Pai
Nesta solução, cada tabela 'pai' tem um FK para a tabela Alerta. Isso facilita a recuperação de alertas associados a um pai. No lado negativo, não há nenhuma cadeia real do Alerta para quem faz referência. Além disso, o modelo de dados permite alertas órfãos -- em que um alerta não está associado a um produto, notícias ou cliente. Novamente, vários LEFT JOINs para descobrir a associação.

CREATE TABLE Product (
    ProductID    int identity(1,1) not null,
    CreateUTC    datetime2(7) not null,
     Name        varchar(100) not null
    AlertID     int null,
    CONSTRAINT   PK_Product Primary Key CLUSTERED (ProductID)
)
CREATE TABLE Customer (
    CustomerID  int identity(1,1) not null,
    CreateUTC   datetime2(7) not null,
     Name       varchar(100) not null
    AlertID     int null,
    CONSTRAINT  PK_Customer Primary Key CLUSTERED (CustomerID)
)
CREATE TABLE News (
    NewsID      int identity(1,1) not null,
    CreateUTC   datetime2(7) not null,
    Name        varchar(100) not null
    AlertID     int null,
    CONSTRAINT  PK_News Primary Key CLUSTERED (NewsID)
)

CREATE TABLE Alert (
    AlertID     int identity(1,1) not null,
    CreateUTC   datetime2(7) not null,
    CONSTRAINT  PK_Alert Primary Key CLUSTERED (AlertID)
)

Isso é apenas vida em um banco de dados de relação? Existem soluções alternativas que você achou mais satisfatórias?