Quando devo usar Cross aplicar sobre a junção interna?
Qual é o objectivo principal da utilização de CROSS APPLY ?
Eu li (vagamente, através de posts na Internet) que {[[1]} pode ser mais eficiente ao selecionar sobre grandes conjuntos de dados, se você estiver Particionando. (Vem-me à mente Paging)
Também sei que CROSS APPLY
não requer uma UDF como a mesa certa.
na maioria das consultas (relações de um para muitos), eu poderia reescrevê-las para usar {[[2]}, mas elas sempre me dão planos de execução equivalentes.
Alguém me pode dar um bom exemplo de quandoCROSS APPLY
faz diferença nos casos em que {3]} também funcionará?
Editar:
Aqui está um exemplo trivial, onde os planos de execução são exatamente os mesmos. (Mostre-me um onde eles diferem e ondecross apply
é mais rápido/mais eficiente)
create table Company (
companyId int identity(1,1)
, companyName varchar(100)
, zipcode varchar(10)
, constraint PK_Company primary key (companyId)
)
GO
create table Person (
personId int identity(1,1)
, personName varchar(100)
, companyId int
, constraint FK_Person_CompanyId foreign key (companyId) references dbo.Company(companyId)
, constraint PK_Person primary key (personId)
)
GO
insert Company
select 'ABC Company', '19808' union
select 'XYZ Company', '08534' union
select '123 Company', '10016'
insert Person
select 'Alan', 1 union
select 'Bobby', 1 union
select 'Chris', 1 union
select 'Xavier', 2 union
select 'Yoshi', 2 union
select 'Zambrano', 2 union
select 'Player 1', 3 union
select 'Player 2', 3 union
select 'Player 3', 3
/* using CROSS APPLY */
select *
from Person p
cross apply (
select *
from Company c
where p.companyid = c.companyId
) Czip
/* the equivalent query using INNER JOIN */
select *
from Person p
inner join Company c on p.companyid = c.companyId
13 answers
Alguém pode dar-me um bom exemplo de quando a cruz se aplica faz diferença nos casos em que a junção interna também funcionará?
Veja o artigo no meu blog para uma comparação detalhada de desempenho:
CROSS APPLY
funciona melhor em coisas que não têm uma condição simples.
Este selecciona 3
os últimos registos de t2
para cada registo de t1
:
SELECT t1.*, t2o.*
FROM t1
CROSS APPLY
(
SELECT TOP 3 *
FROM t2
WHERE t2.t1_id = t1.id
ORDER BY
t2.rank DESC
) t2o
Não pode ser. facilmente formulado com uma condição INNER JOIN
.
Você provavelmente poderia fazer algo assim usando CTE
' S e função da janela:
WITH t2o AS
(
SELECT t2.*, ROW_NUMBER() OVER (PARTITION BY t1_id ORDER BY rank) AS rn
FROM t2
)
SELECT t1.*, t2o.*
FROM t1
INNER JOIN
t2o
ON t2o.t1_id = t1.id
AND t2o.rn <= 3
Mas isto é menos legível e provavelmente menos eficiente.
Actualizar:
Acabei de verificar.master
é uma tabela de cerca de 20,000,000
registros com um PRIMARY KEY
em id
.
Esta consulta:
WITH q AS
(
SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS rn
FROM master
),
t AS
(
SELECT 1 AS id
UNION ALL
SELECT 2
)
SELECT *
FROM t
JOIN q
ON q.rn <= t.id
Corre por quase 30
segundos, enquanto este:
WITH t AS
(
SELECT 1 AS id
UNION ALL
SELECT 2
)
SELECT *
FROM t
CROSS APPLY
(
SELECT TOP (t.id) m.*
FROM master m
ORDER BY
id
) q
É instantâneo.
cross apply
Às vezes permite-nos fazer coisas que não podemos fazer com inner join
.
Exemplo (um erro de sintaxe):
select F.* from sys.objects O
inner join dbo.myTableFun(O.name) F
on F.schema_id= O.schema_id
Este é um erro de sintaxe , Porque, quando usado com inner join
, as funções da tabela só podem tomar variáveis ou constantes como parâmetros. (I. e., O parâmetro da função da tabela não pode depender da coluna de outra tabela.)
No entanto:
select F.* from sys.objects O
cross apply ( select * from dbo.myTableFun(O.name) ) F
where F.schema_id= O.schema_id
Isto é legal.
Editar: Ou, em alternativa, uma sintaxe mais Curta: (por ErikE)
select F.* from sys.objects O
cross apply dbo.myTableFun(O.name) F
where F.schema_id= O.schema_id
Editar:
Nota: Informix 12. 10 xC2+ has tabelas derivadas laterais e Postgresql (9.3+) tem Subqueries laterais que podem ser usadas para um efeito semelhante.
TABELA PRINCIPAL
x------x--------------------x
| Id | Name |
x------x--------------------x
| 1 | A |
| 2 | B |
| 3 | C |
x------x--------------------x
TABELA DE DETALHES
x------x--------------------x-------x
| Id | PERIOD | QTY |
x------x--------------------x-------x
| 1 | 2014-01-13 | 10 |
| 1 | 2014-01-11 | 15 |
| 1 | 2014-01-12 | 20 |
| 2 | 2014-01-06 | 30 |
| 2 | 2014-01-08 | 40 |
x------x--------------------x-------x
Há muitas situações em que precisamos de substituir INNER JOIN
por CROSS APPLY
.
1. Juntar duas tabelas com base nos resultados TOP n
Considere se precisamos selecionar Id
e Name
de Master
e as duas últimas datas para cada Id
de Details table
.
SELECT M.ID,M.NAME,D.PERIOD,D.QTY
FROM MASTER M
INNER JOIN
(
SELECT TOP 2 ID, PERIOD,QTY
FROM DETAILS D
ORDER BY CAST(PERIOD AS DATE)DESC
)D
ON M.ID=D.ID
A a consulta acima gera o seguinte resultado.
x------x---------x--------------x-------x
| Id | Name | PERIOD | QTY |
x------x---------x--------------x-------x
| 1 | A | 2014-01-13 | 10 |
| 1 | A | 2014-01-12 | 20 |
x------x---------x--------------x-------x
Veja, ele gerou resultados para as duas últimas datas com as duas últimas datas Id
e depois juntou estes registros apenas na consulta externa em Id
, o que está errado. Para conseguir isso, precisamos usar CROSS APPLY
.
SELECT M.ID,M.NAME,D.PERIOD,D.QTY
FROM MASTER M
CROSS APPLY
(
SELECT TOP 2 ID, PERIOD,QTY
FROM DETAILS D
WHERE M.ID=D.ID
ORDER BY CAST(PERIOD AS DATE)DESC
)D
E forma o seguinte resultado.
x------x---------x--------------x-------x
| Id | Name | PERIOD | QTY |
x------x---------x--------------x-------x
| 1 | A | 2014-01-13 | 10 |
| 1 | A | 2014-01-12 | 20 |
| 2 | B | 2014-01-08 | 40 |
| 2 | B | 2014-01-06 | 30 |
x------x---------x--------------x-------x
É assim que funciona. A consulta dentro CROSS APPLY
pode referenciar a tabela externa, onde INNER JOIN
não pode fazer isto (ele lança erro de compilação). Ao encontrar as duas últimas datas, A União é feita dentro CROSS APPLY
ou seja, WHERE M.ID=D.ID
.
2. Quando precisamos da funcionalidade INNER JOIN
usando funções.
CROSS APPLY
pode ser usado como substituto de INNER JOIN
Quando precisamos de obter resultados da tabela Master
e de um function
.
SELECT M.ID,M.NAME,C.PERIOD,C.QTY
FROM MASTER M
CROSS APPLY dbo.FnGetQty(M.ID) C
E aqui está a função
CREATE FUNCTION FnGetQty
(
@Id INT
)
RETURNS TABLE
AS
RETURN
(
SELECT ID,PERIOD,QTY
FROM DETAILS
WHERE ID=@Id
)
Que gerou o seguinte resultado
x------x---------x--------------x-------x
| Id | Name | PERIOD | QTY |
x------x---------x--------------x-------x
| 1 | A | 2014-01-13 | 10 |
| 1 | A | 2014-01-11 | 15 |
| 1 | A | 2014-01-12 | 20 |
| 2 | B | 2014-01-06 | 30 |
| 2 | B | 2014-01-08 | 40 |
x------x---------x--------------x-------x
VANTAGEM ADICIONAL DA APLICAÇÃO CRUZADA
APPLY
pode ser utilizado como substituto de UNPIVOT
. Pode utilizar-se aqui CROSS APPLY
ou OUTER APPLY
, que são permutáveis.
Considere que tem a tabela abaixo (nomeado MYTABLE
).
x------x-------------x--------------x
| Id | FROMDATE | TODATE |
x------x-------------x--------------x
| 1 | 2014-01-11 | 2014-01-13 |
| 1 | 2014-02-23 | 2014-02-27 |
| 2 | 2014-05-06 | 2014-05-30 |
| 3 | NULL | NULL |
x------x-------------x--------------x
A consulta está em baixo.
SELECT DISTINCT ID,DATES
FROM MYTABLE
CROSS APPLY(VALUES (FROMDATE),(TODATE))
COLUMNNAMES(DATES)
O que te traz o resultado
x------x-------------x
| Id | DATES |
x------x-------------x
| 1 | 2014-01-11 |
| 1 | 2014-01-13 |
| 1 | 2014-02-23 |
| 1 | 2014-02-27 |
| 2 | 2014-05-06 |
| 2 | 2014-05-30 |
| 3 | NULL |
x------x-------------x
Aqui está um exemplo quando a aplicação CROSS faz uma enorme diferença com o desempenho:
Usar CROSS aplicar para otimizar as ligações entre as condições
Note que, além de substituir as juntas internas, Você também pode reutilizar códigos como datas truncadas sem pagar a penalidade de desempenho por envolver UDFs escalares, por exemplo: Calculando a terceira quarta-feira do mês com UDFs inline
Parece-me que CROSS APPLY pode preencher uma certa lacuna ao trabalhar com campos calculados em consultas complexas/aninhadas, e torná-los mais simples e legíveis.
Exemplo simples: você tem um DoB e você quer apresentar vários campos relacionados com a idade que também irá depender de outras fontes de dados (como o emprego), como a idade, um grupo, AgeAtHiring, Minimumretidato, etc. para uso em sua aplicação de usuário final (Pivotables Excel, por exemplo).
As opções são limitadas e raramente elegante:
As subqueries do JOIN não podem introduzir novos valores no conjunto de dados com base em dados na consulta-mãe (ele deve permanecer por conta própria).
UDFs são limpos, mas lentos como eles tendem a evitar operações paralelas. E ser uma entidade separada pode ser uma coisa boa (menos código) ou má (onde está o código).
Mesas de junção. Às vezes eles podem trabalhar, mas em breve você está se juntando a subquadorias com toneladas de sindicatos. Grande bagunca.
Crie mais uma vista de propósito único, assumindo que os seus cálculos não requerem dados obtidos a meio da sua consulta principal.
Mesas intermediárias. Sim... isso geralmente funciona, e muitas vezes uma boa opção como eles podem ser indexados e rápidos, mas o desempenho também pode cair devido à atualização de declarações não sendo paralelas e não permitindo que as fórmulas em cascata (resultados de reutilização) para atualizar vários campos dentro da mesma declaração. E às vezes preferias fazer as coisas de uma só vez.
Perguntas de nidificação. Sim em qualquer ponto você pode colocar parênteses em toda a sua consulta e usá-lo como um subquery sobre o qual você pode manipular os dados de fonte e campos calculados da mesma forma. Mas não podes fazer isto muito antes que fique feio. Muito feio.
-
Repito código. Qual é o maior valor de 3 longo (caso...MAIS...Declarações? Isso vai ser legível!
- Diz aos teus clientes para calcularem as coisas. eles mesmos.
Os valores introduzidos através da aplicação cruzada podem...
- ser usado para criar um ou vários campos calculados sem adicionar problemas de desempenho, complexidade ou legibilidade Ao misturar
- tal como com as juntas, várias declarações de Aplicação cruzada subsequentes podem referir-se a si próprias:
CROSS APPLY (select crossTbl.someFormula + 1 as someMoreFormula) as crossTbl2
- pode utilizar os valores introduzidos por uma cruz nas condições de junção subsequentes
- como bónus, há o aspecto da função do valor da Tabela
A aplicação cruzada também funciona bem com um campo XML. Se você deseja selecionar os valores do nó em combinação com outros campos.
Por exemplo, se tiver uma tabela contendo algum xml
<root> <subnode1> <some_node value="1" /> <some_node value="2" /> <some_node value="3" /> <some_node value="4" /> </subnode1> </root>
Usando a consulta
SELECT
id as [xt_id]
,xmlfield.value('(/root/@attribute)[1]', 'varchar(50)') root_attribute_value
,node_attribute_value = [some_node].value('@value', 'int')
,lt.lt_name
FROM dbo.table_with_xml xt
CROSS APPLY xmlfield.nodes('/root/subnode1/some_node') as g ([some_node])
LEFT OUTER JOIN dbo.lookup_table lt
ON [some_node].value('@value', 'int') = lt.lt_id
Vai devolver um resultado
xt_id root_attribute_value node_attribute_value lt_name
----------------------------------------------------------------------
1 test1 1 Benefits
1 test1 4 FINRPTCOMPANY
Acho que deve ser legível;)
CROSS APPLY será um pouco único para as pessoas que lêem dizer-lhes que está a ser usado um UDF que será aplicado a cada linha a partir da mesa à esquerda.
É claro que existem outras limitações em que uma aplicação cruzada é melhor usada do que a junção que outros amigos postaram acima.O Cross apply pode ser usado para substituir o subquery onde você precisa de uma coluna do subquery
Subquery
select * from person p where
p.companyId in(select c.companyId from company c where c.companyname like '%yyy%')
Aqui não serei capaz de seleccionar as colunas da tabela da empresa então, usando cross apply
select P.*,T.CompanyName
from Person p
cross apply (
select *
from Company C
where p.companyid = c.companyId and c.CompanyName like '%yyy%'
) T
Aqui está um artigo que explica tudo, com a sua diferença de desempenho e uso sobre as juntas.
SQL Server CROSS APPLY and OUTER APPLY over JOINS
Como sugerido neste artigo, não há diferença de desempenho entre eles para operações normais de junção (interna e cruzada).
A diferença de Utilização chega quando você tem que fazer uma pesquisa como esta:
CREATE FUNCTION dbo.fn_GetAllEmployeeOfADepartment(@DeptID AS INT)
RETURNS TABLE
AS
RETURN
(
SELECT * FROM Employee E
WHERE E.DepartmentID = @DeptID
)
GO
SELECT * FROM Department D
CROSS APPLY dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID)
Isto é, quando tens de te relacionar com a função. Presente não pode ser feito usando a junção interna, o que lhe daria o erro "não foi possível ligar o identificador multi-partes "D. DepartmentID"." aqui o valor é passado para a função como cada linha é lida. Parece-me bem. :)
Bem, eu não tenho certeza se isso se qualifica como uma razão para usar Cross Apply versus Inner Join, mas esta consulta foi respondida para mim em um Post do fórum usando Cross Apply, então eu não tenho certeza se existe um método equalivent usando Inner Join:
Create PROCEDURE [dbo].[Message_FindHighestMatches]
-- Declare the Topical Neighborhood
@TopicalNeighborhood nchar(255)
Como BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
Create table #temp
(
MessageID int,
Subjects nchar(255),
SubjectsCount int
)
Insert into #temp Select MessageID, Subjects, SubjectsCount From Message
Select Top 20 MessageID, Subjects, SubjectsCount,
(t.cnt * 100)/t3.inputvalues as MatchPercentage
From #temp
cross apply (select count(*) as cnt from dbo.Split(Subjects,',') as t1
join dbo.Split(@TopicalNeighborhood,',') as t2
on t1.value = t2.value) as t
cross apply (select count(*) as inputValues from dbo.Split(@TopicalNeighborhood,',')) as t3
Order By MatchPercentage desc
drop table #temp
FIM
Eu forneci um violino SQL abaixo que mostra um exemplo simples de como você pode usar CROSS APPLY para realizar operações lógicas complexas em seu conjunto de dados sem que as coisas fiquem confusas. Não é difícil extrapolar daqui cálculos mais complexos.
A essência do operador aplicar é permitir a correlação entre o lado esquerdo e direito do operador na cláusula FROM.
Em contraste com a junção, a correlação entre entradas não é permitida.
Falando de correlação no operador de Aplicação, quero dizer do lado direito podemos colocar:- um quadro derivado - como um subcontingente correlacionado com um nome falso
- a table valued function - a conceptual view with parameters, where the parameter can refer to the table valued function lado esquerdo
Ambos podem devolver múltiplas colunas e linhas.
SELECT *
FROM Customer
CROSS APPLY (
SELECT TOP 1 *
FROM Order
WHERE Order.CustomerId = Customer.CustomerId
ORDER BY OrderDate DESC
) T