Pregunta ¿Cuándo debería usar Cross Apply over Inner Join?


¿Cuál es el propósito principal de usar CROSS SOLICITAR?

He leído (vagamente, a través de publicaciones en Internet) que cross apply puede ser más eficiente al seleccionar conjuntos de datos grandes si está creando particiones. (La paginación viene a la mente)

También sé que CROSS APPLY  no requiere un UDF como la tabla correcta.

En la mayoría INNER JOIN consultas (relaciones de uno a muchos), podría reescribirlos para usar CROSS APPLY, pero siempre me dan planes de ejecución equivalentes.

¿Alguien puede darme un buen ejemplo de cuándo CROSS APPLY hace una diferencia en aquellos casos donde INNER JOIN ¿funcionará también?


Editar:

Aquí hay un ejemplo trivial, donde los planes de ejecución son exactamente los mismos. (Muéstreme uno donde difieran y dónde cross apply es más rápido / más 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

765
2017-07-16 17:42


origen


Respuestas:


¿Alguien puede darme un buen ejemplo de cuándo CROSS APPLY hace la diferencia en aquellos casos en que INNER JOIN también funcionará?

Vea el artículo en mi blog para una comparación de rendimiento detallada:

CROSS APPLY funciona mejor en cosas que no tienen un simple JOIN condición.

Este selecciona 3 últimos registros de t2 para cada registro 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

No se puede formular fácilmente con un INNER JOIN condición.

Probablemente puedas hacer algo así usando CTEy función de ventana:

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

, pero esto es menos legible y probablemente menos eficiente.

Actualizar:

Acabo de revisarlo.

master es una tabla de aproximadamente 20,000,000 registros con un PRIMARY KEY en 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 casi 30 segundos, mientras que 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

es instantáneo


570
2017-07-16 17:52



cross apply a veces le permite hacer cosas que no puede hacer con inner join.

Ejemplo (un error de sintaxis):

select F.* from sys.objects O  
inner join dbo.myTableFun(O.name) F   
on F.schema_id= O.schema_id

Esto es un error de sintaxis, porque, cuando se usa con inner join, las funciones de tabla solo pueden tomar variables o constantes como parámetros. (Es decir, el parámetro de función de tabla no puede depender de la columna de otra tabla).

Sin embargo:

select F.* from sys.objects O  
cross apply ( select * from dbo.myTableFun(O.name) ) F  
where F.schema_id= O.schema_id

Esto es legal

Editar: O, alternativamente, sintaxis más corta: (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 + tiene Tablas Derivadas Lateral y Postgresql (9.3+) tiene Subconsultas laterales que se puede usar para un efecto similar.


176
2018-03-03 12:28



Considera que tienes dos tablas.

MESA PRINCIPAL

x------x--------------------x
| Id   |        Name        |
x------x--------------------x
|  1   |          A         |
|  2   |          B         |
|  3   |          C         |
x------x--------------------x

CUADRO DETALLES

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

Hay muchas situaciones en las que tenemos que reemplazar INNER JOIN con CROSS APPLY.

1. Unir dos tablas basadas en TOP n resultados

Considere si necesitamos seleccionar Id y Name de Master y las dos últimas fechas 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

La consulta anterior genera el siguiente 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

Ver, generó resultados para las últimas dos fechas con las últimas dos fechas Id y luego se unió a estos registros solo en la consulta externa en Id, Cuál está mal. Para lograr esto, debemos 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

y forma el siguiente 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

Así es como funciona. La consulta dentro CROSS APPLY puede hacer referencia a la mesa exterior, donde INNER JOINno puede hacer esto (arroja error de compilación). Al encontrar las dos últimas fechas, la unión se hace dentro de CROSS APPLY es decir., WHERE M.ID=D.ID.

2. Cuando necesitamos INNER JOIN funcionalidad usando funciones.

CROSS APPLY puede ser utilizado como un reemplazo con INNER JOIN cuando necesitamos obtener el resultado de Master mesa y a function.

SELECT M.ID,M.NAME,C.PERIOD,C.QTY
FROM MASTER M
CROSS APPLY dbo.FnGetQty(M.ID) C

Y aquí está la función

CREATE FUNCTION FnGetQty 
(   
    @Id INT 
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT ID,PERIOD,QTY 
    FROM DETAILS
    WHERE ID=@Id
)

que generó el siguiente 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

VENTAJA ADICIONAL DE CRUZ APLICAR 

APPLY puede ser utilizado como un reemplazo para UNPIVOT. Ya sea CROSS APPLY o OUTER APPLY se puede usar aquí, que son intercambiables.

Considere que tiene la siguiente tabla (llamada 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

La consulta está debajo.

SELECT DISTINCT ID,DATES
FROM MYTABLE 
CROSS APPLY(VALUES (FROMDATE),(TODATE))
COLUMNNAMES(DATES)

que te trae el 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

110
2018-02-26 02:12



Aquí hay un ejemplo cuando CROSS APPLY hace una gran diferencia con el rendimiento:

Usar CROSS APPLY para optimizar las uniones entre las condiciones BETWEEN

Tenga en cuenta que además de reemplazar uniones internas, también puede reutilizar código como truncar fechas sin pagar una penalización de rendimiento por la participación de UDF escalares, por ejemplo: Cálculo del tercer miércoles del mes con UDF en línea


37
2017-07-16 18:28



Me parece que CROSS APPLY puede llenar una cierta brecha cuando se trabaja con campos calculados en consultas complejas / anidadas, y los hace más simples y más legibles.

Ejemplo simple: tiene un DoB y desea presentar múltiples campos relacionados con la edad que también dependerán de otras fuentes de datos (como empleo), como Age, AgeGroup, AgeAtHiring, MinimumRetirementDate, etc. para usar en su aplicación de usuario final (PivotTables de Excel, por ejemplo).

Las opciones son limitadas y raramente elegantes:

  • Las subconsultas UNIR no pueden introducir nuevos valores en el conjunto de datos en función de los datos en la consulta primaria (debe ser independiente).

  • Las UDF son ordenadas, pero lentas ya que tienden a evitar operaciones paralelas. Y ser una entidad separada puede ser una cosa buena (menos código) o mala (dónde está el código).

  • Tablas de unión A veces pueden funcionar, pero pronto se están uniendo subconsultas con toneladas de UNION. Gran desorden.

  • Cree otra vista de un solo propósito, suponiendo que sus cálculos no requieren datos obtenidos a mitad de su consulta principal.

  • Tablas intermedias. Sí ... eso generalmente funciona, y a menudo es una buena opción, ya que pueden indexarse ​​y acelerarse, pero el rendimiento también puede disminuir debido a que las instrucciones UPDATE no son paralelas y no permiten la cascada de fórmulas (reutilización de resultados) para actualizar varios campos dentro del misma declaración. Y a veces preferirías hacer las cosas de una vez.

  • Anidando consultas. Sí, en cualquier punto puede poner paréntesis en toda su consulta y usarla como una subconsulta sobre la cual puede manipular los datos de origen y los campos calculados por igual. Pero solo puedes hacer esto mucho antes de que se ponga feo. Muy feo.

  • Repitiendo el código ¿Cuál es el mayor valor de 3 declaraciones largas (CASE ... ELSE ... END)? ¡Eso será legible!

    • Dile a tus clientes que calculen las malditas cosas ellos mismos.

¿Me he perdido algo? Probablemente, así que siéntete libre de comentar. Pero bueno, CROSS APPLY es como un regalo del cielo en tales situaciones: simplemente agregas un simple CROSS APPLY (select tbl.value + 1 as someFormula) as crossTbl¡y voilá! Su nuevo campo ahora está listo para usar prácticamente como si siempre hubiera estado allí en sus datos de origen.

Los valores introducidos a través de CROSS APPLY pueden ...

  • se usará para crear uno o varios campos calculados sin agregar problemas de rendimiento, complejidad o legibilidad a la mezcla
  • al igual que con JOINs, varias declaraciones subsiguientes de CROSS APPLY pueden referirse a sí mismas: CROSS APPLY (select crossTbl.someFormula + 1 as someMoreFormula) as crossTbl2
  • puede usar los valores introducidos por un CROSS APPLY en subsiguientes condiciones de JOIN
  • Como beneficio adicional, está el aspecto de la función con valores de tabla

¡Dang, no hay nada que no puedan hacer!


33
2018-06-11 07:49



Cross apply también funciona bien con un campo XML. Si desea seleccionar valores de nodo en combinación con otros campos.

Por ejemplo, si tiene una tabla que contiene algunos xml

<root>
    <subnode1>
       <some_node value="1" />
       <some_node value="2" />
       <some_node value="3" />
       <some_node value="4" />
    </subnode1>
</root>

Usando la 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

Devolverá un resultado

xt_id root_attribute_value node_attribute_value lt_name
----------------------------------------------------------------------
1     test1            1                    Benefits
1     test1            4                    FINRPTCOMPANY

12
2018-02-01 18:52



Supongo que debería ser legibilidad;)

CROSS APPLY será algo único para las personas que lean la lectura para decirles que se está utilizando un UDF que se aplicará a cada fila de la tabla de la izquierda.

Por supuesto, hay otras limitaciones donde se aplica mejor una SOLICITUD DE CRUZ que JOIN que otros amigos publicaron anteriormente.


5
2017-07-16 18:12



Aplicar Cruz se puede usar para reemplazar las subconsultas donde necesita una columna de la subconsulta

subconsulta

select * from person p where
p.companyId in(select c.companyId from company c where c.companyname like '%yyy%')

aquí no podré seleccionar las columnas de la tabla de la empresa entonces, 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

3
2017-12-11 09:51



Aquí hay un artículo que lo explica todo, con su diferencia de rendimiento y uso en JOINS.

CROSS de SQL Server se aplica y APLICACIÓN EXTERNA sobre UNIDOS

Como se sugiere en este artículo, no hay diferencia de rendimiento entre ellos para las operaciones de unión normal (INTERIOR Y CRUZADA).

enter image description here

La diferencia de uso llega cuando tienes que hacer una consulta 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)

Es decir, cuando tienes que relacionarte con la función. Esto no se puede hacer usando INNER JOIN, que le daría el error "El identificador de varias partes" D.DepartmentID "no se pudo enlazar". Aquí el valor pasa a la función a medida que se lee cada fila. Me parece genial. :)


3
2018-03-21 04:44



Bueno, no estoy seguro de si esto califica como una razón para usar Cross Apply versus Inner Join, pero esta consulta fue respondida en una publicación del foro usando Cross Apply, por lo que no estoy seguro si existe un método igualitario usando Inner Join:

Create PROCEDURE [dbo].[Message_FindHighestMatches]

-- Declare the Topical Neighborhood
@TopicalNeighborhood nchar(255)

COMO EMPEZAR

-- 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

FIN


2
2018-03-08 19:51



Esta es quizás una vieja pregunta, pero aún me encanta el poder de CROSS APPLY para simplificar la reutilización de la lógica y proporcionar un mecanismo de "encadenamiento" para obtener resultados.

A continuación, proporcioné un SQL Fiddle que muestra un ejemplo simple de cómo puede usar CROSS APPLY para realizar operaciones lógicas complejas en su conjunto de datos sin que las cosas se compliquen. No es difícil extrapolar desde aquí cálculos más complejos.

http://sqlfiddle.com/#!3/23862/2


0
2017-10-06 01:41