Início / dba / Perguntas / 103988
Accepted
Evaldas Buinauskas
Asked: 2015-06-14 06:16:32 +0800 CST

Tabelas subjacentes do filtro dinâmico do SQL Server por aliases especificados na exibição

  • 772

Tenho a estrutura da tabela da seguinte forma:

CREATE TABLE [dbo].[Test] (
   [ID] [int] IDENTITY(1, 1) NOT NULL
   , [ProductID] [int] NOT NULL
   , [CountryID] [int] NOT NULL
   , CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED ([ID] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 ) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TestData] (
   [ID] [int] IDENTITY(1, 1) NOT NULL
   , [TestID] [int] NOT NULL
   , [TimeSeries] [int] NOT NULL
   , [Values] [float] NULL
   , CONSTRAINT [PK_TestData] PRIMARY KEY CLUSTERED ([ID] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 ) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TestData]
   WITH CHECK ADD CONSTRAINT [FK_Test_TestData] FOREIGN KEY ([TestID]) REFERENCES [dbo].[Test]([ID])
GO

ALTER TABLE [dbo].[TestData] CHECK CONSTRAINT [FK_Test_TestData]
GO

Alguns dados de teste:

INSERT INTO dbo.Test (ProductID, CountryID)
VALUES (1, 1), (1, 2), (1, 3), (1, 4)
    , (2, 1);

INSERT INTO dbo.TestData (TestID, TimeSeries, [Values])
VALUES (1, 2013, 0.5), (1, 2014, 1.9)
    , (2, 2013, 0.78), (2, 2014, 9.7)
    , (3, 2012, 0.77), (3, 2013, 5.6), (3, 2014, 1.54)
    , (4, 2011, 0.81), (4, 2014, 6.7);

E eu criei uma visão dinâmica a partir dele:

CREATE VIEW dbo.vw_PivotedTest
AS
SELECT ProductID, CountryID, [2011], [2012], [2013], [2014]
FROM (
    SELECT T.ProductID, T.CountryID, TD.TimeSeries, TD.[Values]
    FROM dbo.Test AS T
    LEFT JOIN dbo.TestData AS TD
        ON TD.TestID = T.ID
    ) AS S
PIVOT (
    AVG([Values])
    FOR TimeSeries IN ([2011], [2012], [2013], [2014])
    ) AS P;

E eu perguntaria assim:

SELECT ProductID, CountryID, [2011]
FROM dbo.vw_PivotedTest;

Agora vem a pergunta: é possível para o SQL Server escolher apenas linhas de dbo.TestData, onde o valor TimeSeries é apenas 2011, em vez de digitalizar tudo?

Dei uma olhada no plano de execução e parece estar verificando toda a tabela e não filtra esses resultados.

Plano de execução

sql-server sql-server-2014

1 respostas

  1. Best Answer

    Não como está, você terá que adicionar um índice para ele e isso nunca será capaz de digitalizar apenas a coluna 2011 da exibição dinâmica - adicionar índice a essa exibição não é possível e dará um erro Msg 10114, Level 16, State 1, Line 17 Cannot create index on view "a.dbo.vw_PivotedTest" because it uses the PIVOT operator. Consider not indexing this view..

    Em seguida, você precisa criar um índice para os dados subjacentes para que o operador Pivot seja executado mais rapidamente. Vejamos se o Optimizer tem algo a dizer.

    Seus dados de amostra são muito pequenos, então execute sua consulta assim.

    SELECT ProductID, CountryID, [2011]
 FROM dbo.vw_PivotedTest 
 option (OPTIMIZE FOR UNKNOWN);

    O que otimizará a consulta para um conjunto maior, você também pode adicionar GO 50após a instrução de inserção na tabela dbo.TestData para executar as instruções de inserção 50 vezes e, assim, adicionar mais dados e, em seguida, pode ignorar a parte da opção.

    Você receberá a seguinte sugestão de índice do otimizador:

    CREATE INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[TestData] ([TestID])
INCLUDE ([TimeSeries],[Values]);

    O que ajudará, mas digitalizará o ProductID - o que faz sentido, você também pode criar esse índice como este

    CREATE NONCLUSTERED INDEX idx2
ON [dbo].[TestData] ([TestID],[TimeSeries],[Values]);

    Mas você acabará com o mesmo plano de consulta e inserções mais caras, portanto, há pouco a ganhar.

    QueryPlanWithAndWithoutInclude

    • 2

relate perguntas