tenho as seguintes tabelas
Create Table dbo.product
(
productId varchar(100) primary key,
productStatus varchar(100),
productRegion varchar(100),
productCreated datetime,
productUpdated datetime
)
Go
declare @id int = 1
while @id <= 100
Begin
Insert Into dbo.product values ('product'+cast(@id as varchar(10)),'Active','North',getdate(),getdate())
set @id = @id + 1
End
set @id = 1
while @id <= 100
Begin
Insert Into dbo.product values ('inprod'+ cast(@id as varchar(10)),'InActive','South',getdate(),getdate())
set @id = @id + 1
End
Go
Create Table dbo.productRef
(
productRef int Identity(1,1) primary key,
productId varchar(100),
productName varchar(100)
)
Go
Insert Into dbo.productRef (productId)
Select top 20 productId
from dbo. product
Go
declare @id int = 1
while @id <= 20
Begin
update dbo.productRef
set productName = 'productName'+convert(varchar(10),@id)
where productRef = @id
set @id = @id + 1
End
Go
Create nonclustered index idx_productRef1 On dbo.productRef(productId)
Qual dessas seleções terá melhor desempenho?
select p.*
from dbo.product p
join dbo.productref pr
on p.productid = pr.productid
order by p.productUpdated
select p.productId
from dbo.product p
join dbo.productref pr
on p.productid = pr.productid
order by p.productUpdated
Aqui estão os planos de consulta para ambas as instruções select:
select * plan:
https://www.brentozar.com/pastetheplan/?id=SyY21P0Jo
select productId plan:
https://www.brentozar.com/pastetheplan/?id=BkK -gwA1i
Pelo que vejo ambos os planos são os mesmos. Existe uma diferença de desempenho ao fazer select * order by vs select column order by?
De fato, neste caso, você está obtendo os mesmos planos de consulta em forma. Isso nem sempre será o caso.
Sim, mesmo no seu caso com planos de consulta com o mesmo formato, há uma diferença de desempenho (embora talvez insignificante neste caso específico). Esqueça a
ORDER BY
cláusula por um segundo, e vamos falar sobreSELECT *
vsSELECT OneSingleColumn
, já que o seguinte é verdade independentemente:Selecionar mais colunas do que o necessário (neste caso, 7 colunas extras)
SELECT *
requer que mais dados sejam localizados, carregados do disco na memória, processados e transferidos pela rede para o consumidor. Em seu esquema, essas 7 colunas extras podem conter até cerca de meio KB extra de dados por linha. Em uma tabela de tamanho decente, digamos, com 100 milhões de linhas, seriam 50 GB extras de dados passando por todas as etapas mencionadas acima.Isso pode / resultará na alocação de mais recursos para atender à consulta para dar suporte a todas as etapas mencionadas acima para as colunas adicionais. Isso reduz os recursos que poderiam estar disponíveis para outras consultas executadas simultaneamente no servidor.
Isso pode resultar em planos de diferentes formas de várias maneiras diferentes. Uma das formas mais comuns é obter operações de varredura para a
SELECT *
versão da consulta em vez de buscas eficientes para aSELECT OneSingleColumn
versão, quando a consulta ultrapassa o ponto de inflexão . Ou outra maneira é quando um plano de consulta usa um índice completamente diferente do que seria o mais eficiente para usar no caso de você precisar apenas doOneSingleColumn
.Uma maneira pela qual a forma do plano pode variar (como apontado por maple_shaft) que afeta o desempenho é que, mesmo no melhor caso, obtendo um índice, procure o índice mais apropriado para atender à consulta, ele provavelmente não conterá todos os campos, ou seja
*
. Assim, você terá um operador de pesquisa de chave adicional , causando trabalho extra para pesquisar os campos restantes do índice clusterizado. Ou em um resultado menos do que o ideal, você acabará com o índice clusterizado sendo verificado para satisfazer a consulta, em vez de buscar um índice mais ideal que conteria apenas as colunas que você está interessado emSELECT
pesquisar (supondo que seus índices atendem às suas perguntas adequadamente). Quando você sóSELECT
as colunas que você precisa, e há um índice que cobre essas colunas, então o índice mais ideal pode ser usado para servir sua consulta sem que operações/trabalhos adicionais precisem ocorrer, e haverá uma boa chance de você obter uma operação de busca contra esse índice também, sendo bastante rápido.SELECT *
também é um antipadrão por vários motivos, não apenas relacionados ao desempenho, mas também à legibilidade e manutenção:A diferença neste caso pode ser vista no operador SORT. Tamanho da linha 27B vs 139B. Com 20 linhas e a diferença de tamanho, neste caso não é um problema. Para tabelas mais largas e mais linhas, podemos falar KB vs MBs ou até GB de dados dependendo.
Da experiência prática, houve uma diferença de classificação KB vs GB e diferença de desempenho real do ponto de vista do usuário.