Início / dba / Perguntas / 232698
Accepted
Learning_DBAdmin
Asked: 2019-03-22 01:11:23 +0800 CST

Melhoria de desempenho deu errado na produção, funcionou bem no teste

  • 772

Temos um aplicativo suportado pelo fornecedor e havia um pedaço de código que estava fazendo leituras lógicas muito pesadas e consumindo tempo dentro de um procedimento, por isso propus a eles ajustar um pouco a consulta para ter muito menos IO e tempo, funcionou bem em teste em termos de dados e desempenho, porém está falhando na produção e retornando dados diferentes, tivemos que reverter as alterações. Precisa do seu conselho especializado sobre isso.

Isso foi testado para dados no ambiente de teste por mais de 3 meses e nunca tivemos nenhum problema de dados. Começou a falhar com moderação na Produção imediatamente após a implantação e estava produzindo dados inconsistentes.

Consulta existente:

SELECT @Ref= CAST(MAX(ISNULL(CAST(ref_clnt AS INT),0))+1 AS VARCHAR(10)) 
FROM table_name WITH(NOLOCK) 
WHERE s_mode='value'

Consulta proposta:

SELECT @Ref = ref_clnt+1 FROM table_name WITH(NOLOCK) 
WHERE RefNo = (SELECT MAX(RefNo) FROM table_name WHERE s_mode = 'value')

O DDL da tabela é o seguinte:

CREATE TABLE [dbo].[table_name](
    [RefNo] [dbo].[udt_RefNo] NOT NULL,
    [S_Mode] [varchar](10) NOT NULL,
    [ref_clnt] [varchar](50) NULL)
CONSTRAINT [PK_table_name] PRIMARY KEY CLUSTERED 
(
    [RefNo] ASC
)

Fornecendo apenas as colunas da definição que são usadas na consulta.

Udt_RefNoé um tipo de dados definido pelo usuário como:

CREATE TYPE [dbo].[udt_RefNo] FROM [char](16) NOT NULL
GO

Versão do SQL Server: Microsoft SQL Server 2014 (SP3) Copyright (c) Microsoft Corporation Enterprise Edition (64 bits) .

Colunas de cobertura de índice não clusterizado, conforme mostrado abaixo:

CREATE NONCLUSTERED INDEX [ncidx_table_name_1] ON [dbo].[table_name]
(
    [S_Mode] ASC,
    [S_Status] ASC
)
INCLUDE (   [ref_clnt])

Encontre os planos de consulta conforme solicitado:

Plano de execução Comparação do número de leituras após habilitar o IO e o tempo de estatísticas:

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(1 row affected)
Table 'table_name'. Scan count 1, logical reads 2732, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row affected)

 SQL Server Execution Times:
   CPU time = 157 ms,  elapsed time = 161 ms.

(1 row affected)
Table 'table_name'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row affected)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

O fornecedor voltou com sugestão como

SELECT MAX(ISNULL(ref_clnt,0))+1 FROM table_name WITH(NOLOCK) WHERE S_Mode='value'

O problema é - como testá-los, pois todos eles parecem funcionar para a maior parte do cenário, mas falham apenas em alguns casos. Não estou muito ciente do aplicativo e de seu fornecedor com suporte, portanto, não consigo obter muitos detalhes, como como a lógica de negócios funciona para o procedimento subjacente.

sql-server sql-server-2014

2 respostas

  1. Best Answer

    Sua reescrita não tem a mesma semântica da consulta original, portanto, resultados diferentes não são surpreendentes.

    Isso é verdade sem se preocupar com a NOLOCKdica, e mesmo que seja (de alguma forma) garantido que o ref_clntvalor mais alto esteja associado à linha com o RefNovalor mais alto. Veja este exemplo de db<>fiddle .

    A precisão é importante ao lidar com computadores, portanto, você precisa pensar cuidadosamente sobre os tipos de dados e os casos extremos. O cálculo máximo RefNousará a semântica de classificação de string , portanto, '999' classifica acima de '1000'. Existem várias outras diferenças importantes entre as consultas. Não vou listar todos eles, mas NULLo manuseio é outro exemplo .

    Existem também vários bugs em ambas as versões do código. O original falhará se qualquer ref_clntvalor de -1000000000 ou inferior for retornado, porque isso não caberá em varchar(10). O sinal faz o comprimento 11.

    A maneira mais fácil de melhorar com segurança a versão original do código é adicionar um índice em uma coluna computada:

    ALTER TABLE dbo.table_name 
ADD ref_cc AS 
    ISNULL(CAST(ref_clnt AS integer), 0);

CREATE NONCLUSTERED INDEX i 
ON dbo.table_name (S_Mode, ref_cc);

    db<>demonstração de violino

    O plano de execução pode então buscar diretamente na ref_clntlinha mais alta (classificada como inteiro) para o S_Modevalor fornecido:

    plano de execução

    O SQL original do fornecedor ainda pode ser de qualidade discutível, mas pelo menos rodará mais rápido e produzirá os mesmos resultados.

    A nova sugestão do fornecedor:

    SELECT MAX(ISNULL(ref_clnt,0))+1 FROM table_name WITH(NOLOCK) WHERE S_Mode='value'

    ... ainda é problemático, pelo menos em teoria, porque ISNULLusa o tipo de dados do primeiro parâmetro, então o literal inteiro 0é convertido implicitamente em varchar(50).

    O valor de check_expression é retornado se não for NULL; caso contrário, o valor_substituição é retornado após ser convertido implicitamente no tipo de check_expression , se os tipos forem diferentes. valor_substituição pode ser truncado se valor_substituição for maior que check_expression .

    O MAXainda opera em uma string, o que pode produzir resultados inesperados . Em qualquer caso, a expressão ainda não é pesquisável sem um índice de coluna calculado (diferente).

    • 7

  2. Eu vou adivinhar que @Refé varchar(10). Ref_clnté varchar(50)e você está adicionando um a ele.

    SELECT @Ref = ref_clnt+1

    ...irá gerar uma conversão implícita.

    Esse problema apareceu nos planos de execução como um aviso e afetou o desempenho em nosso aplicativo. A maioria dos casos que vi foram quando a declaração coalesce foi usada assim:

    coalesce(field_name, 0) = 0

    quando deveria ser:

    coalesce(field_name, '0') = '0' or coalesce(field_name, '') = ''

    ... porque field_nameera varchar.

    • 0

relate perguntas