Início / dba / Perguntas / 21542
Accepted
Rachel
Asked: 2012-07-27 04:44:36 +0800 CST

Qual é a maneira mais eficiente de obter o mínimo de várias colunas no SQL Server 2005?

  • 772

Estou em uma situação em que quero obter o valor mínimo de 6 colunas.

Encontrei três maneiras até agora de fazer isso, mas tenho preocupações com o desempenho desses métodos e gostaria de saber qual seria melhor para o desempenho.

O primeiro método é usar uma instrução big case . Aqui está um exemplo com 3 colunas, baseado no exemplo do link acima. Minha declaração de caso seria muito mais longa, pois estarei olhando para 6 colunas.

Select Id,
       Case When Col1 <= Col2 And Col1 <= Col3 Then Col1
            When Col2 <= Col3 Then Col2 
            Else Col3
            End As TheMin
From   MyTable

A segunda opção é usar o UNIONoperador com várias instruções de seleção . Eu colocaria isso em uma UDF que aceita um parâmetro Id.

select Id, dbo.GetMinimumFromMyTable(Id)
from MyTable

e

select min(col)
from
(
    select col1 [col] from MyTable where Id = @id
    union all
    select col2 from MyTable where Id = @id
    union all
    select col3 from MyTable where Id = @id
) as t

E a 3ª opção que encontrei foi usar o operador UNPIVOT , que eu nem sabia que existia até agora

with cte (ID, Col1, Col2, Col3)
as
(
    select ID, Col1, Col2, Col3
    from TestTable
)
select cte.ID, Col1, Col2, Col3, TheMin from cte
join
(
    select
        ID, min(Amount) as TheMin
    from 
        cte 
        UNPIVOT (Amount for AmountCol in (Col1, Col2, Col3)) as unpvt
    group by ID
) as minValues
on cte.ID = minValues.ID

Devido ao tamanho da tabela e à frequência em que essa tabela é consultada e atualizada, estou preocupado com o impacto no desempenho que essas consultas teriam no banco de dados.

Esta consulta será realmente usada em uma junção a uma tabela com alguns milhões de registros, porém os registros retornados serão reduzidos para cerca de cem registros por vez. Ele será executado muitas vezes ao longo do dia, e as 6 colunas que estou consultando são atualizadas com frequência (elas contêm estatísticas diárias). Acho que não há índices nas 6 colunas que estou consultando.

Qual desses métodos é melhor para o desempenho ao tentar obter o mínimo de várias colunas? Ou existe outro método melhor que eu não conheço?

Estou usando o SQL Server 2005

Dados e resultados de amostra

Se meus dados contivessem registros como este:

Id Col1 Col2 Col3 Col4 Col5 Col6
1 3 4 0 2 1 5
2 2 6 10 5 7 9
3 1 1 2 3 4 5
4 9 5 4 6 8 9

O resultado final deve ser

Valor do código
1 0
2 2
3 1
4 4
sql-server sql-server-2005

7 respostas

  1. Best Answer

    Testei o desempenho de todos os 3 métodos e aqui está o que encontrei:

    • 1 registro: Nenhuma diferença perceptível
    • 10 registros: Nenhuma diferença perceptível
    • 1.000 registros: Nenhuma diferença perceptível
    • 10.000 registros: UNIONa subconsulta foi um pouco mais lenta. A CASE WHENconsulta é um pouco mais rápida que a UNPIVOTúnica.
    • 100.000 registros: UNIONa subconsulta é significativamente mais lenta, mas UNPIVOTa consulta se torna um pouco mais rápida que a CASE WHENconsulta
    • 500.000 registros: UNIONa subconsulta ainda é significativamente mais lenta, mas UNPIVOTse torna muito mais rápida que a CASE WHENconsulta

    Assim, os resultados finais parecem ser

    • Com conjuntos de discos menores, não parece haver diferença suficiente para importar. Use o que for mais fácil de ler e manter.

    • Quando você começa a entrar em conjuntos de registros maiores, a UNION ALLsubconsulta começa a ter um desempenho ruim em comparação com os outros dois métodos.

    • A CASEinstrução tem o melhor desempenho até um certo ponto (no meu caso, cerca de 100 mil linhas), e em qual ponto a UNPIVOTconsulta se torna a consulta de melhor desempenho

    O número real em que uma consulta se torna melhor que outra provavelmente mudará como resultado de seu hardware, esquema de banco de dados, dados e carga atual do servidor, portanto, certifique-se de testar com seu próprio sistema se estiver preocupado com o desempenho.

    Também fiz alguns testes usando a resposta do Mikael ; no entanto, foi mais lento do que todos os 3 outros métodos tentados aqui para a maioria dos tamanhos de conjuntos de registros. A única exceção foi que foi melhor do que a UNION ALLconsulta para tamanhos de conjuntos de registros muito grandes. Eu gosto do fato de mostrar o nome da coluna além do menor valor.

    Não sou dba, então posso não ter otimizado meus testes e ter perdido alguma coisa. Eu estava testando com os dados reais ao vivo, então isso pode ter afetado os resultados. Tentei explicar isso executando cada consulta algumas vezes diferentes, mas nunca se sabe. Eu definitivamente estaria interessado se alguém escrevesse um teste limpo disso e compartilhasse seus resultados.

    • 22

  2. Não sei o que é mais rápido, mas você pode tentar algo assim.

    declare @T table
(
  Col1 int,
  Col2 int,
  Col3 int,
  Col4 int,
  Col5 int,
  Col6 int
)

insert into @T values(1, 2, 3, 4, 5, 6)
insert into @T values(2, 3, 1, 4, 5, 6)

select T4.ColName, T4.ColValue
from @T as T1
  cross apply (
                select T3.ColValue, T3.ColName
                from (
                       select row_number() over(order by T2.ColValue) as rn,
                              T2.ColValue,
                              T2.ColName
                       from (
                              select T1.Col1, 'Col1' union all
                              select T1.Col2, 'Col2' union all
                              select T1.Col3, 'Col3' union all
                              select T1.Col4, 'Col4' union all
                              select T1.Col5, 'Col5' union all
                              select T1.Col6, 'Col6'
                            ) as T2(ColValue, ColName)
                     ) as T3
                where T3.rn = 1
              ) as T4

    Resultado:

    ColName ColValue
------- -----------
Col1    1
Col3    1

    Se você não estiver interessado em qual coluna tem o valor mínimo, você pode usar isso.

    declare @T table
(
  Id int,
  Col1 int,
  Col2 int,
  Col3 int,
  Col4 int,
  Col5 int,
  Col6 int
)

insert into @T
select 1,        3,       4,       0,       2,       1,       5 union all
select 2,        2,       6,      10,       5,       7,       9 union all
select 3,        1,       1,       2,       3,       4,       5 union all
select 4,        9,       5,       4,       6,       8,       9

select T.Id, (select min(T1.ColValue)
              from (
                      select T.Col1 union all
                      select T.Col2 union all
                      select T.Col3 union all
                      select T.Col4 union all
                      select T.Col5 union all
                      select T.Col6
                    ) as T1(ColValue)
             ) as ColValue
from @T as T

    Uma consulta não dinâmica simplificada.

    select Id, min(ColValue) as ColValue
from @T
unpivot (ColValue for Col in (Col1, Col2, Col3, Col4, Col5, Col6)) as U
group by Id
    • 6

  3. Adicione uma coluna computada persistente que usa uma CASEinstrução para fazer a lógica necessária.

    O valor mínimo estará sempre disponível de forma eficiente quando você precisar fazer uma junção (ou qualquer outra coisa) com base nesse valor.

    O valor será recalculado toda vez que qualquer um dos valores de origem for alterado ( INSERT/ UPDATE/ MERGE). Não estou dizendo que essa é necessariamente a melhor solução para a carga de trabalho, apenas a ofereço como uma solução, assim como as outras respostas. Somente o OP pode determinar qual é o melhor para a carga de trabalho.

    • 6

  4. Sua casedeclaração não é eficiente. Você está fazendo 5 comparações no pior caso e 2 no melhor caso; ao passo que encontrar o mínimo de ndeve fazer no máximo n-1comparações.

    Para cada linha, em média, você está fazendo 3,5 comparações em vez de 2. Assim, leva mais tempo de CPU e é lento. Tente seus testes novamente usando a caseinstrução abaixo. Ele está usando apenas 2 comparações por linha e deve ser mais eficiente que unpivote union all.

    Select Id, 
       Case 
           When Col1 <= Col2 then case when Col1 <= Col3 Then Col1  else col3 end
            When  Col2 <= Col3 Then Col2  
            Else Col3 
            End As TheMin 
From   YourTableNameHere

    O union allmétodo está errado no seu caso, pois você está obtendo o valor mínimo não por linha, mas por toda a tabela. Além disso, não será eficiente, pois você fará a varredura da mesma tabela 3 vezes. Quando a tabela é pequena, a E/S não fará muita diferença, mas para tabelas grandes fará. Não use esse método.

    Unpivoté bom e tente desarticular manualmente também usando a junção cruzada da sua tabela com (select 1 union all select 2 union all select 3). Deve ser tão eficiente quanto o unpivot.

    A melhor solução seria ter uma coluna persistente computada, se você não tiver problemas de espaço. Ele aumentará o tamanho da linha em 4 bytes (suponho que você terá inttipo), o que, por sua vez, aumentará o tamanho da tabela.

    No entanto, o espaço e a memória são problemas em seu sistema e a CPU não é, então, não persista, mas use uma coluna computada simples usando a instrução case. Isso tornará o código mais simples.

    • 1

  5. Declaração de caso para 6 datas. Para fazer menos, copie a ramificação verdadeira da primeira instrução case. O pior caso é quando Date1 é o valor mais baixo, o melhor caso é quando Date6 é o valor mais baixo, então coloque a data mais provável em Date6. Eu escrevi isso por causa das limitações das colunas computadas.

    CASE WHEN Date1 IS NULL OR Date1 > Date2 THEN
        CASE WHEN Date2 IS NULL OR Date2 > Date3 THEN
            CASE WHEN Date3 IS NULL OR Date3 > Date4 THEN
                CASE WHEN Date4 IS NULL OR Date4 > Date5 THEN
                    CASE WHEN Date5 IS NULL OR Date5 > Date6 THEN
                        Date6
                    ELSE
                        Date5
                    END
                ELSE
                    CASE WHEN Date4 IS NULL OR Date4 > Date6 THEN
                        Date6
                    ELSE
                        Date4
                    END
                END
            ELSE
                CASE WHEN Date3 IS NULL OR Date3 > Date5 THEN
                    CASE WHEN Date5 IS NULL OR Date5 > Date6 THEN
                        Date6
                    ELSE
                        Date5
                    END
                ELSE
                    CASE WHEN Date3 IS NULL OR Date3 > Date6 THEN
                        Date6
                    ELSE
                        Date3
                    END
                END
            END
        ELSE
            CASE WHEN Date2 IS NULL OR Date2 > Date4 THEN
                CASE WHEN Date4 IS NULL OR Date4 > Date5 THEN
                    CASE WHEN Date5 IS NULL OR Date5 > Date6 THEN
                        Date6
                    ELSE
                        Date5
                    END
                ELSE
                    CASE WHEN Date4 IS NULL OR Date4 > Date5 THEN
                        CASE WHEN Date5 IS NULL OR Date5 > Date6 THEN
                            Date6
                        ELSE
                            Date5
                        END
                    ELSE
                        CASE WHEN Date4 IS NULL OR Date4 > Date6 THEN
                            Date6
                        ELSE
                            Date4
                        END
                    END
                END
            ELSE
                CASE WHEN Date2 IS NULL OR Date2 > Date5 THEN
                    CASE WHEN Date5 IS NULL OR Date5 > Date6 THEN
                        Date6
                    ELSE
                        Date5
                    END
                ELSE
                    CASE WHEN Date2 IS NULL OR Date2 > Date6 THEN
                        Date6
                    ELSE
                        Date2
                    END
                END
            END
        END
ELSE
    CASE WHEN Date1 IS NULL OR Date1 > Date3 THEN
        CASE WHEN Date3 IS NULL OR Date3 > Date4 THEN
            CASE WHEN Date4 IS NULL OR Date4 > Date5 THEN
                CASE WHEN Date5 IS NULL OR Date5 > Date6 THEN
                    Date6
                ELSE
                    Date5
                END
            ELSE
                CASE WHEN Date4 IS NULL OR Date4 > Date6 THEN
                    Date6
                ELSE
                    Date4
                END
            END
        ELSE
            CASE WHEN Date3 IS NULL OR Date3 > Date5 THEN
                CASE WHEN Date5 IS NULL OR Date5 > Date6 THEN
                    Date6
                ELSE
                    Date5
                END
            ELSE
                CASE WHEN Date3 IS NULL OR Date3 > Date6 THEN
                    Date6
                ELSE
                    Date3
                END
            END
        END
    ELSE
        CASE WHEN Date1 IS NULL OR Date1 > Date4 THEN
            CASE WHEN Date4 IS NULL OR Date4 > Date5 THEN
                CASE WHEN Date5 IS NULL OR Date5 > Date6 THEN
                    Date6
                ELSE
                    Date5
                END
            ELSE
                CASE WHEN Date4 IS NULL OR Date4 > Date6 THEN
                    Date6
                ELSE
                    Date4
                END
            END
        ELSE
            CASE WHEN Date1 IS NULL OR Date1 > Date5 THEN
                CASE WHEN Date5 IS NULL OR Date5 > Date6 THEN
                    Date6
                ELSE
                    Date5
                END
            ELSE
                CASE WHEN Date1 IS NULL OR Date1 > Date6 THEN
                    Date6
                ELSE
                    Date1
                END
            END
        END
    END
END

    Se você se deparou com esta página simplesmente procurando comparar datas e não está tão preocupado com desempenho ou compatibilidade, você pode usar um construtor de valor de tabela, que pode ser usado sempre que subseleções são permitidas (SQL Server 2008 e superior):

    Lowest =    
(
    SELECT MIN(TVC.d) 
    FROM 
    (
        VALUES
            (Date1), 
            (Date2), 
            (Date3), 
            (Date4), 
            (Date5), 
            (Date6)
    ) 
    AS TVC(d)
)
    • 1

  6. Eu acho que a primeira opção é a mais rápida (embora não pareça muito boa do ponto de vista da programação!). Isso ocorre porque ele lida com exatamente N linhas (onde N é o tamanho da tabela) e não precisa pesquisar ou classificar como o método 2 ou 3.

    Um teste com amostra grande deve provar o ponto.

    Outra opção a ser considerada (como se você precisasse de mais!), é criar uma visão materializada sobre sua mesa. se o tamanho da sua tabela estiver em centenas de milhares ou mais. Dessa forma, o valor mínimo é calculado enquanto a linha é alterada e a tabela inteira não precisaria ser processada com cada consulta. No SQL Server, as visualizações materializadas são chamadas de visualizações indexadas

    • -1 
  7. Create table #temp
   (
    id int identity(1,1),
    Name varchar(30),
    Year1 int,
    Year2 int,
    Year3 int,
    Year4 int
   )

   Insert into #temp values ('A' ,2015,2016,2014,2010)
   Insert into #temp values ('B' ,2016,2013,2017,2018)
   Insert into #temp values ('C' ,2010,2016,2014,2017)
   Insert into #temp values ('D' ,2017,2016,2014,2015)
   Insert into #temp values ('E' ,2016,2016,2016,2016)
   Insert into #temp values ('F' ,2016,2017,2018,2019)
   Insert into #temp values ('G' ,2016,2017,2020,2019)

   Select *, Case 
                 when Year1 >= Year2 and Year1 >= Year3 and Year1 >= Year4 then Year1
                 when Year2 >= Year3 and Year2 >= Year4 and Year2 >= Year1 then Year2
                 when Year3 >= Year4 and Year3 >= Year1 and Year3 >= Year2 then Year3
                 when Year4 >= Year1 and Year4 >= Year2 and Year4 >= Year3 then Year4  
                 else Year1 end as maxscore  
                 from #temp
    • -1

relate perguntas