none
Performance de SELECT - Considerando data de vigência RRS feed

  • Pergunta

  • Pessoal,

    Considerando que em todos os cadastros do sistema em que trabalho tem datas de vigências, qual seria o método mais eficiente para realizar uma consulta como, por exemplo, trazer o código dos serviços e o código da tabela de preço vigente para uma determinada data?

    Para exemplificar execute o código abaixo em uma base de teste:

    Code Snippet

    IF(OBJECT_ID('TABPRECO') IS NOT NULL)

    DROP TABLE TABPRECO
    GO
    IF(OBJECT_ID('SERVICO') IS NOT NULL)
    DROP TABLE SERVICO
    GO


    CREATE TABLE SERVICO (
    SERV_ID INT NOT NULL PRIMARY KEY,
    SERV_NOME VARCHAR(16) NOT NULL
    )
    GO
    INSERT INTO SERVICO VALUES (1, 'Serv 01')
    INSERT INTO SERVICO VALUES (2, 'Serv 02')
    GO


    CREATE TABLE TABPRECO (
    TAPR_ID INT NOT NULL PRIMARY KEY,
    SERV_ID INT NOT NULL FOREIGN KEY REFERENCES SERVICO(SERV_ID),
    TAPR_DTVIG SMALLDATETIME NOT NULL,

    CONSTRAINT UNQ_SERVID_DTVIG UNIQUE (SERV_ID, TAPR_DTVIG)
    )
    GO
    INSERT INTO TABPRECO VALUES (1, 1, '2008-08-01')
    INSERT INTO TABPRECO VALUES (2, 1, '2008-09-01')
    INSERT INTO TABPRECO VALUES (3, 1, '2008-10-01')
    INSERT INTO TABPRECO VALUES (4, 2, '2008-08-15')
    INSERT INTO TABPRECO VALUES (5, 2, '2008-09-15')
    INSERT INTO TABPRECO VALUES (6, 2, '2008-10-15')


    -- Consulta 01
    SELECT TAPR.SERV_ID,
    TAPR.TAPR_ID
    FROM (
    SELECT SERV.SERV_ID,
    MAX(TAPR.TAPR_DTVIG) TAPR_DTVIG
    FROM SERVICO SERV
    JOIN TABPRECO TAPR
    ON TAPR.SERV_ID = SERV.SERV_ID
    WHERE TAPR.SERV_ID = SERV.SERV_ID
    AND TAPR.TAPR_DTVIG < GETDATE()
    GROUP BY SERV.SERV_ID
    ) TPSE
    JOIN TABPRECO TAPR
    ON TAPR.SERV_ID = TPSE.SERV_ID
    AND TAPR.TAPR_DTVIG = TPSE.TAPR_DTVIG

    -- Consulta 02
    SELECT SERV.SERV_ID,
    (
    SELECT TOP 1 TAPR.TAPR_ID
    FROM TABPRECO TAPR
    WHERE TAPR.SERV_ID = SERV.SERV_ID
    AND TAPR.TAPR_DTVIG < GETDATE()
    ORDER BY TAPR.TAPR_DTVIG DESC
    ) TAPR_ID

    FROM SERVICO SERV

     


    Ambos os SELECTs retornam os mesmos dados, mas o primeiro usa MAX e faz um segundo JOIN com a mesma tabela (TABPRECO) e o segundo usa SELECT no lugar dos campos a serem selecionados.

    Qual dessas consultas é considerada melhor em termo de desempenho?
    Existe alguma outra maneira mais eficiente para retornar os mesmos dados?
    JOIN com campos SMALLDATETIME ou DATETIME têm perda de performance?

    Obs.: Normalmente, a tabela de serviços é pequena, mas imagine que no lugar dela fosse uma tabela com milhares de registros.

    Eu procuro essa query para o SQL 2000.
    Só de curiosidade, para o 2005 seria apenas utilizar o "CROSS APPLY" com "SELECT TOP N"?

    Obrigado à todos!
    terça-feira, 23 de setembro de 2008 02:09

Respostas

  • Bom vamos lá, em um mundo perfeito onde o ID da tabela de preço é sempre crescente poderíamos fazer o seguinte select

     

    -- Consulta 03

    SELECT SERV.SERV_ID,

           TAPR.TAPR_ID

      FROM SERVICO SERV

     INNER JOIN (SELECT MAX(TAPR_ID) AS TAPR_ID,

                        SERV_ID

                   FROM TABPRECO

                  WHERE TAPR_DTVIG < CONVERT(SmallDateTime, GETDATE())

                  GROUP BY SERV_ID) AS TAPR

      ON SERV.SERV_ID = TAPR.SERV_ID

    GO

     

    Mas o que acontece é que posso perceber que nem sempre o MAX(TAPR_ID) será o último registro incluído, pode acontecer por exemplo de uma tabela de preço incluída anteriormente a última(max) sofra um update para uma data maior que a do último registro incluído, portanto creio que não poderemos usar o MAX ao invés de TOP 1 com ORDER BY, caso seu esteja errado, então a primeira coisa seria alterar seu código para a consulta acima, pois creio que em casos com muitos registros será a melhor opção.

     

    O MAX irá gerar um Stream Aggregate se comparado ao ORDER BY do TOP 1 o MAX será mais custoso em relação a uso de CPU, porém na maioria das vezes acaba sendo mais veloz, principalmente com grande carga de dados.

     

    A consulta 2 é a menos ruim em termo de desempenho, considerando que nenhuma é boa, pois para cada linha da tabela SERVICO o subSelect será executado, mesmo assim tem alguns pontos que podemos melhorar, detalhes que fazem uma grande diferença no final das contas...

     

    Em relação ao campo SmallDateTime, aqui ele é muito importante 1º ele vai economizar algumas páginas de dados na sua tabela TabPreco pois usa a metade do espaço do DateTime, isso significa que você terá que fazer menos reads para retornar seus dados, outra coisa é o seguinte,

     

    Ao comparar GetDate() com uma coluna SmallDateTime o SQL terá que converter o valor de GetDate para um SmallDateTime para poder fazer a comparação, o ideal seria que ao utilizar esse tipo de instrução você force a conversão do GetDate por ex:

     

    Veja a diferença dos planos de execução, entre as 2 consultas abaixo,

     

    SELECT TOP 1 TAPR.TAPR_ID

      FROM TABPRECO TAPR

     WHERE TAPR.SERV_ID = 1

       AND TAPR.TAPR_DTVIG < GETDATE()

     ORDER BY TAPR.TAPR_DTVIG DESC

     

    SELECT TOP 1 TAPR.TAPR_ID    FROM TABPRECO TAPR   WHERE TAPR.SERV_ID = 1     AND TAPR.TAPR_DTVIG < GETDATE()   ORDER BY TAPR.TAPR_DTVIG DESC

      |--Top(TOP EXPRESSIONSad(1)))

           |--Nested Loops(Inner Join, OUTER REFERENCESSad[Expr1004], [Expr1005], [Expr1003]))

                |--Compute Scalar(DEFINESad([Expr1004],[Expr1005],[Expr1003])=GetRangeWithMismatchedTypes(NULL,getdate(),(10))))

                |    |--Constant Scan

                |--Index Seek(OBJECTSad[tempdb].[dbo].[TABPRECO].[UNQ_SERVID_DTVIG] AS [TAPR]), SEEKSad[TAPR].[SERV_ID]=(1) AND [TAPR].[TAPR_DTVIG] > [Expr1004] AND [TAPR].[TAPR_DTVIG] < [Expr1005]) ORDERED BACKWARD)

     

    SELECT TOP 1 TAPR.TAPR_ID

      FROM TABPRECO TAPR

     WHERE TAPR.SERV_ID = 1

       AND TAPR.TAPR_DTVIG < CONVERT(SmallDateTime, GETDATE())

     ORDER BY TAPR.TAPR_DTVIG DESC

     

    SELECT TOP 1 TAPR.TAPR_ID    FROM TABPRECO TAPR   WHERE TAPR.SERV_ID = 1     AND TAPR.TAPR_DTVIG < CONVERT(SmallDateTime, GETDATE())   ORDER BY TAPR.TAPR_DTVIG DESC

      |--Top(TOP EXPRESSIONSad(1)))

           |--Index Seek(OBJECTSad[tempdb].[dbo].[TABPRECO].[UNQ_SERVID_DTVIG] AS [TAPR]), SEEKSad[TAPR].[SERV_ID]=(1) AND [TAPR].[TAPR_DTVIG] < CONVERT(smalldatetime,getdate(),0)) ORDERED BACKWARD)

     

    Portanto caso você crie uma variável para deixar a data dinâmica certifique-se de que a variável é do tipo SmallDateTime, ou seja, do mesmo tipo da coluna.

     

    Bom concluindo,

     

    Qual dessas consultas é considerada melhor em termo de desempenho?
    A 3 opção é a melhor, mas caso não seja possível utiliza-lá fique com a 2 porem com a alteração do Convert.

     

    Existe alguma outra maneira mais eficiente para retornar os mesmos dados?

    SQL 2000, Consulta 3, ou então consulta 4

    -- Consulta 4

    SELECT SERV_ID,

           MAX(TAPR.TAPR_ID) AS TAPR_ID

      FROM TABPRECO TAPR

     WHERE TAPR.TAPR_DTVIG < GETDATE()

     GROUP BY SERV_ID

     

    JOIN com campos SMALLDATETIME ou DATETIME têm perda de performance?
    Nenhuma perda só atenção a utilização de variáveis do mesmo tipo da coluna.

     

    terça-feira, 23 de setembro de 2008 12:43

Todas as Respostas

  • Boa Noite,

     

    Acredito que os colegas (em especial o Fabiano) tenham boas colocações a fazer sobre essa Thread, mas em todo caso, vou adiantando alguns pontos.

     

    Qual dessas consultas é considerada melhor em termo de desempenho?

    A julgar pelos dados do exemplo, basta uma análise do plano de execução para perceber que a primeira consulta é 50% mais custosa que a segunda. Acredito que essa relação se mantenha mesmo que a quantidade de registros aumente.


    Existe alguma outra maneira mais eficiente para retornar os mesmos dados?

    Não vejo muitas alternativas que não as utilizadas (ainda mais que você possui o SQL Server 2000). Se fosse no 2005 você poderia tentar utilizar o Row_Number com o Partition By. Quanto ao Cross Apply e o TOP N, só seria realmente necessário se você quisesse retornar mais de uma data por serviço mas é uma opção também.


    JOIN com campos SMALLDATETIME ou DATETIME têm perda de performance?

    Se devidamente indexados devem apresentar um bom desempenho. Campos SmallDatetime levam a vantagem de economizar espaço em relação ao Datetime, mas são tantas as variáveis que não acho que essa economia pudesse influenciar. Normalmente tipos de dados não trazem diferenças em atividades de JOINs. Só teríamos algo muito perceptivo se você utilizar tipos textuais com grandes quantidades de caractéres (nesse caso o desempenho é inferior).

     

    [ ]s,

     

    Gustavo

    terça-feira, 23 de setembro de 2008 02:41
  • Bom vamos lá, em um mundo perfeito onde o ID da tabela de preço é sempre crescente poderíamos fazer o seguinte select

     

    -- Consulta 03

    SELECT SERV.SERV_ID,

           TAPR.TAPR_ID

      FROM SERVICO SERV

     INNER JOIN (SELECT MAX(TAPR_ID) AS TAPR_ID,

                        SERV_ID

                   FROM TABPRECO

                  WHERE TAPR_DTVIG < CONVERT(SmallDateTime, GETDATE())

                  GROUP BY SERV_ID) AS TAPR

      ON SERV.SERV_ID = TAPR.SERV_ID

    GO

     

    Mas o que acontece é que posso perceber que nem sempre o MAX(TAPR_ID) será o último registro incluído, pode acontecer por exemplo de uma tabela de preço incluída anteriormente a última(max) sofra um update para uma data maior que a do último registro incluído, portanto creio que não poderemos usar o MAX ao invés de TOP 1 com ORDER BY, caso seu esteja errado, então a primeira coisa seria alterar seu código para a consulta acima, pois creio que em casos com muitos registros será a melhor opção.

     

    O MAX irá gerar um Stream Aggregate se comparado ao ORDER BY do TOP 1 o MAX será mais custoso em relação a uso de CPU, porém na maioria das vezes acaba sendo mais veloz, principalmente com grande carga de dados.

     

    A consulta 2 é a menos ruim em termo de desempenho, considerando que nenhuma é boa, pois para cada linha da tabela SERVICO o subSelect será executado, mesmo assim tem alguns pontos que podemos melhorar, detalhes que fazem uma grande diferença no final das contas...

     

    Em relação ao campo SmallDateTime, aqui ele é muito importante 1º ele vai economizar algumas páginas de dados na sua tabela TabPreco pois usa a metade do espaço do DateTime, isso significa que você terá que fazer menos reads para retornar seus dados, outra coisa é o seguinte,

     

    Ao comparar GetDate() com uma coluna SmallDateTime o SQL terá que converter o valor de GetDate para um SmallDateTime para poder fazer a comparação, o ideal seria que ao utilizar esse tipo de instrução você force a conversão do GetDate por ex:

     

    Veja a diferença dos planos de execução, entre as 2 consultas abaixo,

     

    SELECT TOP 1 TAPR.TAPR_ID

      FROM TABPRECO TAPR

     WHERE TAPR.SERV_ID = 1

       AND TAPR.TAPR_DTVIG < GETDATE()

     ORDER BY TAPR.TAPR_DTVIG DESC

     

    SELECT TOP 1 TAPR.TAPR_ID    FROM TABPRECO TAPR   WHERE TAPR.SERV_ID = 1     AND TAPR.TAPR_DTVIG < GETDATE()   ORDER BY TAPR.TAPR_DTVIG DESC

      |--Top(TOP EXPRESSIONSad(1)))

           |--Nested Loops(Inner Join, OUTER REFERENCESSad[Expr1004], [Expr1005], [Expr1003]))

                |--Compute Scalar(DEFINESad([Expr1004],[Expr1005],[Expr1003])=GetRangeWithMismatchedTypes(NULL,getdate(),(10))))

                |    |--Constant Scan

                |--Index Seek(OBJECTSad[tempdb].[dbo].[TABPRECO].[UNQ_SERVID_DTVIG] AS [TAPR]), SEEKSad[TAPR].[SERV_ID]=(1) AND [TAPR].[TAPR_DTVIG] > [Expr1004] AND [TAPR].[TAPR_DTVIG] < [Expr1005]) ORDERED BACKWARD)

     

    SELECT TOP 1 TAPR.TAPR_ID

      FROM TABPRECO TAPR

     WHERE TAPR.SERV_ID = 1

       AND TAPR.TAPR_DTVIG < CONVERT(SmallDateTime, GETDATE())

     ORDER BY TAPR.TAPR_DTVIG DESC

     

    SELECT TOP 1 TAPR.TAPR_ID    FROM TABPRECO TAPR   WHERE TAPR.SERV_ID = 1     AND TAPR.TAPR_DTVIG < CONVERT(SmallDateTime, GETDATE())   ORDER BY TAPR.TAPR_DTVIG DESC

      |--Top(TOP EXPRESSIONSad(1)))

           |--Index Seek(OBJECTSad[tempdb].[dbo].[TABPRECO].[UNQ_SERVID_DTVIG] AS [TAPR]), SEEKSad[TAPR].[SERV_ID]=(1) AND [TAPR].[TAPR_DTVIG] < CONVERT(smalldatetime,getdate(),0)) ORDERED BACKWARD)

     

    Portanto caso você crie uma variável para deixar a data dinâmica certifique-se de que a variável é do tipo SmallDateTime, ou seja, do mesmo tipo da coluna.

     

    Bom concluindo,

     

    Qual dessas consultas é considerada melhor em termo de desempenho?
    A 3 opção é a melhor, mas caso não seja possível utiliza-lá fique com a 2 porem com a alteração do Convert.

     

    Existe alguma outra maneira mais eficiente para retornar os mesmos dados?

    SQL 2000, Consulta 3, ou então consulta 4

    -- Consulta 4

    SELECT SERV_ID,

           MAX(TAPR.TAPR_ID) AS TAPR_ID

      FROM TABPRECO TAPR

     WHERE TAPR.TAPR_DTVIG < GETDATE()

     GROUP BY SERV_ID

     

    JOIN com campos SMALLDATETIME ou DATETIME têm perda de performance?
    Nenhuma perda só atenção a utilização de variáveis do mesmo tipo da coluna.

     

    terça-feira, 23 de setembro de 2008 12:43
  • Isso é o que eu chamo de uma verdadeira aula! rsrsrs

    Infelizmente o mundo não é perfeito mesmo, Fabiano. Eu gostaria de utilizar esse select (consulta 3), mas não é garantido que as tabelas estão realmente em ordem crescente. Uma forma seria colocar a coluna TAPR_ID como não identity e, ao cadastrar uma nova tabela de preço ou alterar a data de uma delas, fazer um UPDATE para (TAPR_ID + 1) para todas as outras que possuirem data maior do que a cadastrada/alterada para garantir que os códigos cresçam junto com a data da tabela (ordenado por data e código). Mas como o sistema já está em produção e não funciona desta forma, terei que optar pela segunda então, com as modificações mencionadas.

    Gosto de conhecer esses pequenos detalhes que fazem grandes diferenças.

    Agradeço muito pela a ajuda de vocês!
    terça-feira, 23 de setembro de 2008 18:33
  • Já estou para postar isso há um bom tempo, mas no serviço é bloqueado e em ksa eu não estava entrando no fórum. Antes tarde do que nunca... rsrsrs

    Para desencargo de consciência, inseri 1.000.000 de registros na tabela SERVICO e 3.000.000 de registros na tabela TABPRECO (3 para cada serviço)  para ver o tempo das consultas.


    Resultado das consultas COM conversão explícita para SMALLDATETIME:

      1 - 2266 ms
      2 - 93 ms
      3 - 610 ms



    Resultado das consultas SEM conversão explícita para SMALLDATETIME:

      1 - 2313 ms
      2 - 93 ms
      3 - 703 ms


    Obs.: Resultados obtidos pela média de tempo de 05 execuções para cada consulta.
    domingo, 5 de outubro de 2008 17:16
  • Interessante, acabou não dando tanta diferença, mas se você analisar no profiler verá que o uso de CPU será menor com a consulta usando a conversão explicita, analise a coluna CPU,

    Eu que esses detalhes são importantes principalmente quando estamos falando de cursores e loops onde 1 passo a menos é sempre bem vindo...

    Abraço...
    segunda-feira, 6 de outubro de 2008 17:17