Pertanyaan Kapan saya harus menggunakan Cross Apply melalui Inner Join?


Apa tujuan utama penggunaan CROSS APPLY?

Saya telah membaca (secara samar-samar, melalui tulisan di Internet) itu cross apply bisa lebih efisien ketika memilih set data besar jika Anda mempartisi. (Pager muncul dalam pikiran)

Saya juga tahu itu CROSS APPLY  tidak membutuhkan UDF sebagai meja kanan.

Di sebagian besar INNER JOIN pertanyaan (hubungan satu ke banyak), saya dapat menulis ulang untuk digunakan CROSS APPLY, tetapi mereka selalu memberi saya rencana eksekusi yang setara.

Adakah yang bisa memberi saya contoh yang baik tentang kapan CROSS APPLY membuat perbedaan dalam kasus-kasus di mana INNER JOIN akan bekerja juga?


Edit:

Berikut contoh sepele, di mana rencana pelaksanaannya persis sama. (Tunjukkan pada saya di mana mereka berbeda dan di mana cross apply lebih cepat / lebih efisien)

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


asal


Jawaban:


Adakah yang bisa memberi saya contoh bagus ketika CROSS APPLY membuat perbedaan dalam kasus-kasus di mana INNER JOIN akan berfungsi juga?

Lihat artikel di blog saya untuk perbandingan kinerja terperinci:

CROSS APPLY bekerja lebih baik pada hal-hal yang tidak sederhana JOIN kondisi.

Yang ini memilih 3 catatan terakhir dari t2 untuk setiap rekaman dari 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

Ini tidak dapat dengan mudah diformulasikan dengan INNER JOIN kondisi.

Anda mungkin bisa melakukan hal seperti itu CTEdan fungsi window:

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

, tetapi ini kurang bisa dibaca dan mungkin kurang efisien.

Memperbarui:

Baru saja diperiksa.

master adalah tabel tentang 20,000,000 rekaman dengan PRIMARY KEY di id.

Kueri ini:

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

berjalan hampir 30 detik, sementara yang ini:

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

instan.


570
2017-07-16 17:52



cross apply terkadang memungkinkan Anda melakukan hal-hal yang tidak dapat Anda lakukan inner join.

Contoh (kesalahan sintaks):

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

Ini adalah sebuah kesalahan sintaks, karena, bila digunakan dengan inner join, fungsi tabel hanya bisa diambil variabel atau konstanta sebagai parameter. (Yaitu, parameter fungsi tabel tidak dapat bergantung pada kolom tabel lain.)

Namun:

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

Ini legal.

Edit: Atau alternatifnya, sintaks yang lebih pendek: (oleh ErikE)

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

Edit:

catatan: Informix 12.10 xC2 + memiliki Tabel Berasal Lateral dan Postgresql (9.3+) memiliki Subqueri Lateral yang dapat digunakan untuk efek yang sama.


176
2018-03-03 12:28



Anggap Anda memiliki dua tabel.

TABEL MASTER

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

TABEL DETAIL

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

Ada banyak situasi di mana kita perlu mengganti INNER JOIN dengan CROSS APPLY.

1. Bergabung dengan dua tabel berdasarkan TOP n hasil

Pertimbangkan jika kita perlu memilih Id dan Name dari Master dan dua tanggal terakhir untuk masing-masing Id dari 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

Kueri di atas menghasilkan hasil sebagai berikut.

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

Lihat, itu menghasilkan hasil untuk dua tanggal terakhir dengan dua tanggal terakhir Id dan kemudian bergabung dengan catatan ini hanya di kueri luar Id, itu salah. Untuk mencapai ini, kita perlu menggunakannya 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

dan membentuk hasil sebagai berikut.

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

Begini cara kerjanya. Kueri di dalamnya CROSS APPLY dapat mereferensikan tabel luar, di mana INNER JOINtidak bisa melakukan ini (ini melempar kesalahan kompilasi). Ketika menemukan dua tanggal terakhir, bergabung dilakukan di dalam CROSS APPLY yaitu., WHERE M.ID=D.ID.

2. Saat kita membutuhkan INNER JOIN fungsionalitas menggunakan fungsi.

CROSS APPLY dapat digunakan sebagai pengganti INNER JOIN ketika kita perlu mendapatkan hasil Master meja dan a function.

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

Dan inilah fungsinya

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

yang menghasilkan hasil sebagai berikut

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

KEUNTUNGAN TAMBAHAN CROSS APPLY 

APPLY dapat digunakan sebagai pengganti UNPIVOT. Antara CROSS APPLY atau OUTER APPLY dapat digunakan di sini, yang dapat dipertukarkan.

Anggap saja Anda memiliki tabel di bawah ini (bernama 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

Kueri di bawah.

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

yang membawa Anda hasilnya

  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



di sini adalah contoh ketika CROSS APPLY membuat perbedaan besar dengan kinerja:

Menggunakan CROSS APPLY untuk mengoptimalkan gabungan pada kondisi BETWEEN

Perhatikan bahwa selain mengganti gabungan internal, Anda juga dapat menggunakan kembali kode seperti memotong tanggal tanpa membayar penalti kinerja untuk melibatkan UDF skalar, misalnya: Menghitung hari Rabu ketiga setiap bulan dengan UDF sebaris


37
2017-07-16 18:28



Tampaknya bagi saya bahwa CROSS APPLY dapat mengisi celah tertentu ketika bekerja dengan bidang terhitung dalam query yang kompleks / bersarang, dan membuatnya lebih sederhana dan lebih mudah dibaca.

Contoh sederhana: Anda memiliki DoB dan Anda ingin menyajikan beberapa bidang yang berkaitan dengan usia yang juga akan bergantung pada sumber data lain (seperti pekerjaan), seperti Umur, Umur Kelompok, AgeAtHiring, MinimumRetirementDate, dll. Untuk digunakan dalam aplikasi pengguna akhir Anda (Excel PivotTable, misalnya).

Pilihan terbatas dan jarang elegan:

  • JOIN subqueries tidak dapat memperkenalkan nilai-nilai baru dalam dataset berdasarkan data dalam query induk (harus berdiri sendiri).

  • UDFs rapi, tetapi lambat karena cenderung mencegah operasi paralel. Dan menjadi entitas yang terpisah dapat menjadi hal yang baik (kurang kode) atau buruk (di mana kode).

  • Tabel persimpangan. Terkadang mereka dapat bekerja, tetapi segera Anda bergabung dengan subqueri dengan banyak UNION. Kekacauan besar.

  • Buat satu lagi tampilan tujuan tunggal, dengan asumsi perhitungan Anda tidak memerlukan data yang diperoleh di tengah jalan melalui permintaan utama Anda.

  • Tabel perantara. Ya ... yang biasanya berfungsi, dan sering merupakan pilihan yang baik karena dapat diindeks dan cepat, tetapi kinerja juga dapat menurun karena pernyataan UPDATE tidak paralel dan tidak mengizinkan rumus kaskade (menggunakan kembali hasil) untuk memperbarui beberapa bidang dalam pernyataan yang sama. Dan kadang-kadang Anda lebih suka melakukan sesuatu dalam satu pass.

  • Pertanyaan bersarang. Ya, pada titik mana pun Anda dapat menempatkan tanda kurung pada seluruh kueri Anda dan menggunakannya sebagai subkueri tempat Anda dapat memanipulasi data sumber dan bidang terhitung sama. Tetapi Anda hanya dapat melakukan ini begitu banyak sebelum menjadi jelek. Sangat jelek.

  • Mengulangi kode. Berapa nilai terbesar dari 3 pernyataan panjang (KASUS ... LAIN ... AKHIR)? Itu bisa dibaca!

    • Beritahu klien Anda untuk menghitung sendiri barang-barang sialan itu.

Apakah saya melewatkan sesuatu? Mungkin, jadi jangan ragu untuk berkomentar. Tapi, hei, CROSS APPLY bagaikan dewa dalam situasi seperti itu: Anda tinggal menambahkan yang sederhana CROSS APPLY (select tbl.value + 1 as someFormula) as crossTbldan voila! Bidang baru Anda sekarang siap untuk digunakan secara praktis seperti itu selalu ada di sumber data Anda.

Nilai yang diperkenalkan melalui CROSS APPLY dapat ...

  • digunakan untuk membuat satu atau beberapa bidang terhitung tanpa menambahkan masalah kinerja, kompleksitas atau keterbacaan ke dalam campuran
  • seperti dengan GABUNG, beberapa pernyataan CROSS APPLY selanjutnya dapat merujuk kepada diri mereka sendiri: CROSS APPLY (select crossTbl.someFormula + 1 as someMoreFormula) as crossTbl2
  • Anda dapat menggunakan nilai yang diperkenalkan oleh CROSS APPLY dalam kondisi GABUNG berikutnya
  • Sebagai bonus, ada aspek fungsi Table-valued

Dang, tidak ada yang tidak bisa mereka lakukan!


33
2018-06-11 07:49



Cross apply bekerja dengan baik dengan bidang XML juga. Jika Anda ingin memilih nilai node dalam kombinasi dengan bidang lain.

Misalnya, jika Anda memiliki tabel yang berisi beberapa xml

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

Menggunakan kueri

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

Akan mengembalikan hasilnya

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

12
2018-02-01 18:52



Saya kira itu harus mudah dibaca;)

CROSS APPLY akan agak unik untuk orang yang membaca untuk memberi tahu mereka bahwa UDF sedang digunakan yang akan diterapkan ke setiap baris dari tabel di sebelah kiri.

Tentu saja, ada batasan lain di mana CROSS APPLY lebih baik digunakan daripada JOIN yang telah diposting teman-teman lain di atas.


5
2017-07-16 18:12



Berlaku lintas dapat digunakan untuk menggantikan subquery di mana Anda memerlukan kolom subquery

subquery

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

di sini saya tidak akan dapat memilih kolom tabel perusahaan jadi, menggunakan lintas berlaku

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



Berikut ini adalah artikel yang menjelaskan semuanya, dengan perbedaan kinerja dan penggunaannya atas JOINS.

SQL Server CROSS APPLY dan OUTER BERLAKU atas GABUNG

Seperti yang disarankan dalam artikel ini, tidak ada perbedaan kinerja antara keduanya untuk operasi penggabungan normal (INNER AND CROSS).

enter image description here

Perbedaan penggunaan muncul ketika Anda harus melakukan kueri seperti ini:

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)

Yaitu, ketika Anda harus berhubungan dengan fungsi. Ini tidak dapat dilakukan menggunakan INNER JOIN, yang akan memberi Anda kesalahan "Pengenal multi-bagian" D.DepartmentID "tidak dapat terikat." Di sini nilainya diteruskan ke fungsi karena setiap baris dibaca. Kedengarannya keren untukku. :)


3
2018-03-21 04:44



Yah saya tidak yakin apakah ini memenuhi syarat sebagai alasan untuk menggunakan Cross Apply versus Inner Join, tetapi pertanyaan ini telah dijawab untuk saya di Forum Post menggunakan Cross Apply, jadi saya tidak yakin apakah ada metode equalivent menggunakan Inner Join:

Create PROCEDURE [dbo].[Message_FindHighestMatches]

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

SEBAGAI MULAI

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

AKHIR


2
2018-03-08 19:51