sql - इनर ज्वाइन पर क्रॉस एप्लाई कब लगाना चाहिए?




sql-server performance tsql cross-apply (11)

क्रॉस आवेदन का उपयोग करने का मुख्य उद्देश्य क्या है?

मैंने पढ़ा है (अस्पष्ट रूप से, इंटरनेट पर पोस्ट के माध्यम से) कि अगर आप विभाजन कर रहे हैं तो बड़े डेटा सेट पर चयन करते समय 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

Answers

APPLY ऑपरेटर का सार ऑपरेशन के बाएं और दाएं किनारे के बीच सेक्शन में FROM में सहसंबंध की अनुमति देना है।

जॉइन के विपरीत, इनपुट के बीच सहसंबंध की अनुमति नहीं है।

ऑपरेटर ऑपरेटर में सहसंबंध के बारे में बोलते हुए, मेरा मतलब है कि दाएं हाथ की तरफ हम रख सकते हैं:

  • एक व्युत्पन्न तालिका - एक उपनाम के साथ एक सहसंबंधित subquery के रूप में
  • एक टेबल मूल्यवान फ़ंक्शन - पैरामीटर के साथ एक वैचारिक दृश्य, जहां पैरामीटर बाईं ओर संदर्भित कर सकता है

दोनों एकाधिक कॉलम और पंक्तियां वापस कर सकते हैं।


खैर मुझे यकीन नहीं है कि यह क्रॉस लागू बनाम इनर जॉइन का उपयोग करने के कारण के रूप में योग्यता प्राप्त करता है, लेकिन क्रॉस एप्लिकेशन का उपयोग करके फोरम पोस्ट में इस प्रश्न का उत्तर दिया गया था, इसलिए मुझे यकीन नहीं है कि इनर जॉइन का उपयोग करके एक समान विधि है या नहीं:

Create PROCEDURE [dbo].[Message_FindHighestMatches]

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

के रूप में BEGIN

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

समाप्त


मान लें कि आपके पास दो टेबल हैं।

मास्टर टेबल

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

क्रॉस लागू का उपयोग 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

यहां एक उदाहरण दिया गया है जब क्रॉस आवेदन प्रदर्शन के साथ एक बड़ा अंतर बनाता है:

क्रॉस का उपयोग करने के लिए स्थिति के आधार पर जुड़ने के लिए आवेदन करें

ध्यान दें कि आंतरिक जोड़ों को बदलने के अलावा आप स्केलर यूडीएफ को शामिल करने के लिए प्रदर्शन जुर्माना के भुगतान के बिना छंटनी तिथियों जैसे कोड का पुन: उपयोग कर सकते हैं, उदाहरण के लिए: इनलाइन यूडीएफ के साथ महीने के तीसरे बुधवार की गणना करना


cross apply कभी-कभी आपको उन चीजों को करने में सक्षम बनाता है जिन्हें आप inner join साथ नहीं कर सकते हैं।

उदाहरण (एक वाक्यविन्यास त्रुटि):

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

यह एक वाक्यविन्यास त्रुटि है , क्योंकि, जब inner join साथ प्रयोग किया जाता है, तो तालिका कार्य केवल पैरामीटर के रूप में चर या स्थिरांक ले सकते हैं। (यानी, तालिका फ़ंक्शन पैरामीटर किसी अन्य तालिका के कॉलम पर निर्भर नहीं हो सकता है।)

तथापि:

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

यह कानूनी है।

संपादित करें: या वैकल्पिक रूप से, छोटे वाक्यविन्यास: (एरिक द्वारा)

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

संपादित करें:

नोट: इनफॉर्मिक्स 12.10 xC2 + में लेटरल व्युत्पन्न टेबल्स और पोस्टग्रेस्क्ल (9.3+) में लेटरल सबक्विरीज़ हैं जिनका उपयोग इसी तरह के प्रभाव में किया जा सकता है।


यह शायद एक पुराना सवाल है, लेकिन मुझे अभी भी तर्क के पुन: उपयोग को सरल बनाने और परिणामों के लिए "श्रृंखला" तंत्र प्रदान करने के लिए क्रॉस आवेदन की शक्ति पसंद है।

मैंने नीचे एक एसक्यूएल फिल्ड प्रदान किया है जो कि एक सरल उदाहरण दिखाता है कि आप क्रॉस एप्पल का उपयोग अपने डेटा सेट पर जटिल लॉजिकल ऑपरेशंस करने के लिए कैसे कर सकते हैं बिना किसी गड़बड़ के। यहां अधिक जटिल गणनाओं से बाहर निकलना मुश्किल नहीं है।

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


क्रॉस एक एक्सएमएल फ़ील्ड के साथ अच्छी तरह से काम करता है। यदि आप अन्य फ़ील्ड के साथ संयोजन में नोड मानों का चयन करना चाहते हैं।

उदाहरण के लिए, यदि आपके पास कुछ एक्सएमएल युक्त टेबल है

<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

मुझे लगता है कि यह पठनीयता होना चाहिए;)

क्रॉस आवेदन उन लोगों को बताने के लिए कुछ अद्वितीय होगा जो उन्हें बताते हैं कि एक यूडीएफ का उपयोग किया जा रहा है जो बाईं ओर तालिका से प्रत्येक पंक्ति पर लागू किया जाएगा।

संभोग, ऐसी अन्य सीमाएं हैं जहां एक क्रॉस आवेदन जॉइन से बेहतर उपयोग किया जाता है जो अन्य मित्रों ने ऊपर पोस्ट किया है।


यहां एक लेख है जो जॉइन पर उनके प्रदर्शन अंतर और उपयोग के साथ, सभी को समझाता है।

एसक्यूएल सर्वर क्रॉस आवेदन और संयुक्त आवेदन पर लागू आवेदन

जैसा कि इस आलेख में सुझाया गया है, उनके बीच सामान्य जुड़ने के संचालन (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)

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


declare @p varbinary(max)
set @p = 0x
declare @local table (col text)

SELECT   @p = @p + 0x3B + CONVERT(varbinary(100), Email)
 FROM tbCarsList
 where email <> ''
 group by email
 order by email

 set @p = substring(@p, 2, 100000)

 insert @local values(cast(@p as varchar(max)))
 select DATALENGTH(col) as collen, col from @local

result collen > 8000, length col value is more than 8000 chars




sql sql-server performance tsql cross-apply