Início / dba / Perguntas / 242611
Accepted
vikrant rana
Asked: 2019-07-11 23:29:21 +0800 CST

melhorando o desempenho de junção na tabela do sql server

  • 772

Eu tenho uma tabela no servidor sql digamos TableA. e uma outra tabela como TableB. Ambas as tabelas estão muito carregadas.

A Tabela A é carregada com os registros 35883788. Ela contém muitas duplicatas na coluna program_id.

A Tabela B é carregada com registros 27343331. Ela também contém uma duplicata na coluna em program_id. Por algum motivo comercial, precisamos manter registros duplicados em program_id em ambas as tabelas.

Agora estou realizando uma junção à esquerda em determinada tabela como:

select 
a.*,b.date_of_birth,
datediff(year,b.date_of_birth,a.from_date)-
(case when dateadd(year,
           datediff(year,b.date_of_birth,a.from_date),b.date_of_birth)
           > a.from_date then 1 else 0
                   end) as age_final,
case when datediff(yyyy,b.date_of_birth,a.from_date) is null then ''
     when datediff(yyyy,b.date_of_birth,a.from_date) <=1 then 'less than 1 year'
     when datediff(yyyy,b.date_of_birth,a.from_date) <=17 then 'then 1 - 17 year'
     when datediff(yyyy,b.date_of_birth,a.from_date) <=29 then 'then 19 - 29 year'
     when datediff(yyyy,b.date_of_birth,a.from_date) <=39 then 'then 30 - 39 year'
     when datediff(yyyy,b.date_of_birth,a.from_date) <=49 then 'then 40 - 49 year'
     when datediff(yyyy,b.date_of_birth,a.from_date) <=59 then 'then 50 - 59 year'
     when datediff(yyyy,b.date_of_birth,a.from_date) <=64 then 'then 60 - 64 year'
else 'More than 64 years' end as Age_band
from TableA a 
left join ( select program_id,date_of_birth,max(process_date) 
            from TableB 
            group by program_id,date_of_birth) b
    on a.program_id=b.program_id;

minha consulta interna está me dando um conjunto exclusivo de program_id usando group by statement.

Nenhuma dessas tabelas está indexada. Se eu tiver que criar um índice em ambas as tabelas, que tipo de índice eu preciso ter no program_id para ambas as tabelas.

o que todas as outras coisas eu posso cuidar para melhorar o desempenho da junção.

preciso de sugestão para otimizar a junção e os índices.

sql-server ssis-2016

1 respostas

  1. Best Answer

    DDL e DML na parte inferior

    Índices padrão que podem ser criados com base nas informações fornecidas

    CREATE NONCLUSTERED INDEX IX_program_id_date_of_birth_process_date
ON dbo.TableB (program_id,date_of_birth,process_date);
CREATE NONCLUSTERED INDEX IX_TableA
ON dbo.TableA(program_id);

    Executando a consulta

    SET STATISTICS IO, TIME ON;
select 
a.*,b.date_of_birth
from dbo.TableA a 
left join ( select program_id,date_of_birth,max(process_date)  as maxprocess_date
            from dbo.TableB 
            group by program_id,date_of_birth) b
    on a.program_id=b.program_id;

    insira a descrição da imagem aqui

    O índice NC on TableAé usado, pois IDestá incluído no índice NC e nenhuma outra coluna está, TableAexceto a program_idcoluna, que é uma coluna chave desse índice.

    Mas , como você está usando select a.*, provavelmente terá que adicionar todas essas colunas ao índice NC TableAcomo colunas incluídas, o que aumentará o tamanho do seu índice dependendo da quantidade de colunas na tabela.

    Se adicionarmos uma varchar(3)coluna e a preenchermos com um valor:

    ALTER TABLE dbo.TableA ADD bla varchar(3) 
DEFAULT ('bla')
WITH VALUES;

    O índice não é mais usado:

    insira a descrição da imagem aqui

    Por causa da blacoluna adicional adicionada:

    insira a descrição da imagem aqui

    Para corrigir isso , recrie o índice com a blacoluna incluída.

    DROP  INDEX IX_TableA ON dbo.TableA;
CREATE NONCLUSTERED INDEX IX_TableA
ON dbo.TableA(program_id)
INCLUDE(bla);

    Adicionalmente

    Nenhuma dessas tabelas está indexada.

    As tabelas de heap não são ideais, você deve considerar adicionar um índice clusterizado que faça sentido e ler sobre a indexação.

    Mais sobre a diferença entre tabelas de heap e tabelas com um índice clusterizado aqui

    Selecione uma.*

    Usar Select *não é uma boa prática, considere nomear todas as colunas usadas em sua instrução select separadamente.

    Maus hábitos para chutar: usando SELECT *

    DDL + DML usado para teste

    CREATE TABLE 
dbo.TableA(ID INT IDENTITY(1,1) PRIMARY KEY,
           program_id INT);
CREATE TABLE 
dbo.TableB(ID INT IDENTITY(1,1) PRIMARY KEY,
           program_id INT,
           date_of_birth date,
           process_date datetime2)
-- Dataset reduced by / 10 ~= 3 588 378

INSERT INTO 
dbo.TableA WITH(TABLOCK)  (program_id)
SELECT TOP(3588378) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) / 1000
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2;
-- values from 0 to 3588

-- Dataset reduced by / 10 ~= 2 734 333
INSERT INTO 
dbo.TableB WITH(TABLOCK) (program_id,date_of_birth,process_date) 
SELECT TOP(2734333) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) / 1000,
CAST('01/01/1990' AS date),
DATEADD(MINUTE,-ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),GETDATE()) -- one minute less to get distinct process_dates
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2;
-- values from 0 to 2734
    • 1

relate perguntas