Início / dba / Perguntas / 318389
Accepted
Sicilian-Najdorf
Asked: 2022-10-19 23:04:48 +0800 CST

Buscar predicados com desigualdades - Predicado Residual

  • 772

Alguém poderia me orientar para uma melhor compreensão das buscas de índice com vários predicados de desigualdade. Considere a seguinte tabela:

CREATE TABLE [dbo].[table_1](
[DBINVHDID] [int] IDENTITY(1,1) NOT NULL,
[TYPE] [nvarchar](1) NULL,
[INV_NO] [nvarchar](13) NULL,
[ACCOUNT] [nvarchar](10) NULL,
[NAME] [nvarchar](40) NULL,
[ADDR1] [nvarchar](25) NULL,
[ADDR2] [nvarchar](25) NULL,
[ADDR3] [nvarchar](25) NULL,
[CONTACT] [nvarchar](25) NULL,
[GST_NO] [nvarchar](15) NULL,
[CR_TERMS] [nvarchar](10) NULL,
[OREF] [nvarchar](20) NULL,
[YREF] [nvarchar](20) NULL,
[SALESREP] [nvarchar](20) NULL,
[DISCOUNT] [decimal](18, 2) NULL,
[TRANSPORT] [decimal](18, 2) NULL,
[INS] [decimal](18, 2) NULL,
[GST] [decimal](18, 2) NULL,
[TOTAL] [decimal](18, 2) NULL,
[DATE] [smalldatetime] NULL,
[LINE] [smallint] NULL,
[PSLIP] [nvarchar](10) NULL,
[VAT] [nvarchar](1) NULL,
[STORE] [nvarchar](3) NULL,
[STKSTR] [nvarchar](3) NULL,
[POST] [smalldatetime] NULL,
[PAID] [decimal](18, 2) NULL,
[WEIGHT] [decimal](18, 4) NULL,
[INSURE] [nvarchar](15) NULL,
[HAND] [decimal](18, 2) NULL,
[CODC] [decimal](18, 2) NULL,
[TOPAY] [decimal](18, 2) NULL,
[IND1] [nvarchar](1) NULL,
[IND2] [nvarchar](1) NULL,
[IND3] [nvarchar](1) NULL,
[COST] [decimal](18, 2) NULL,
[REPC] [nvarchar](6) NULL,
[DISPATCH] [nvarchar](1) NULL,
[TILLNO] [nvarchar](2) NULL,
[USER] [nvarchar](6) NULL,
[RCOMM] [decimal](18, 2) NULL,
[DISP1] [nvarchar](1) NULL,
[DISP2] [nvarchar](7) NULL,
[PRNSEQ] [int] NULL,
[TIME] [nvarchar](20) NULL,
[ROUND] [decimal](18, 2) NULL,
[MESSAGE1] [nvarchar](50) NULL,
[MESSAGE2] [nvarchar](50) NULL,
[MESSAGE3] [nvarchar](50) NULL,
[PRINT] [nvarchar](1) NULL,
[PDATE] [smalldatetime] NULL,
[FILENO] [nvarchar](20) NULL,
[sched6] [nvarchar](1) NULL,
[BUYACC] [nvarchar](10) NULL,
[DBCLW] [nvarchar](20) NULL,
[PRINTCODE] [nvarchar](3) NULL,
[INV_HOLD] [nvarchar](1) NULL,
[CURRATE] [decimal](18, 4) NULL,
[CURCODE] [nvarchar](3) NULL,
[DEPOS] [smalldatetime] NULL,
[ROUTE] [nvarchar](5) NULL,
[DEL_METHOD] [nvarchar](1) NULL,
[DROPNO] [int] NULL,
[SEATNO] [int] NULL,
[DDATE] [smalldatetime] NULL,
[COMM] [nvarchar](20) NULL,
[ROT] [nvarchar](1) NULL,
[KNAPP] [nvarchar](1) NULL,
[OSEC] [nvarchar](1) NULL,
[OSECT] [nvarchar](3) NULL,
[UPDEXT] [nvarchar](1) NULL,
[EORDREFNO] [nvarchar](32) NULL,
[FSI] [nvarchar](1) NULL,
[MESSAGE_LINES] [nvarchar](500) NULL)

Este é o índice:

CREATE INDEX [Index_1] ON table_1 
([PRINT], [DEL_METHOD], [TYPE], [INV_NO], [DBINVHDID], [OSECT], [OSEC]) 
INCLUDE 
( [BUYACC], [CR_TERMS], [DISP1], [NAME], [ROUTE], [SEATNO], [STORE])

Esta é a consulta:

SELECT 
    [DBINVHDID],[STORE], [INV_NO], [DISP1],[SEATNO], [BUYACC],
    [NAME],[ROUTE], [CR_TERMS], [DEL_METHOD] 
FROM table_1 
WHERE 
    [PRINT] = 'N' 
    AND ([TYPE] = '3' OR [TYPE]='5') 
    AND [DEL_METHOD] = 'C' 
    AND [OSECT] <> 'KNP' 
    AND [OSEC] <> '1' 
ORDER BY [INV_NO] ASC

O plano de consulta:

insira a descrição da imagem aqui

O problema que tenho é que existe um predicado SEEK e um PREDICATE nas desigualdades:

insira a descrição da imagem aqui

Como posso recriar esse índice para que não haja predicado residual?

Inicialmente, o índice foi criado com as colunas OSECT e OSEC mais à esquerda na definição, o que causou avisos de E/S residuais e muitas leituras - então movi essas colunas para depois das colunas de igualdade que esclareceram os problemas de E/S.

O que estou tentando entender é como posso obter esse índice para satisfazer completamente a consulta sem um predicado residual?

A consulta pode ser reescrita?

Além disso, há um aviso de concessão de memória excessiva - SQL está alocando muita memória para esta consulta - STATS estão atualizadas com FULLSCAN

A concessão excessiva está relacionada ao predicado residual?

sql-server

2 respostas

  1. Best Answer

    Como posso recriar esse índice para que não haja predicado residual?

    Parece provável que você exigiria que a identidade fosse única. Isso precisa ser cumprido:

    CREATE UNIQUE CLUSTERED INDEX CX ON dbo.table_1 (DBINVHDID);

    Em seguida, crie um índice filtrado:

    CREATE INDEX [Index_1] 
ON table_1 ([PRINT], [DEL_METHOD], [TYPE], [INV_NO]) 
INCLUDE (
    [DBINVHDID], [OSECT], [OSEC], [STORE], [DISP1], [SEATNO], [BUYACC],
    [NAME], [ROUTE], [CR_TERMS])
WHERE 
    OSECT <> N'KNP'
    AND OSEC <> N'1';

    Expresse a consulta como:

    SELECT 
    U.[DBINVHDID],
    U.[STORE],
    U.[INV_NO],
    U.[DISP1],
    U.[SEATNO], 
    U.[BUYACC],
    U.[NAME],
    U.[ROUTE],
    U.[CR_TERMS],
    U.[DEL_METHOD] 
FROM 
(
    SELECT T1.*
    FROM dbo.table_1 AS T1
    WHERE
        T1.[PRINT] = N'N'
        AND T1.DEL_METHOD = N'C'
        AND T1.[TYPE] = N'3'

    UNION ALL

    SELECT T2.*
    FROM dbo.table_1 AS T2
    WHERE
        T2.[PRINT] = N'N'
        AND T2.DEL_METHOD = N'C'
        AND T2.[TYPE] = N'5'
) AS U
WHERE
    U.OSECT <> N'KNP'
    AND U.OSEC <> N'1'
ORDER BY
    U.INV_NO ASC;

    Isso fornecerá um plano de execução sem predicado residual e sem classificação:

    Planeje sem resíduo ou classificação

    Você pode omitir a parte de filtragem do índice se essas condições puderem variar em consultas diferentes. Os dois predicados de desigualdade OSECTe OSECserão aplicados como predicados residuais.

    A concessão excessiva está relacionada ao predicado residual?

    A concessão de memória é para a classificação. Estima-se que seja proporcional ao número e tamanho das linhas que requerem classificação. Os predicados (buscáveis ​​e residuais) afetam a estimativa de cardinalidade. Também parece que sua mesa está vazia. O SQL Server geralmente emite um aviso de 'memória excessiva' sem sentido nesse caso, mesmo que a reserva de memória seja pequena.

    Sem uma classificação, não há necessidade de memória.

    Você não deve criar tabelas no banco de dados mestre .

    Sua única tabela é um monte de dados desorganizados. Um design relacional com várias tabelas para representar as diferentes entidades provavelmente seria muito mais fácil de consultar. Você deve considerar um redesenho.

    Leitura relacionada:

    • 8

  2. Sua consulta tem condições sargáveis, como operadores <>, OR. Esses operadores têm um impacto negativo no custo da consulta. Para diminuir o custo da consulta, você pode remover a condição 'OR' dividindo sua consulta em 2 consultas como esta:

    SELECT 
    [DBINVHDID],[STORE], [INV_NO], [DISP1],[SEATNO], [BUYACC],
    [NAME],[ROUTE], [CR_TERMS], [DEL_METHOD] 
FROM table_1 
WHERE 
    [PRINT] = 'N' 
    AND ([TYPE] = '3') 
    AND [DEL_METHOD] = 'C' 
    AND [OSECT] <> 'KNP' 
    AND [OSEC] <> '1'
UNION 
SELECT 
    [DBINVHDID],[STORE], [INV_NO], [DISP1],[SEATNO], [BUYACC],
    [NAME],[ROUTE], [CR_TERMS], [DEL_METHOD] 
FROM table_1 
WHERE 
    [PRINT] = 'N' 
    AND ([TYPE]='5') 
    AND [DEL_METHOD] = 'C' 
    AND [OSECT] <> 'KNP' 
    AND [OSEC] <> '1' 
ORDER BY [INV_NO] ASC

    Para entender melhor, veja este link: https://medium.com/codex/sargable-predicates-and-null-values-in-sql-server-c43ec3d8b108

    • 1

relate perguntas