Frage Wann sollte ich Cross Apply über Inner Join anwenden?


Was ist der Hauptzweck der Verwendung? KREUZ BEWERBEN?

Ich habe (vage, durch Beiträge im Internet) gelesen, dass cross apply kann bei der Auswahl über große Datenmengen effizienter sein, wenn Sie eine Partitionierung durchführen. (Paging kommt mir in den Sinn)

Das weiß ich auch CROSS APPLY  benötigt keine UDF als rechte Tabelle.

In den meisten INNER JOIN Abfragen (Eins-zu-viele-Beziehungen), könnte ich sie umschreiben um sie zu verwenden CROSS APPLY, aber sie geben mir immer gleichwertige Ausführungspläne.

Kann mir jemand ein gutes Beispiel geben, wann? CROSS APPLY macht einen Unterschied in den Fällen, in denen INNER JOIN wird auch funktionieren?


Bearbeiten:

Hier ist ein triviales Beispiel, wo die Ausführungspläne genau gleich sind. (Zeig mir, wo sie sich unterscheiden und wo cross apply ist schneller / effizienter)

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


Ursprung


Antworten:


Kann mir jemand ein gutes Beispiel dafür geben, wann CROSS APPLY in den Fällen einen Unterschied macht, in denen INNER JOIN auch funktionieren wird?

Sehen Sie den Artikel in meinem Blog für einen detaillierten Leistungsvergleich:

CROSS APPLY funktioniert besser bei Dingen, die nicht einfach sind JOIN Bedingung.

Dieser wählt aus 3 letzte Aufzeichnungen von t2 für jeden Datensatz von 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

Es kann nicht leicht mit einem formuliert werden INNER JOIN Bedingung.

Sie könnten wahrscheinlich so etwas mit CTEs und fenster funktion:

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

, aber das ist weniger lesbar und wahrscheinlich weniger effizient.

Aktualisieren:

Gerade kontrolliert.

master ist eine Tabelle von ungefähr 20,000,000 Aufzeichnungen mit a PRIMARY KEY auf id.

Diese Abfrage:

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

läuft fast 30 Sekunden, während dieser:

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

ist sofort.


570
2017-07-16 17:52



cross apply Manchmal können Sie Dinge tun, die Sie nicht tun können inner join.

Beispiel (ein Syntaxfehler):

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

Das ist ein Syntax-Fehler, weil, wenn verwendet mit inner join, Tabellenfunktionen können nur übernehmen Variablen oder Konstanten als Parameter. (Das heißt, der Tabellenfunktionsparameter kann nicht von der Spalte einer anderen Tabelle abhängen.)

Jedoch:

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

Das ist legal.

Bearbeiten: Oder alternativ kürzere Syntax: (von ErikE)

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

Bearbeiten:

Hinweis: Informix 12.10 xC2 + hat Seitlich abgeleitete Tabellen und Postgresql (9.3+) hat Seitliche Unteranfragen was zu einem ähnlichen Effekt verwendet werden kann.


176
2018-03-03 12:28



Beachten Sie, dass Sie zwei Tabellen haben.

Haupttabelle

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

DETAILS TABELLE

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

Es gibt viele Situationen, in denen wir ersetzen müssen INNER JOIN mit CROSS APPLY.

1. Verknüpfen Sie zwei Tabellen basierend auf TOP n Ergebnisse

Überlegen Sie, ob wir auswählen müssen Id und Name von Master und die letzten beiden Termine für jeden Id von 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

Die obige Abfrage erzeugt das folgende Ergebnis.

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

Sehen Sie, es hat Ergebnisse für die letzten zwei Daten mit den letzten zwei Daten erzeugt Id und dann diese Datensätze nur in der äußeren Abfrage an Id, was falsch ist. Um dies zu erreichen, müssen wir verwenden 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

und bildet das folgende Ergebnis.

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

So funktioniert das. Die Abfrage innerhalb CROSS APPLY kann auf die äußere Tabelle verweisen, wo INNER JOINkann dies nicht tun (es wirft einen Kompilierungsfehler auf). Wenn Sie die letzten beiden Daten finden, wird der Beitritt innerhalb durchgeführt CROSS APPLY d.h. WHERE M.ID=D.ID.

2. Wenn wir brauchen INNER JOIN Funktionalität mit Funktionen.

CROSS APPLY kann als Ersatz mit verwendet werden INNER JOIN wenn wir Ergebnisse bekommen müssen Master Tisch und a function.

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

Und hier ist die Funktion

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

was das folgende Ergebnis erzeugte

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

ZUSÄTZLICHER VORTEIL VON CROSS APPLY 

APPLY kann als Ersatz für verwendet werden UNPIVOT. Entweder CROSS APPLY oder OUTER APPLY kann hier verwendet werden, die austauschbar sind.

Betrachten Sie die folgende Tabelle (benannt 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

Die Abfrage ist unten.

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

was bringt dir das Ergebnis

  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



Hier ist ein Beispiel, wenn CROSS APPLY mit der Leistung einen großen Unterschied macht:

Verwenden Sie CROSS APPLY, um Joins auf BETWEEN-Bedingungen zu optimieren

Beachten Sie, dass Sie neben dem Ersetzen innerer Joins auch Code wie das Abschneiden von Daten ohne Leistungseinbuße für skalierbare UDFs verwenden können. Beispiel: Berechnung des dritten Mittwochs des Monats mit Inline-UDFs


37
2017-07-16 18:28



Es scheint mir, dass CROSS APPLY eine gewisse Lücke füllen kann, wenn man mit berechneten Feldern in komplexen / verschachtelten Abfragen arbeitet und sie einfacher und lesbarer macht.

Einfaches Beispiel: Sie verfügen über eine DoB und möchten mehrere altersbezogene Felder darstellen, die sich auch auf andere Datenquellen (z. B. Beschäftigung) wie Age, AgeGroup, AgeAtHiring, MinimumRetirementDate usw. zur Verwendung in Ihrer Endbenutzeranwendung stützen (Excel PivotTables zum Beispiel).

Optionen sind begrenzt und selten elegant:

  • JOIN-Unterabfragen können keine neuen Werte im Dataset basierend auf Daten in der übergeordneten Abfrage einfügen (sie muss eigenständig sein).

  • UDFs sind ordentlich, aber langsam, da sie dazu neigen, parallele Operationen zu verhindern. Und eine separate Entität zu sein, kann ein gutes (weniger Code) oder ein schlechtes (wo ist der Code) Ding sein.

  • Junction-Tabellen. Manchmal können sie arbeiten, aber schon bald schließen Sie sich Unterabfragen mit Unionen an. Großes Chaos.

  • Erstellen Sie eine weitere Einzelzweckansicht, vorausgesetzt, Ihre Berechnungen erfordern keine Daten, die während der Hauptabfrage erhalten werden.

  • Zwischentische Ja ... das funktioniert normalerweise und ist oft eine gute Option, da sie indexiert und schnell sein können. Die Performance kann jedoch auch dadurch abfallen, dass UPDATE-Anweisungen nicht parallel sind und keine Kaskadenformeln (Ergebnisse wiederverwenden) erlauben, um mehrere Felder zu aktualisieren gleiche Aussage. Und manchmal ziehen Sie es vor, Dinge in einem Durchgang zu erledigen.

  • Verschachtelung von Abfragen Ja, zu jedem Zeitpunkt können Sie Klammern in Ihre gesamte Abfrage einfügen und sie als Unterabfrage verwenden, auf der Sie Quelldaten und berechnete Felder gleichermaßen bearbeiten können. Aber du kannst das nur so oft machen, bevor es hässlich wird. Sehr hässlich.

  • Wiederholungscode Was ist der größte Wert von 3 langen (CASE ... ELSE ... END) Anweisungen? Das wird lesbar sein!

    • Sagen Sie Ihren Kunden, die verdammten Dinge selbst zu berechnen.

Habe ich etwas verpasst? Wahrscheinlich, also zögern Sie nicht zu kommentieren. Aber hey, CROSS APPLY ist wie ein Glücksfall in solchen Situationen: Sie fügen einfach ein einfaches hinzu CROSS APPLY (select tbl.value + 1 as someFormula) as crossTblund voilà! Ihr neues Feld ist nun praktisch so, wie es in Ihren Quelldaten schon immer vorhanden war.

Werte, die durch CROSS APPLY eingeführt werden, können ...

  • verwendet werden, um ein oder mehrere berechnete Felder zu erstellen, ohne dem Mix Leistung, Komplexität oder Lesbarkeitsprobleme hinzuzufügen
  • Wie bei JOINs können mehrere nachfolgende CROSS APPLY-Anweisungen auf sich selbst verweisen: CROSS APPLY (select crossTbl.someFormula + 1 as someMoreFormula) as crossTbl2
  • Sie können Werte verwenden, die bei nachfolgenden JOIN-Bedingungen durch ein CROSS APPLY eingeführt werden
  • Als Bonus gibt es den Tabellenfunktionsaspekt

Dang, es gibt nichts, was sie nicht tun können!


33
2018-06-11 07:49



Cross-Apply funktioniert auch gut mit einem XML-Feld. Wenn Sie Knotenwerte in Kombination mit anderen Feldern auswählen möchten.

Zum Beispiel, wenn Sie eine Tabelle haben, die etwas XML enthält

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

Verwenden der Abfrage

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

Gibt ein Ergebnis zurück

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

12
2018-02-01 18:52



Ich denke, es sollte Lesbarkeit sein;)

CROSS APPLY ist etwas einzigartig für Leute, die lesen, um ihnen zu sagen, dass eine UDF verwendet wird, die auf jede Zeile aus der Tabelle auf der linken Seite angewendet wird.

Natürlich gibt es andere Einschränkungen, bei denen ein CROSS APPLY besser benutzt wird als JOIN, was andere Freunde oben gepostet haben.


5
2017-07-16 18:12



Cross apply kann verwendet werden, um Unterabfragen zu ersetzen, wo Sie eine Spalte der Unterabfrage benötigen

Unterabfrage

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

Hier kann ich die Spalten der Unternehmenstabelle nicht auswählen Also, mit Kreuz anwenden

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



Hier ist ein Artikel, der alles erklärt, mit ihrer Leistungsdifferenz und Verwendung über JOINS.

SQL Server CROSS APPLY und OUTER APPLY über Joins

Wie in diesem Artikel vorgeschlagen, gibt es keinen Leistungsunterschied zwischen ihnen für normale Join-Vorgänge (INNER AND CROSS).

enter image description here

Der Verwendungsunterschied kommt an, wenn Sie eine Abfrage wie folgt durchführen müssen:

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)

Das heißt, wenn Sie sich mit der Funktion identifizieren müssen. Dies kann nicht mit INNER JOIN durchgeführt werden, was zu einem Fehler führen würde "Die mehrteilige Kennung" D.DepartmentID "konnte nicht gebunden werden." Hier wird der Wert an die Funktion übergeben, wenn jede Zeile gelesen wird. Klingt cool für mich. :)


3
2018-03-21 04:44



Nun, ich bin mir nicht sicher, ob dies als Grund für die Verwendung von Cross Apply versus Inner Join gilt, aber diese Frage wurde für mich in einem Forum Post mit Cross Apply beantwortet, also bin ich mir nicht sicher, ob es eine Methode mit Innerem Join gibt:

Create PROCEDURE [dbo].[Message_FindHighestMatches]

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

WIE START

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

ENDE


2
2018-03-08 19:51



Dies ist vielleicht eine alte Frage, aber ich liebe immer noch die Kraft von CROSS APPLY, um die Wiederverwendung von Logik zu vereinfachen und einen "Verkettungs" -Mechanismus für Ergebnisse bereitzustellen.

Ich habe unten eine SQL-Fiddle vorgestellt, die ein einfaches Beispiel dafür zeigt, wie Sie mit CROSS APPLY komplexe logische Operationen auf Ihrem Datenbestand durchführen können, ohne dass die Dinge unordentlich werden. Es ist nicht schwer, von hier aus komplexere Berechnungen zu extrapolieren.

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


0
2017-10-06 01:41