Question Quand devrais-je utiliser Cross Apply sur Inner Join?


Quel est le but principal de l'utilisation CROSS APPLIQUE?

J'ai lu (vaguement, à travers des messages sur Internet) que cross apply peut être plus efficace lors de la sélection de grands ensembles de données si vous partitionnez. (La pagination vient à l'esprit)

Je sais aussi que CROSS APPLY  ne nécessite pas de UDF comme table de droite.

Dans la plupart INNER JOIN requêtes (relations un-à-plusieurs), je pourrais les réécrire à utiliser CROSS APPLYmais ils me donnent toujours des plans d'exécution équivalents.

Quelqu'un peut-il me donner un bon exemple de quand CROSS APPLY fait une différence dans les cas où INNER JOIN travaillera aussi bien?


Modifier:

Voici un exemple trivial, où les plans d'exécution sont exactement les mêmes. (Montrez-moi un où ils diffèrent et où cross apply est plus rapide / plus efficace)

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


origine


Réponses:


Quelqu'un peut-il me donner un bon exemple de quand CROSS APPLY fait une différence dans les cas où INNER JOIN fonctionnera aussi bien?

Voir l'article sur mon blog pour une comparaison détaillée des performances:

CROSS APPLY fonctionne mieux sur les choses qui n'ont pas simple JOIN condition.

Celui-ci sélectionne 3 derniers enregistrements de t2 pour chaque enregistrement 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

Il ne peut pas être facilement formulé avec un INNER JOIN condition.

Vous pourriez probablement faire quelque chose comme ça en utilisant CTEet la fonction de la fenêtre:

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

, mais c'est moins lisible et probablement moins efficace.

Mettre à jour:

Juste vérifié.

master est une table d'environ 20,000,000 enregistrements avec un PRIMARY KEY sur id.

Cette requête:

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

fonctionne pour presque 30 secondes, alors que celui-ci:

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

est instantané.


570
2017-07-16 17:52



cross apply vous permet parfois de faire des choses que vous ne pouvez pas faire avec inner join.

Exemple (une erreur de syntaxe):

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

C'est un erreur de syntaxe, parce que, lorsqu'il est utilisé avec inner join, les fonctions de table ne peuvent prendre variables ou constantes en tant que paramètres. (Par exemple, le paramètre de la fonction table ne peut pas dépendre de la colonne d'une autre table.)

Toutefois:

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

C'est légal.

Modifier: Ou alternativement, une syntaxe plus courte: (par ErikE)

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

Modifier:

Remarque: Informix 12.10 xC2 + a Tables dérivées latérales et Postgresql (9.3+) a Sous-requêtes latérales qui peut être utilisé pour un effet similaire.


176
2018-03-03 12:28



Considérez que vous avez deux tables.

MAÎTRE TABLE

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

TABLEAU DE DÉTAILS

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

Il y a beaucoup de situations où nous devons remplacer INNER JOIN avec CROSS APPLY.

1. Joignez deux tables basées sur TOP n résultats

Considérez si nous devons sélectionner Id et Name de Master et deux dernières dates pour chaque 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 requête ci-dessus génère le résultat suivant.

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

Voir, il a généré des résultats pour les deux dernières dates avec les deux dernières dates Id puis joint ces enregistrements uniquement dans la requête externe sur Id, ce qui est faux. Pour ce faire, nous devons utiliser 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

et forme le résultat suivant.

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

Voici comment cela fonctionne. La requête à l'intérieur CROSS APPLY peut référencer la table externe, où INNER JOINne peut pas faire cela (il jette une erreur de compilation). Lors de la recherche des deux dernières dates, la jonction se fait à l'intérieur CROSS APPLY c'est à dire., WHERE M.ID=D.ID.

2. Quand nous avons besoin INNER JOIN fonctionnalité à l'aide de fonctions.

CROSS APPLY peut être utilisé comme un remplacement avec INNER JOIN quand nous devons obtenir des résultats de Master table et un function.

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

Et voici la fonction

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

qui a généré le résultat suivant

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

AVANTAGE SUPPLÉMENTAIRE DE CROSS APPLY 

APPLY peut être utilisé en remplacement de UNPIVOT. Non plus CROSS APPLY ou OUTER APPLY peut être utilisé ici, qui sont interchangeables.

Considérez que vous avez le tableau ci-dessous (nommé 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 requête est ci-dessous.

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

ce qui vous apporte le résultat

  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



Voici un exemple lorsque CROSS APPLY fait une énorme différence avec les performances:

Utiliser CROSS APPLY pour optimiser les jointures sur les conditions BETWEEN

Notez qu'en plus de remplacer les jointures internes, vous pouvez également réutiliser du code, par exemple tronquer des dates sans payer de pénalité de performance pour les fonctions UDF scalaires, par exemple: Calcul du troisième mercredi du mois avec les UDF en ligne


37
2017-07-16 18:28



Il me semble que CROSS APPLY peut combler une certaine lacune lorsque l'on travaille avec des champs calculés dans des requêtes complexes / imbriquées, et les rendre plus simples et plus lisibles.

Exemple simple: vous avez un DoB et vous souhaitez présenter plusieurs champs liés à l'âge qui dépendront également d'autres sources de données (telles que l'emploi), telles que Age, AgeGroup, AgeAtHiring, MinimumRetirementDate, etc. à utiliser dans votre application utilisateur. (Tableaux croisés dynamiques Excel, par exemple).

Les options sont limitées et rarement élégantes:

  • Les sous-requêtes JOIN ne peuvent pas introduire de nouvelles valeurs dans l'ensemble de données en fonction des données de la requête parente (elles doivent être autonomes).

  • Les fonctions UDF sont propres, mais lentes car elles ont tendance à empêcher les opérations parallèles. Et être une entité séparée peut être une bonne chose (moins de code) ou une mauvaise (où est le code).

  • Tables de jonction. Parfois, ils peuvent fonctionner, mais bientôt vous rejoignez des sous-requêtes avec des tonnes d'UNIONs. Grand désordre.

  • Créez encore une autre vue à usage unique, en supposant que vos calculs ne nécessitent pas de données obtenues à mi-chemin de votre requête principale.

  • Tableaux intermédiaires. Oui ... cela fonctionne généralement, et souvent une bonne option car ils peuvent être indexés et rapides, mais les performances peuvent également chuter car les instructions UPDATE ne sont pas parallèles et ne permettent pas de cascader des formules (réutilisation des résultats) pour mettre à jour plusieurs champs dans le même déclaration. Et parfois, vous préféreriez simplement faire les choses en un seul passage.

  • Imbrication des requêtes Oui, à tout moment, vous pouvez mettre des parenthèses sur l'ensemble de votre requête et l'utiliser comme une sous-requête sur laquelle vous pouvez manipuler les données sources et les champs calculés. Mais vous ne pouvez le faire tellement avant qu'il ne devienne laid. Très laid.

  • Répéter le code. Quelle est la plus grande valeur de 3 déclarations longues (CASE ... ELSE ... END)? Ça va être lisible!

    • Dites à vos clients de calculer les choses sacrément elles-mêmes.

Ai-je manqué quelque chose? Probablement, alors n'hésitez pas à commenter. Mais bon, CROSS APPLY est comme une aubaine dans de telles situations: vous venez d'ajouter un simple CROSS APPLY (select tbl.value + 1 as someFormula) as crossTblet voilà! Votre nouveau champ est maintenant prêt à être utilisé pratiquement comme il l'a toujours été dans vos données sources.

Les valeurs introduites via CROSS APPLY peuvent ...

  • être utilisé pour créer un ou plusieurs champs calculés sans ajouter de problèmes de performance, de complexité ou de lisibilité au mélange
  • Comme avec JOINs, plusieurs instructions CROSS APPLY suivantes peuvent se référer à elles-mêmes: CROSS APPLY (select crossTbl.someFormula + 1 as someMoreFormula) as crossTbl2
  • vous pouvez utiliser les valeurs introduites par une application CROSS dans les conditions JOIN suivantes
  • En prime, il y a l'aspect de la fonction Table

Dang, il n'y a rien qu'ils ne peuvent pas faire!


33
2018-06-11 07:49



Cross apply fonctionne bien avec un champ XML. Si vous souhaitez sélectionner des valeurs de nœud en combinaison avec d'autres champs.

Par exemple, si vous avez une table contenant du xml

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

En utilisant la requête

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

Va retourner un résultat

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

12
2018-02-01 18:52



Je suppose que ce devrait être la lisibilité;)

CROSS APPLY sera un peu unique pour les personnes lisant pour leur dire que l'on utilise un UDF qui sera appliqué à chaque ligne de la table sur la gauche.

Bien sûr, il y a d'autres limitations où une APPLICATION CROISEE est mieux utilisée que JOIN que d'autres amis ont posté ci-dessus.


5
2017-07-16 18:12



Cross apply peut être utilisé pour remplacer les sous-requêtes où vous avez besoin d'une colonne de la sous-requête

sous-requête

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

ici je ne serai pas en mesure de sélectionner les colonnes de la table de l'entreprise donc, en utilisant cross appliquer

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



Voici un article qui explique tout, avec leur différence de performance et leur utilisation sur JOINS.

SQL Server CROSS APPLY et APPLICATION EXTERNE sur JOINS

Comme suggéré dans cet article, il n'y a aucune différence de performance entre eux pour les opérations de jointure normales (INNER AND CROSS).

enter image description here

La différence d'utilisation arrive quand vous devez faire une requête comme ceci:

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)

Autrement dit, quand vous devez vous rapporter à la fonction. Cela ne peut pas être fait en utilisant INNER JOIN, ce qui vous donnerait l'erreur "L'identificateur en plusieurs parties" D.DepartmentID "ne pouvait pas être lié." Ici, la valeur est transmise à la fonction lorsque chaque ligne est lue. Ça a l'air cool pour moi. :)


3
2018-03-21 04:44



Eh bien, je ne suis pas sûr que cela soit une raison pour utiliser Cross Apply contre Inner Join, mais cette question a été répondue dans un forum post utilisant Cross Apply, donc je ne suis pas sûr qu'il existe une méthode equalivent utilisant Inner Join:

Create PROCEDURE [dbo].[Message_FindHighestMatches]

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

COMME COMMENCER

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