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

क्या कोई मुझे एक अच्छा उदाहरण दे सकता है जब क्रॉस आवेदन उन मामलों में एक फर्क पड़ता है जहां इनर जॉइन भी काम करेगा?

विस्तृत प्रदर्शन तुलना के लिए मेरे ब्लॉग में आलेख देखें:

INNER JOIN बनाम CROSS APPLY

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)

यही है, जब आपको कार्य से संबंधित होना है। यह इनर जॉइन का उपयोग करके नहीं किया जा सकता है, जो आपको त्रुटि देगा "बहु-भाग पहचानकर्ता" डी। डिपार्टमेंट आईडी "बाध्य नहीं हो सका।" यहां मूल्य को फ़ंक्शन पर पास किया जाता है क्योंकि प्रत्येक पंक्ति पढ़ी जाती है। मुझे अच्छा लगता है। :)