494 votos

Cuando debo usar Cruz Aplicar sobre Inner Join?

¿Cuál es el propósito principal del uso de la CRUZ se APLICAN?

He leído (vagamente, a través de mensajes en Internet) que de la cruz se aplican pueden ser más eficientes a la hora de seleccionar a través de grandes conjuntos de datos si está particionado. (Paginación viene a la mente)

También sé que la CRUZ de APLICAR , no requiere de una UDF como el derecho de la tabla.

En la mayoría de los INNER JOIN consultas (de uno a varios relaciones), yo podría volver a escribir para el uso de la CRUZ de APLICAR, pero ellos siempre me dan equivalente de planes de ejecución.

Puede alguien darme un buen ejemplo de cuando entre a APLICAR hace una diferencia en aquellos casos donde INNER JOIN va a trabajar así?


Edición:

He aquí un ejemplo trivial, en la que la ejecución de los planes son exactamente los mismos. (Muéstrame donde se diferencian y en la cruz de aplicar, 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

373voto

Quassnoi Puntos 191041

Puede alguien darme un buen ejemplo de cuando entre a APLICAR hace una diferencia en aquellos casos donde INNER JOIN va a trabajar así?

Ver el artículo en mi blog de detallado del rendimiento de comparación:

CROSS APPLY trabaja mejor en las cosas que no tienen sencillos 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 puede ser fácilmente formulado con un INNER JOIN condición.

Usted probablemente podría hacer algo así como que el uso de CTE's y la función de la 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.

Actualización:

Simplemente marcada.

master es una tabla de alrededor 20,000,000 registros con un PRIMARY KEY sobre 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

funciona para 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.

122voto

nurettin Puntos 4083

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

Ejemplo (error de sintaxis):

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

Este es un error de sintaxis, porque de funciones de tabla sólo puede tener variables o constantes como parámetros cuando se utiliza inner join.

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.

Edición: O, alternativamente, más corto sintaxis: (por ErikE)

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

31voto

AlexKuznetsov Puntos 9555

aquí es un ejemplo, cuando la CRUZ se APLICAN hace una gran diferencia con el rendimiento:

El uso de la CRUZ se APLICAN para optimizar las combinaciones ENTRE las condiciones de

Tenga en cuenta que además de la sustitución de interior se une también se puede reutilizar el código como truncar fechas sin pagar penalización de rendimiento para involing Udf escalares, por ejemplo: Calcular el tercer miércoles del mes, en línea con Udf

19voto

mtone Puntos 407

A mí me parece que la CRUZ puede APLICAR relleno de una cierta falta cuando se trabaja con campos calculados en el complejo/consultas anidadas, y hacerlos más simples y más fáciles de leer.

Ejemplo Simple: usted tiene una fecha de Nacimiento y desea presentar varias relacionadas con la edad de los campos que también dependen de otras fuentes de datos (como el empleo), como la Edad, grupo de edad, AgeAtHiring, MinimumRetirementDate, etc. para el uso en la aplicación de usuario final (Tablas dinámicas de Excel, por ejemplo).

Las opciones son limitadas y rara vez elegante:

  • ÚNETE a subconsultas no introducir nuevos valores en el conjunto de datos basado en datos del padre de la consulta (que debe estar en su propio).

  • Udf están ordenadas, pero lento, ya que tienden a evitar que las operaciones paralelas. Y siendo una entidad independiente puede ser una buena (menos código) o mala (donde está el código) de la cosa.

  • Tablas. A veces se puede trabajar, pero pronto va a unir subconsultas con toneladas de Sindicatos. Gran lío.

  • Crear otro propósito único punto de vista, asumiendo que sus cálculos no es necesario que los datos obtenidos a mitad de camino a través de su consulta principal.

  • Intermediario de las tablas. Sí..., que habitualmente trabaja, y a menudo una buena opción, ya que puede ser indexados y rápido, pero el rendimiento también puede caer debido a la ACTUALIZACIÓN de las declaraciones de no ser paralelo y que no permite a la cascada de fórmulas (reutilización de resultados) para actualizar varios campos dentro de la misma declaración. Y a veces simplemente te prefieren hacer las cosas en una sola pasada.

  • La anidación de las consultas. Sí, en cualquier momento, usted puede poner un paréntesis en todo su consulta y utilizarla como una subconsulta en la que puede manipular los datos de origen y campos calculados por igual. Pero usted sólo puede hacer esto mucho antes de que la cosa se pone fea. Muy feo.

  • La repetición de código. ¿Cuál es el mayor valor de 3 largo (CASO...ELSE...END) declaraciones? Que va a ser leído!

    • Dígale a su clientes para calcular la maldita cosas mismas.

¿Me olvido de algo? Probablemente, así que siéntete libre de comentar. Pero bueno, CROSS APPLY es como un regalo del cielo en tales situaciones: usted acaba de agregar un simple CROSS APPLY (select tbl.value + 1 as someFormula) as crossTbl y voilà! Su nuevo campo está ahora listo para su uso, prácticamente, como siempre ha sido en su origen de datos.

Los valores introducidos a través de la CRUZ puede APLICAR...

  • ser utilizado para crear uno o varios campos calculados sin la adición de rendimiento, complejidad o problemas de legibilidad a la mezcla
  • como con Combinaciones de varias de las siguientes CRUZ APLICAR las instrucciones pueden referirse a sí mismos: CROSS APPLY (select crossTbl.someFormula + 1 as someMoreFormula) as crossTbl2
  • puede utilizar los valores introducidos por una CRUZ de APLICAR en las subsiguientes condiciones de combinación
  • Como un bono, la función con valores de Tabla de aspecto

Dang, no hay nada que no pueda hacer.

6voto

Chris Puntos 51

Cruz aplicar funciona bien con un campo XML así. Si desea seleccionar los valores de nodo en combinación con otros campos.

Por ejemplo, si tiene una tabla que contiene algo de xml

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

El uso de 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

Iteramos.com

Iteramos es una comunidad de desarrolladores que busca expandir el conocimiento de la programación mas allá del inglés.
Tenemos una gran cantidad de contenido, y también puedes hacer tus propias preguntas o resolver las de los demás.

Powered by:

X