Eu tenho uma consulta como a seguinte:
DELETE FROM tblFEStatsBrowsers WHERE BrowserID NOT IN (
SELECT DISTINCT BrowserID FROM tblFEStatsPaperHits WITH (NOLOCK) WHERE BrowserID IS NOT NULL
)
tblFEStatsBrowsers tem 553 linhas.
tblFEStatsPaperHits tem 47.974.301 linhas.
tblFEStatsNavegadores:
CREATE TABLE [dbo].[tblFEStatsBrowsers](
[BrowserID] [smallint] IDENTITY(1,1) NOT NULL,
[Browser] [varchar](50) NOT NULL,
[Name] [varchar](40) NOT NULL,
[Version] [varchar](10) NOT NULL,
CONSTRAINT [PK_tblFEStatsBrowsers] PRIMARY KEY CLUSTERED ([BrowserID] ASC)
)
tblFEStatsPaperHits:
CREATE TABLE [dbo].[tblFEStatsPaperHits](
[PaperID] [int] NOT NULL,
[Created] [smalldatetime] NOT NULL,
[IP] [binary](4) NULL,
[PlatformID] [tinyint] NULL,
[BrowserID] [smallint] NULL,
[ReferrerID] [int] NULL,
[UserLanguage] [char](2) NULL
)
Há um índice clusterizado em tblFEStatsPaperHits que não inclui BrowserID. A execução da consulta interna exigirá, portanto, uma varredura completa da tabela de tblFEStatsPaperHits - o que está totalmente OK.
Atualmente, uma varredura completa é executada para cada linha em tblFEStatsBrowsers, o que significa que tenho 553 varreduras completas da tabela de tblFEStatsPaperHits.
Reescrever apenas para WHERE EXISTS não altera o plano:
DELETE FROM tblFEStatsBrowsers WHERE NOT EXISTS (
SELECT * FROM tblFEStatsPaperHits WITH (NOLOCK) WHERE BrowserID = tblFEStatsBrowsers.BrowserID
)
No entanto, conforme sugerido por Adam Machanic, adicionar uma opção HASH JOIN resulta no plano de execução ideal (apenas uma única varredura de tblFEStatsPaperHits):
DELETE FROM tblFEStatsBrowsers WHERE NOT EXISTS (
SELECT * FROM tblFEStatsPaperHits WITH (NOLOCK) WHERE BrowserID = tblFEStatsBrowsers.BrowserID
) OPTION (HASH JOIN)
Agora, isso não é tanto uma questão de como consertar isso - posso usar a OPTION (HASH JOIN) ou criar uma tabela temporária manualmente. Estou mais me perguntando por que o otimizador de consulta usaria o plano que usa atualmente.
Como o QO não possui nenhuma estatística na coluna BrowserID, suponho que esteja assumindo o pior - 50 milhões de valores distintos, exigindo assim uma tabela de trabalho em memória/tempdb bastante grande. Dessa forma, a maneira mais segura é realizar verificações para cada linha em tblFEStatsBrowsers. Não há relacionamento de chave estrangeira entre as colunas BrowserID nas duas tabelas, portanto, o QO não pode deduzir nenhuma informação de tblFEStatsBrowsers.
É este, tão simples quanto parece, o motivo?
Atualização 1
Para fornecer algumas estatísticas: OPÇÃO (HASH JOIN):
208.711 leituras lógicas (12 varreduras)
OPÇÃO (LOOP JOIN, HASH GROUP):
11.008.698 leituras lógicas (~scan per BrowserID (339))
Sem opções:
11.008.775 leituras lógicas (~scan per BrowserID (339))
Atualização 2
Excelentes respostas, todos vocês - obrigado! Difícil escolher apenas um. Embora Martin tenha sido o primeiro e Remus forneça uma excelente solução, tenho que dar ao Kiwi por pensar nos detalhes :)
Mostra os dois caminhos SAN sendo utilizados e, em seguida, descartados.
O backup foi iniciado por volta das 15:38:50 - observe que tudo parece bom e, em seguida, há uma série de picos. Não estou preocupado com as gravações, apenas as leituras parecem travar.
Observe muito pouca ação liga/desliga, embora um desempenho incrível no final.
Observe um máximo de 12 segundos, embora a média geral seja boa.
