sql - इनर ज्वाइन पर क्रॉस एप्लाई कब लगाना चाहिए?
sql-server performance (8)
क्रॉस आवेदन का उपयोग करने का मुख्य उद्देश्य क्या है?
मैंने पढ़ा है (अस्पष्ट रूप से, इंटरनेट पर पोस्ट के माध्यम से) कि अगर आप विभाजन कर रहे हैं तो बड़े डेटा सेट पर चयन करते समय cross apply
अधिक कुशल हो सकता है। (पेजिंग दिमाग में आता है)
मुझे यह भी पता है कि CROSS APPLY
को यूडीएफ को दाएं-टेबल के रूप में आवश्यकता नहीं है।
अधिकांश INNER JOIN
प्रश्नों में (एक से कई रिश्ते), मैं उन्हें CROSS APPLY
का उपयोग करने के लिए फिर से लिख सकता हूं, लेकिन वे हमेशा मुझे बराबर निष्पादन योजनाएं देते हैं।
क्या कोई मुझे एक अच्छा उदाहरण दे सकता है जब CROSS APPLY
उन मामलों में एक फर्क पड़ता है जहां INNER JOIN
भी काम करेगा?
संपादित करें:
यहां एक मामूली उदाहरण है, जहां निष्पादन योजनाएं बिल्कुल वही हैं। (मुझे एक दिखाएं जहां वे भिन्न होते हैं और जहां cross apply
है तेज़ / अधिक कुशल होता है)
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
क्या कोई मुझे एक अच्छा उदाहरण दे सकता है जब क्रॉस आवेदन उन मामलों में एक फर्क पड़ता है जहां इनर जॉइन भी काम करेगा?
विस्तृत प्रदर्शन तुलना के लिए मेरे ब्लॉग में आलेख देखें:
CROSS APPLY
उन चीजों पर बेहतर काम करता है जिनमें कोई साधारण JOIN
स्थिति नहीं है।
यह t1
से प्रत्येक रिकॉर्ड के लिए t1
2 से 3
अंतिम रिकॉर्ड का चयन करता है:
SELECT t1.*, t2o.*
FROM t1
CROSS APPLY
(
SELECT TOP 3 *
FROM t2
WHERE t2.t1_id = t1.id
ORDER BY
t2.rank DESC
) t2o
इसे आसानी से INNER JOIN
स्थिति के साथ तैयार नहीं किया जा सकता है।
आप संभवतः CTE
और विंडो फ़ंक्शन का उपयोग करके ऐसा कुछ कर सकते हैं:
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
, लेकिन यह कम पठनीय और शायद कम कुशल है।
अद्यतन करें:
बस चेक किया गया।
master
id
पर PRIMARY KEY
साथ लगभग 20,000,000
रिकॉर्ड की एक तालिका है।
यह प्रश्न:
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
लगभग 30
सेकंड तक चलता है, जबकि यह एक:
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
तत्काल है
APPLY ऑपरेटर का सार ऑपरेशन के बाएं और दाएं किनारे के बीच सेक्शन में FROM में सहसंबंध की अनुमति देना है।
जॉइन के विपरीत, इनपुट के बीच सहसंबंध की अनुमति नहीं है।
ऑपरेटर ऑपरेटर में सहसंबंध के बारे में बोलते हुए, मेरा मतलब है कि दाएं हाथ की तरफ हम रख सकते हैं:
- एक व्युत्पन्न तालिका - एक उपनाम के साथ एक सहसंबंधित subquery के रूप में
- एक टेबल मूल्यवान फ़ंक्शन - पैरामीटर के साथ एक वैचारिक दृश्य, जहां पैरामीटर बाईं ओर संदर्भित कर सकता है
दोनों एकाधिक कॉलम और पंक्तियां वापस कर सकते हैं।
क्रॉस एक एक्सएमएल फ़ील्ड के साथ अच्छी तरह से काम करता है। यदि आप अन्य फ़ील्ड के साथ संयोजन में नोड मानों का चयन करना चाहते हैं।
उदाहरण के लिए, यदि आपके पास कुछ एक्सएमएल युक्त टेबल है
<root> <subnode1> <some_node value="1" /> <some_node value="2" /> <some_node value="3" /> <some_node value="4" /> </subnode1> </root>
क्वेरी का प्रयोग करना
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
परिणाम लौटाएगा
xt_id root_attribute_value node_attribute_value lt_name
----------------------------------------------------------------------
1 test1 1 Benefits
1 test1 4 FINRPTCOMPANY
क्रॉस लागू का उपयोग subquery के स्थान पर करने के लिए किया जा सकता है जहां आपको सबक्वायरी के कॉलम की आवश्यकता होती है
सबक्वेरी
select * from person p where
p.companyId in(select c.companyId from company c where c.companyname like '%yyy%')
यहां मैं क्रॉस लागू करने का उपयोग करके कंपनी टेबल के कॉलम का चयन नहीं कर पाऊंगा
select P.*,T.CompanyName
from Person p
cross apply (
select *
from Company C
where p.companyid = c.companyId and c.CompanyName like '%yyy%'
) T
मान लें कि आपके पास दो टेबल हैं।
मास्टर टेबल
x------x--------------------x
| Id | Name |
x------x--------------------x
| 1 | A |
| 2 | B |
| 3 | C |
x------x--------------------x
विवरण तालिका
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
ऐसी कई स्थितियां हैं जहां हमें CROSS APPLY
साथ INNER JOIN
को प्रतिस्थापित करने की आवश्यकता है।
1. TOP n
परिणामों के आधार पर दो टेबल में शामिल हों
गौर करें कि हमें Master
से Id
और Name
चुनने की आवश्यकता है और Details table
से प्रत्येक Id
लिए अंतिम दो तिथियां हैं।
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
उपर्युक्त क्वेरी निम्नलिखित परिणाम उत्पन्न करती है।
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
देखें, पिछले दो तिथियों के Id
साथ पिछले दो तिथियों के लिए यह परिणाम उत्पन्न हुए और फिर इन रिकॉर्ड्स में केवल Id
पर बाहरी क्वेरी में शामिल हो गए, जो गलत है। इसे पूरा करने के लिए, हमें 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
और निम्नलिखित परिणाम बनाता है।
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
यहां देखिए यह कैसे काम करता है। CROSS APPLY
अंदर की क्वेरी बाहरी तालिका का संदर्भ दे सकती है, जहां INNER JOIN
ऐसा नहीं कर सकता है (यह संकलन त्रुटि फेंकता है)। पिछली दो तिथियों को WHERE M.ID=D.ID
, CROSS APPLY
अंदर शामिल होना यानी, WHERE M.ID=D.ID
।
2. जब हमें कार्यों का उपयोग करके INNER JOIN
कार्यक्षमता की आवश्यकता होती है।
CROSS APPLY
को INNER JOIN
साथ प्रतिस्थापन के रूप में उपयोग किया जा सकता है जब हमें Master
टेबल और function
से परिणाम प्राप्त करने की आवश्यकता होती है।
SELECT M.ID,M.NAME,C.PERIOD,C.QTY
FROM MASTER M
CROSS APPLY dbo.FnGetQty(M.ID) C
और यहाँ समारोह है
CREATE FUNCTION FnGetQty
(
@Id INT
)
RETURNS TABLE
AS
RETURN
(
SELECT ID,PERIOD,QTY
FROM DETAILS
WHERE [email protected]
)
जिसने निम्नलिखित परिणाम उत्पन्न किए
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
क्रॉस आवेदन के अतिरिक्त लाभ
APPLY
UNPIVOT
प्रतिस्थापन के रूप में इस्तेमाल किया जा सकता है। या तो CROSS APPLY
या OUTER APPLY
का उपयोग यहां किया जा सकता है, जो विनिमयशील हैं।
MYTABLE
कि आपके पास निम्न तालिका है ( 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
क्वेरी नीचे है।
SELECT DISTINCT ID,DATES
FROM MYTABLE
CROSS APPLY(VALUES (FROMDATE),(TODATE))
COLUMNNAMES(DATES)
जो आपको परिणाम लाता है
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
मुझे लगता है कि यह पठनीयता होना चाहिए;)
क्रॉस आवेदन उन लोगों को बताने के लिए कुछ अद्वितीय होगा जो उन्हें बताते हैं कि एक यूडीएफ का उपयोग किया जा रहा है जो बाईं ओर तालिका से प्रत्येक पंक्ति पर लागू किया जाएगा।
संभोग, ऐसी अन्य सीमाएं हैं जहां एक क्रॉस आवेदन जॉइन से बेहतर उपयोग किया जाता है जो अन्य मित्रों ने ऊपर पोस्ट किया है।
यहां एक उदाहरण दिया गया है जब क्रॉस आवेदन प्रदर्शन के साथ एक बड़ा अंतर बनाता है:
क्रॉस का उपयोग करने के लिए स्थिति के आधार पर जुड़ने के लिए आवेदन करें
ध्यान दें कि आंतरिक जोड़ों को बदलने के अलावा आप स्केलर यूडीएफ को शामिल करने के लिए प्रदर्शन जुर्माना के भुगतान के बिना छंटनी तिथियों जैसे कोड का पुन: उपयोग कर सकते हैं, उदाहरण के लिए: इनलाइन यूडीएफ के साथ महीने के तीसरे बुधवार की गणना करना
यहां एक लेख है जो जॉइन पर उनके प्रदर्शन अंतर और उपयोग के साथ, सभी को समझाता है।
एसक्यूएल सर्वर क्रॉस आवेदन और संयुक्त आवेदन पर लागू आवेदन
जैसा कि इस आलेख में सुझाया गया है, उनके बीच सामान्य जुड़ने के संचालन (INNER और CROSS) के बीच कोई प्रदर्शन अंतर नहीं है।
जब आप इस तरह की कोई क्वेरी करना चाहते हैं तो उपयोग अंतर आता है:
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)
यही है, जब आपको कार्य से संबंधित होना है। यह इनर जॉइन का उपयोग करके नहीं किया जा सकता है, जो आपको त्रुटि देगा "बहु-भाग पहचानकर्ता" डी। डिपार्टमेंट आईडी "बाध्य नहीं हो सका।" यहां मूल्य को फ़ंक्शन पर पास किया जाता है क्योंकि प्रत्येक पंक्ति पढ़ी जाती है। मुझे अच्छा लगता है। :)