Início / dba / Perguntas / 249820
Accepted
wp78de
Asked: 2019-09-28 10:25:56 +0800 CST

Otimização de desempenho: alternativa para LEFT JOIN na mesma tabela duas vezes

  • 772

Gostaria de estender a seguinte consulta com o LEFT JOIN comentado. No entanto, essas linhas tornam a consulta visivelmente mais lenta.
A consulta é usada por uma API para pesquisar IDs de usuário por número de telefone e as coisas precisam ser rápidas. Então, existe uma alternativa mais eficiente para juntar a mesma tabela duas vezes com cláusulas ON diferentes?

set statistics time on 

SELECT
    ivr.ID
FROM ivr
LEFT OUTER JOIN ADDR_MASTER am1
    ON am1.ID = ivr.ID
        AND am1.ADDR_TYPE = 'WORK' --WORK PHONE (only adds 35ms to lookup time)
--LEFT OUTER JOIN ADDR_MASTER am2
--  ON am2.ID = ivr.ID
--      AND am2.ADDR_TYPE = 'HOME' --HOME PHONE (fallback if not in IVR, adds 100+ ms :(
WHERE (PHONE1 = '0123456789'
OR PHONE2 = '0123456789'
OR PHONE3 = '0123456789'
OR am1.PHONE = '0123456789'
--OR am2.PHONE = '0123456789'
)

set statistics time off

Aqui estão os planos:

sql-server sql-server-2016

2 respostas

  1. Best Answer

    Eu acho que isso vai fazer o que você está pedindo.

    SELECT
    ivr.ID
FROM ivr
LEFT OUTER JOIN ADDR_MASTER am1
    ON am1.ID = ivr.ID
        AND am1.ADDR_TYPE IN ('WORK','HOME')
WHERE (PHONE1 = '0123456789'
OR PHONE2 = '0123456789'
OR PHONE3 = '0123456789'
OR am1.PHONE = '0123456789'
)

    Dito isto, há algumas coisas que você pode fazer diferente.

    Isso pode ser melhor, não tenho certeza, mas desde que não seja mais lento, provavelmente é mais fácil de manter.

    SELECT
    ivr.ID
FROM ivr
LEFT OUTER JOIN ADDR_MASTER am1
    ON am1.ID = ivr.ID
        AND am1.ADDR_TYPE IN ('WORK','HOME')
WHERE '0123456789' IN (PHONE1, PHONE2, PHONE3, am1.PHONE)

    Agora, supondo que você de fato precise apenas do ivr.ID (pelo menos nada de ADDR_MASTER), você pode fazer isso:

    SELECT
    ivr.ID
FROM ivr
WHERE '0123456789' IN (PHONE1, PHONE2, PHONE3)
    OR EXISTS ( SELECT 1 FROM ADDR_MASTER am1
                WHERE am1.ID = ivr.ID
                    AND am1.ADDR_TYPE IN ('WORK','HOME')
                    AND am1.PHONE = '0123456789' )

    Eu tentaria várias combinações para ver quais delas são realmente mais rápidas.

    • 2

  2. Refatorar a tabela seria mais fácil. Os números de telefone devem estar em uma tabela separada e relacionada, não na tabela de endereços (como mostrado pela necessidade de vários campos de telefone), algo assim (simplificado como exemplo - sinalizadores para máquinas de FAX, celular, Peferred #, sempre uma resposta máquina, sem chamadas após as 18h, etc também podem ser necessárias):

    CREATE TABLE PhoneNumbers (
    PhoneId bigint IDENTITY(1,1),
    AddrId bigint NOT NULL,
    PhoneNo varchar(20) NOT NULL,
    -- PK assumes this will most often pull up phone numbers for a given address
    CONSTRAINT PK_PhoneNumbers_AddrID_PhoneID (AddrId, PhoneId) 
    )

CREATE UNIQUE INDEX Idx_PhoneNumbers_PhoneNo_INCLUDE
ON PhoneNumbers (PhoneNo) INCLUDE (AddrId) -- Look up Addr by Phone # index

    Quando você quiser todos os telefones associados a um endereço, acesse AddrId. Quando você quiser todos os endereços associados a um número de telefone, entre por número de telefone. E o número de telefones associados a um endereço é efetivamente ilimitado, em vez de limitado ao número de colunas designadas para essa finalidade.

    • 1

relate perguntas