Kiedy powinienem zastosować krzyżowanie na złączeniu wewnętrznym?

Jaki jest główny cel korzystania z aplikacji CROSS APPLY ?

Przeczytałem (niejasno przez posty w Internecie), które cross applymogą być bardziej wydajne przy wybieraniu dużych zestawów danych, jeśli partycjonujesz. (Przychodzi mi na myśl stronicowanie)

Wiem również, że CROSS APPLYnie wymaga UDF jako prawej tabeli.

W większości INNER JOINzapytań (relacje jeden do wielu) mogłem przepisać je na potrzeby używania CROSS APPLY, ale zawsze dają mi one równoważne plany wykonania.

Czy ktoś może mi podać dobry przykład, kiedy CROSS APPLYrobi różnicę w tych przypadkach, w których również INNER JOINbędzie działać?

Edytować:

Oto prosty przykład, w którym plany wykonania są dokładnie takie same. (Pokaż mi, gdzie się różnią i gdzie cross applyjest szybszy / bardziej wydajny)

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

Czy ktoś może mi podać dobry przykład, kiedy CROSS APPLY robi różnicę w tych przypadkach, w których INNER JOIN również będzie działał?

Zobacz artykuł na moim blogu, aby uzyskać szczegółowe porównanie wydajności:

• INNER JOIN vs. CROSS APPLY

CROSS APPLYdziała lepiej na rzeczach, które nie mają prostego JOINstanu.

Ten wybiera 3ostatnie rekordy t2dla każdego rekordu z 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

Nie można go łatwo sformułować z INNER JOINwarunkiem.

Prawdopodobnie możesz zrobić coś takiego za pomocą CTEfunkcji okna i okna:

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

, ale jest to mniej czytelne i prawdopodobnie mniej wydajne.

Aktualizacja:

Właśnie sprawdziłem.

masterjest tabelą zawierającą 20,000,000rekordy z PRIMARY KEYwłączonym id.

To zapytanie:

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

działa przez prawie 30sekundy, podczas gdy ten:

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

jest natychmiastowy.

cross applyczasami pozwala ci robić rzeczy, z którymi nie możesz zrobić inner join.

Przykład (błąd składniowy):

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

Jest to błąd składniowy , ponieważ w połączeniu z inner joinfunkcjami tabel może przyjmować tylko zmienne lub stałe jako parametry. (Tj. Parametr funkcji tabeli nie może zależeć od kolumny innej tabeli).

Jednak:

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

To jest legalne.

Edycja: lub alternatywnie krótsza składnia: (autor: ErikE)

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

Edytować:

Uwaga: Informix 12.10 xC2 + ma boczne tabele pochodne, a Postgresql (9.3+) ma boczne podkwerendy, których można użyć w podobny sposób.

Rozważ, że masz dwie tabele.

STÓŁ MASTER

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

TABELA SZCZEGÓŁÓW

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

Istnieje wiele sytuacji, w których trzeba wymienić INNER JOINz CROSS APPLY.

1. Połącz dwie tabele na podstawie TOP nwyników

Zastanów się, czy musimy wybrać Idi Nameod Masteri ostatnie dwie daty dla każdej Idz 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

• FIDDLE SQL

Powyższe zapytanie generuje następujący wynik.

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

Zobacz, wygenerował wyniki dla dwóch ostatnich dat z dwiema ostatnimi datami, Ida następnie dołączył te rekordy tylko w zewnętrznym zapytaniu włączonym Id, co jest błędne. Powinno to zwracać zarówno Ids1, jak i 2, ale zwróciło tylko 1, ponieważ 1 ma dwie ostatnie daty. Aby to osiągnąć, musimy użyć 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

• FIDDLE SQL

i tworzy następujący wynik.

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

Oto jak to działa. Kwerenda wewnątrz CROSS APPLYmoże odwoływać się do zewnętrznej tabeli, gdzie INNER JOINnie może tego zrobić (generuje błąd kompilacji). Po znalezieniu dwóch ostatnich dat łączenie odbywa się wewnątrz, CROSS APPLYtj WHERE M.ID=D.ID.

2. Kiedy potrzebujemy INNER JOINfunkcjonalności za pomocą funkcji.

CROSS APPLYmoże być użyty jako zamiennik, INNER JOINkiedy musimy uzyskać wynik z Mastertabeli i function.

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

A oto funkcja

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

• FIDDLE SQL

który wygenerował następujący wynik

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

DODATKOWA KORZYŚĆ ZASTOSOWANIA KRZYŻOWEGO

APPLYmoże być stosowany jako zamiennik UNPIVOT. Albo CROSS APPLYalboOUTER APPLY można stosować tutaj, które są wymienne.

Rozważ, że masz poniższą tabelę (o nazwie 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

Zapytanie jest poniżej.

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

• FIDDLE SQL

co przynosi wynik

  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

Wydaje mi się, że CROSS APPLY może wypełnić pewną lukę podczas pracy z polami obliczeniowymi w złożonych / zagnieżdżonych zapytaniach i uczynić je prostszymi i bardziej czytelnymi.

Prosty przykład: masz DoB i chcesz przedstawić wiele pól związanych z wiekiem, które będą również opierać się na innych źródłach danych (takich jak zatrudnienie), takich jak Age, AgeGroup, AgeAtHiring, MinimumRetirementDate itp. Do wykorzystania w aplikacji użytkownika końcowego (Na przykład tabele przestawne programu Excel).

Opcje są ograniczone i rzadko eleganckie:

• Podkwerendy JOIN nie mogą wprowadzać nowych wartości w zbiorze danych na podstawie danych w kwerendzie nadrzędnym (musi stać samodzielnie).

• UDF są schludne, ale powolne, ponieważ mają tendencję do zapobiegania równoległym operacjom. A bycie odrębnym bytem może być dobrą (mniej kodu) lub złą (gdzie jest kod) rzeczą.

• Tabele połączeń Czasami mogą działać, ale wkrótce dołączasz do zapytań z tonami UNII. Wielki bałagan.

• Utwórz kolejny widok jednego celu, zakładając, że twoje obliczenia nie wymagają danych uzyskanych w połowie twojego głównego zapytania.

• Tabele pośredniczące. Tak ... to zwykle działa, a często jest to dobra opcja, ponieważ mogą być indeksowane i szybkie, ale wydajność może również spaść ze względu na to, że instrukcje UPDATE nie są równoległe i nie pozwalają na kaskadowe formuły (ponowne użycie wyników) w celu aktualizacji kilku pól w to samo oświadczenie. A czasem wolisz robić rzeczy za jednym razem.

• Zagnieżdżanie zapytań. Tak, w dowolnym momencie możesz umieścić nawias w całym zapytaniu i użyć go jako podzapytania, na którym możesz manipulować danymi źródłowymi i polami obliczeniowymi. Ale możesz to zrobić tylko tyle, zanim stanie się brzydka. Bardzo brzydki.

• Powtarzający się kod. Jaka jest największa wartość 3 długich instrukcji (CASE ... ELSE ... END)? To będzie czytelne!

  • Powiedz swoim klientom, aby sami obliczyli te cholerne rzeczy.

Przegapiłem coś? Prawdopodobnie więc skomentuj. Ale hej, APLIKACJA KRZYŻOWA jest jak wybawienie w takich sytuacjach: wystarczy dodać prostyCROSS APPLY (select tbl.value + 1 as someFormula) as crossTbl i voila! Twoje nowe pole jest teraz gotowe do użycia praktycznie tak, jak zawsze było w danych źródłowych.

Wartości wprowadzone za pomocą aplikacji CROSS APPLY mogą ...

• być używane do tworzenia jednego lub wielu pól obliczeniowych bez dodawania problemów z wydajnością, złożonością lub czytelnością do miksu
• podobnie jak w przypadku JOIN, kilka kolejnych instrukcji CROSS APPLY może odnosić się do siebie: CROSS APPLY (select crossTbl.someFormula + 1 as someMoreFormula) as crossTbl2
• możesz użyć wartości wprowadzonych przez APLIKACJĘ KRZYŻOWĄ w kolejnych warunkach JOIN
• Jako bonus istnieje aspekt funkcji cenionej w tabeli

Dang, nic nie mogą zrobić!

Cross Apply działa również dobrze z polem XML. Jeśli chcesz wybrać wartości węzłów w połączeniu z innymi polami.

Na przykład, jeśli masz tabelę zawierającą trochę xml

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

Za pomocą zapytania

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

Zwróci wynik

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

Odpowiedź na to pytanie jest już bardzo dobra pod względem technicznym, ale pozwólcie, że podam konkretny przykład tego, jak jest niezwykle przydatna:

Powiedzmy, że masz dwie tabele: Klient i Zamówienie. Klienci mają wiele zamówień.

Chcę utworzyć widok, który poda mi szczegółowe informacje o klientach i ostatnim złożonym przez nich zamówieniu. Z JOINS wymagałoby to samodzielnego łączenia i agregacji, co nie jest ładne. Ale dzięki Cross Apply jest to bardzo łatwe:

SELECT *
FROM Customer
CROSS APPLY (
  SELECT TOP 1 *
  FROM Order
  WHERE Order.CustomerId = Customer.CustomerId
  ORDER BY OrderDate DESC
) T

Zastosuj krzyżowo można zastosować do zastąpienia podzapytań, w których potrzebna jest kolumna podzapytania

podzapytanie

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

tutaj nie będę mógł wybrać kolumn tabeli firmowej, więc używając krzyżowania

select P.*,T.CompanyName
from Person p
cross apply (
    select *
    from Company C
    where p.companyid = c.companyId and c.CompanyName like '%yyy%'
) T

Chyba powinna to być czytelność;)

Aplikacja KRZYŻOWA będzie w pewnym sensie wyjątkowa dla osób czytających, aby powiedzieć im, że używany jest UDF, który zostanie zastosowany do każdego wiersza z tabeli po lewej stronie.

Oczywiście istnieją inne ograniczenia, w których KRZYŻ APLIKACJA jest lepiej używany niż DOŁĄCZ, które inni przyjaciele napisali powyżej.

Oto artykuł, który wyjaśnia to wszystko, z ich różnicą wydajności i wykorzystaniem w stosunku do JOINS.

SQL Server ZASTOSUJ KRZYŻ i ZEWNĘTRZNIE ZASTOSUJ przez POŁĄCZENIA

Jak zasugerowano w tym artykule, nie ma różnicy w wydajności między nimi w przypadku normalnych operacji łączenia (WEWNĘTRZNE I KRZYŻOWE).

Różnica w użyciu pojawia się, gdy trzeba wykonać takie zapytanie:

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)

To znaczy, kiedy musisz odnosić się do funkcji. Nie można tego zrobić za pomocą INNER JOIN, co dałoby błąd „Nie można powiązać wieloczęściowego identyfikatora„ D.DepartmentID ”.” Tutaj wartość jest przekazywana do funkcji podczas odczytu każdego wiersza. Brzmi dla mnie fajnie. :)

Cóż, nie jestem pewien, czy to kwalifikuje się jako powód do zastosowania krzyżowania w porównaniu z łączeniem wewnętrznym, ale na to pytanie odpowiedziano mi w poście na forum za pomocą krzyżowania, więc nie jestem pewien, czy istnieje równoważna metoda przy użyciu łączenia wewnętrznego:

Create PROCEDURE [dbo].[Message_FindHighestMatches]

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

JAK POCZĄTEK

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

KONIEC

Istotą operatora APPLY jest umożliwienie korelacji między lewą i prawą stroną operatora w klauzuli FROM.

W przeciwieństwie do JOIN korelacja między danymi wejściowymi jest niedozwolona.

Mówiąc o korelacji w operatorze APPLY, mam na myśli po prawej stronie możemy umieścić:

• tabela pochodna - jako skorelowane podkwerenda z aliasem
• funkcja ceniona w tabeli - widok koncepcyjny z parametrami, w którym parametr może odnosić się do lewej strony

Oba mogą zwracać wiele kolumn i wierszy.

To może stare pytanie, ale nadal uwielbiam moc aplikacji CROSS APPLY, która upraszcza ponowne użycie logiki i zapewnia mechanizm „łączenia łańcuchów” wyników.

Poniżej przedstawiłem skrzypce SQL, która pokazuje prosty przykład użycia aplikacji CROSS APPLY do wykonywania złożonych operacji logicznych na zbiorze danych, bez bałaganu. Nie jest trudno ekstrapolować stąd bardziej złożone obliczenia.

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

Podczas gdy większość zapytań, które wykorzystują CROSS APPLY, można przepisać za pomocą INNER JOIN, CROSS APPLY może dać lepszy plan wykonania i lepszą wydajność, ponieważ może ograniczyć zestaw dołączania jeszcze przed przyłączeniem.

Skradziony stąd