NB: esta questão é puramente acadêmica / para ajudar a melhorar minha compreensão do desempenho do SQL Server.
Dada uma tabela mestre relacionada a uma ou mais outras tabelas, como você determinaria a melhor abordagem para consultar essa tabela mestre em busca de registros, que incluem um indicador para a presença de registros nas tabelas relacionadas?
Por exemplo, digamos que temos uma tabela Pessoa e desejamos obter uma lista de todas as pessoas junto com um indicador de se elas têm filhos (neste exemplo, Pessoa pode ser reutilizada como a tabela relacionada):
create table Person
(
Id bigint not null constraint pk_Person primary key clustered
, ParentId bigint null constraint fk_Person_Parent foreign key references Person(Id)
, FirstName nvarchar(256) not null
, LastName nvarchar(256) not null
)
Poderíamos executar qualquer uma das consultas abaixo para verificar a presença de filhos relacionados:
--variables for restricting our result set, just to keep things interesting
declare @LastName nvarchar(256) = 'Be%'
, @FirstName nvarchar(256) = null
Exemplo 1
-- fairly straight forward, but requires grouping to account for the
-- potential of a parent having multiple kids (which I don't care about here)
-- which could be adding some inefficiency.
select parent.Id
, parent.FirstName
, parent.LastName
, case when max(child.Id) is null then 0 else 1 end HasChildren
from Person parent
left outer join Person child --1:n
on child.ParentId = parent.Id
where (@LastName is null or parent.LastName like @LastName)
and (@FirstName is null or parent.FirstName like @FirstName)
group by parent.Id, parent.FirstName, parent.LastName --resolve 1:n
exemplo 2
-- avoid the need to group the results by first getting
-- a single child per parent.
-- may be inefficient because we get children for all parents
-- even if we filter for only a few parents.
select parent.Id
, parent.FirstName
, parent.LastName
, coalesce(child.hasChildren, 0) HasChildren
from Person parent
left outer join --1:? (0 or 1)
(
select distinct parentId, 1 hasChildren
from Person
where parentId is not null --not sure if this adds value
) child
on child.ParentId = parent.Id
where (@LastName is null or LastName like @LastName)
and (@FirstName is null or FirstName like @FirstName)
--group by removed since we're 1:?
exemplo 3
-- same as #2 except we limit the child results to those
-- related to the parents we're interested in / having stored
-- them in a CTE to avoid querying for the same parent data
-- in the inner query and outer query.
-- Getting a bit silly now, but could overcome some inefficienies?
;with parentCTE as (
select Id, FirstName, LastName
from person
where (@LastName is null or LastName like @LastName)
and (@FirstName is null or FirstName like @FirstName)
)
select parentCTE.Id
, parentCTE.FirstName
, parentCTE.LastName
, coalesce(child.hasChildren, 0) HasChildren
from parentCTE
left outer join --1:? (0 or 1)
(
select distinct parentId, 1 hasChildren
from Person
where parentId in --reduce the amount of data we return here based on the records we're interested in
(
select Id
from parentCTE
)
) child
on child.ParentId = parentCTE.Id
exemplo 4
-- back to a simple one; just check for children on our parents
-- but this time having brought back the full parent set.
-- may be inefficient because we're querying the table once per
-- matching parent to check for children.
select parent.Id
, parent.FirstName
, parent.LastName
, coalesce((select top 1 1 from Person child where child.parentId = parent.Id),0) HasChildren
from Person parent
where (@LastName is null or LastName like @LastName)
and (@FirstName is null or FirstName like @FirstName)
Estou atrás de informações sobre como entender melhor as compensações envolvidas em tais situações, em vez de um simples example 3melhor. Indicações para artigos que possam me ajudar a entender também seriam bem-vindas.
SQL Fiddle relacionado: http://sqlfiddle.com/#!6/edc17/3
O exemplo 4 tem o menor número de varreduras e lê:
Exemplo 1
Tabela 'Pessoa'.
Contagem de varredura 9, leituras lógicas 27, leituras físicas 0,
Exemplo 2
Tabela 'Pessoa'.
Contagem de varredura 9, leituras lógicas 27, leituras físicas 0,
Exemplo 3
Tempo de análise e compilação do SQL Server: tempo de CPU = 7 ms, tempo decorrido = 7 ms.
Tempos de execução do SQL Server: tempo de CPU = 0 ms, tempo decorrido = 0 ms.
(8 linhas afetadas)
Tabela 'Pessoa'. Contagem de varredura 9, leituras lógicas 41, leituras físicas 0, r
(9 linhas afetadas)
Tempos de execução do SQL Server: tempo de CPU = 0 ms, tempo decorrido = 0 ms.
Exemplo 4
Tempo de análise e compilação do SQL Server: tempo de CPU = 3 ms, tempo decorrido = 3 ms.
Tempos de execução do SQL Server: tempo de CPU = 0 ms, tempo decorrido = 0 ms.
(8 linhas afetadas)
Tabela 'Pessoa'. Contagem de varredura 3, leituras lógicas 26, leituras físicas 0,
(11 linhas afetadas)
Tempos de execução do SQL Server: tempo de CPU = 0 ms, tempo decorrido = 0 ms.
Você quer o menor número possível de varreduras. Quanto mais vezes tivermos que escanear a tabela, mais tempo levará.