Início / dba / Perguntas / 23766
Accepted
peter
Asked: 2012-09-06 15:04:23 +0800 CST

Como otimizar a consulta

  • 772

Eu tenho uma estrutura de banco de dados semelhante a esta,

CREATE TABLE [dbo].[Dispatch](
    [DispatchId] [int] NOT NULL,
    [ContractId] [int] NOT NULL,
    [DispatchDescription] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Dispatch] PRIMARY KEY CLUSTERED 
(
    [DispatchId] ASC,
    [ContractId] 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].[DispatchLink](
    [ContractLink1] [int] NOT NULL,
    [DispatchLink1] [int] NOT NULL,
    [ContractLink2] [int] NOT NULL,
    [DispatchLink2] [int] NOT NULL
) ON [PRIMARY]

GO
INSERT [dbo].[Dispatch] ([DispatchId], [ContractId], [DispatchDescription]) VALUES (1, 1, N'Test')
GO
INSERT [dbo].[Dispatch] ([DispatchId], [ContractId], [DispatchDescription]) VALUES (2, 1, N'Test')
GO
INSERT [dbo].[Dispatch] ([DispatchId], [ContractId], [DispatchDescription]) VALUES (3, 1, N'Test')
GO
INSERT [dbo].[Dispatch] ([DispatchId], [ContractId], [DispatchDescription]) VALUES (4, 1, N'Test')
GO
INSERT [dbo].[DispatchLink] ([ContractLink1], [DispatchLink1], [ContractLink2], [DispatchLink2]) VALUES (1, 1, 1, 2)
GO
INSERT [dbo].[DispatchLink] ([ContractLink1], [DispatchLink1], [ContractLink2], [DispatchLink2]) VALUES (1, 1, 1, 3)
GO
INSERT [dbo].[DispatchLink] ([ContractLink1], [DispatchLink1], [ContractLink2], [DispatchLink2]) VALUES (1, 3, 1, 2)
GO

O objetivo da tabela DispatchLink é vincular dois registros de Dispatch. A propósito, estou usando uma chave primária composta em minha tabela de despacho por causa do legado, então não posso mudar isso sem muito trabalho. Além disso, a tabela de links pode não ser a maneira correta de fazer isso? Mas novamente legado.

Então, minha pergunta, se eu executar esta consulta

select * from Dispatch d
inner join DispatchLink dl on d.DispatchId = dl.DispatchLink1 and d.ContractId = dl.ContractLink1
or d.DispatchId = dl.DispatchLink2 and d.ContractId = dl.ContractLink2

Nunca consigo fazer uma busca de índice na tabela DispatchLink. Ele sempre faz uma varredura de índice completa. Isso é bom com alguns registros, mas quando você tem 50.000 nessa tabela, ele verifica 50.000 registros no índice de acordo com o plano de consulta. É porque há 'ands' e 'ors' na cláusula join, mas não consigo entender por que o SQL não pode fazer algumas buscas de índice, uma para o lado esquerdo do 'or', e um para o lado direito do 'ou'.

Eu gostaria de uma explicação para isso, não uma sugestão para tornar a consulta mais rápida, a menos que isso possa ser feito sem ajustar a consulta. O motivo é que estou usando a consulta acima como um filtro de junção de replicação de mesclagem, portanto, infelizmente, não posso simplesmente adicionar outro tipo de consulta.

ATUALIZAÇÃO: Por exemplo, esses são os tipos de índices que venho adicionando,

CREATE NONCLUSTERED INDEX IDX1 ON DispatchLink (ContractLink1, DispatchLink1)
CREATE NONCLUSTERED INDEX IDX2 ON DispatchLink (ContractLink2, DispatchLink2)
CREATE NONCLUSTERED INDEX IDX3 ON DispatchLink (ContractLink1, DispatchLink1, ContractLink2, DispatchLink2)

Então ele usa os índices, mas faz uma varredura de índice em todo o índice, então 50.000 registros ele varre 50.000 registros no índice.

sql-server performance

1 respostas

  1. Best Answer

    O otimizador pode considerar muitas alternativas de plano (incluindo algumas com buscas múltiplas), mas para disjunções ( ORpredicados) ele não considera planos envolvendo interseções de índice por padrão. Dados os índices:

    CREATE CLUSTERED INDEX cx 
ON dbo.DispatchLink (DispatchLink1, ContractLink1);

CREATE NONCLUSTERED INDEX nc1 
ON dbo.DispatchLink (DispatchLink2, ContractLink2);

    Podemos forçar buscas de índice (assumindo o SQL Server 2008 ou posterior):

    SELECT * 
FROM dbo.Dispatch AS d
INNER JOIN dbo.DispatchLink AS dl WITH (FORCESEEK) ON 
    (d.DispatchId = dl.DispatchLink1 AND d.ContractId = dl.ContractLink1)
    OR (d.DispatchId = dl.DispatchLink2 AND d.ContractId = dl.ContractLink2);

    plano FORCESEEK

    Usando seus dados de amostra, o plano de busca custa 0,0332551 unidades em comparação com 0,0068057 para o plano de varredura:

    plano de digitalização

    Existem todos os tipos de possíveis reescritas de consulta e dicas que podemos tentar. Um exemplo de reescrita para promover uma opção que o otimizador não considera para o plano original é:

    SELECT * 
FROM dbo.Dispatch AS d
CROSS APPLY
(
    SELECT TOP (1) * FROM
    (
        SELECT * FROM dbo.DispatchLink AS dl
        WHERE dl.DispatchLink1 = d.DispatchId
        AND dl.ContractLink1 = d.ContractId
        UNION ALL
        SELECT * FROM dbo.DispatchLink AS dl
        WHERE dl.DispatchLink2 = d.DispatchId
        AND dl.ContractLink2 = d.ContractId
    ) SQ1
) AS F1;

    Este plano de execução não busca o segundo índice se encontrar uma correspondência no primeiro:

    APLICAR plano TOP

    Isso pode ter um desempenho ligeiramente melhor do que o FORCESEEKplano padrão.

    Sem adicionar novos índices, também podemos forçar uma busca na tabela Dispatch:

    SELECT * 
FROM dbo.DispatchLink AS dl
JOIN dbo.Dispatch AS d WITH (FORCESEEK) ON
    (d.DispatchId = dl.DispatchLink1 AND d.ContractId = dl.ContractLink1)
    OR (d.DispatchId = dl.DispatchLink2 AND d.ContractId = dl.ContractLink2);

    Procurar 2

    Isso pode ser melhor ou pior do que o primeiro exemplo, dependendo de coisas como quantas linhas existem em cada uma das tabelas. A APPLY + TOPmelhoria ainda é possível:

    SELECT * 
FROM dbo.DispatchLink AS dl
CROSS APPLY
(
    SELECT TOP (1) * FROM
    (
        SELECT * FROM dbo.Dispatch AS d
        WHERE dl.DispatchLink1 = d.DispatchId
        AND dl.ContractLink1 = d.ContractId
        UNION ALL
        SELECT * FROM dbo.Dispatch AS d
        WHERE dl.DispatchLink2 = d.DispatchId
        AND dl.ContractLink2 = d.ContractId
    ) SQ1
) AS F1;
    • 12

relate perguntas