Quand dois-je utiliser l'application croisée plutôt que la jointure interne ?

Question

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

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

Je sais aussi que CROSS APPLY n'a pas besoin d'un UDF comme table de droite.

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

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

---

Edit :

Voici un exemple trivial, où les plans d'exécution sont exactement les mêmes. (Montrez-moi un exemple où ils diffèrent et où l'application croisée est plus rapide ou 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

Answer 1

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

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

• INNER JOIN vs. CROSS APPLY

CROSS APPLY fonctionne mieux sur les choses qui ne sont pas simples JOIN condition.

Celui-ci sélectionne 3 les 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

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

Vous pourriez probablement faire quelque chose comme ça en utilisant CTE et la fonction de 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 cela est moins lisible et probablement moins efficace.

Mise à jour :

Je viens de vérifier.

master est un tableau d'environ 20,000,000 les 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 pendant près de 30 secondes, tandis 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ée.

Answer 2

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

Il s'agit d'un erreur de syntaxe car les fonctions de table ne peuvent prendre que des variables ou des constantes comme paramètres lorsqu'elles utilisent la fonction inner join .

Cependant :

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.

Edit : Ou encore, 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

Answer 3

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

Exemple simple : vous disposez d'une BD et vous souhaitez présenter plusieurs champs liés à l'âge qui reposeront également sur d'autres sources de données (comme l'emploi), comme l'âge, le groupe d'âge, l'âge à l'embauche, la date de départ à la retraite minimum, etc. pour les utiliser dans votre application d'utilisateur final (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 sur la base des données de la requête mère (elles doivent être autonomes).

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

• Tables de recherche. Parfois, elles peuvent fonctionner, mais très vite, vous joignez des sous-requêtes avec des tonnes d'UNIONs. Un gros gâchis.

• Créez 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.

• Tables intermédiaires. Oui... cela fonctionne généralement, et c'est souvent une bonne option car elles peuvent être indexées et rapides, mais les performances peuvent aussi chuter car les instructions UPDATE ne sont pas parallèles et ne permettent pas de cascader les formules (réutilisation des résultats) pour mettre à jour plusieurs champs dans la même instruction. Et parfois, vous préférez simplement faire les choses en une seule passe.

• Requêtes imbriquées. 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 il y a une limite à ce que vous pouvez faire avant que cela ne devienne laid. Très vilain.

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

  • Dites à vos clients de calculer eux-mêmes ces fichues choses.

J'ai raté quelque chose ? Probablement, alors n'hésitez pas à faire des commentaires. Mais bon, CROSS APPLY est comme une aubaine dans de telles situations : il suffit d'ajouter un simple CROSS APPLY (select tbl.value + 1 as someFormula) as crossTbl et voilà ! Votre nouveau champ est maintenant prêt à être utilisé pratiquement comme s'il avait toujours été là dans vos données sources.

Les valeurs introduites par CROSS APPLY peuvent...

• être utilisé pour créer un ou plusieurs champs calculés sans ajouter des problèmes de performance, de complexité ou de lisibilité au mélange
• Comme avec les JOIN, plusieurs instructions CROSS APPLY ultérieures 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 un CROSS APPLY dans des conditions JOIN ultérieures
• En prime, il y a l'aspect fonction de la valeur du Tableau

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

Answer 4

Voici un exemple où CROSS APPLY fait une énorme différence dans les performances :

Utilisation de CROSS APPLY pour optimiser les jointures sur les conditions BETWEEN (entre)

Notez que, outre le remplacement des jointures internes, vous pouvez également réutiliser du code tel que la troncature des dates sans payer de pénalité de performance pour impliquer des UDF scalaires, par exemple : Calculer le troisième mercredi du mois avec des UDF en ligne

Answer 5

L'application croisée fonctionne aussi bien avec un champ XML. Si vous souhaitez sélectionner des valeurs de nœuds en combinaison avec d'autres champs.

Par exemple, si vous avez une table contenant des fichiers xml

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

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

Renverra un résultat

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