sql - আমি অন্তরের যোগদান ক্রস ব্যবহার করা উচিত?




sql-server performance (9)

যখন ক্রোশ আবেদনটি সেই ক্ষেত্রে যে ক্ষেত্রে INNER JOIN কাজ করবে সেখানে কোনও পার্থক্য সৃষ্টি করে কেউ আমাকে একটি ভাল উদাহরণ দিতে পারে?

বিস্তারিত কর্মক্ষমতা তুলনা জন্য আমার ব্লগে নিবন্ধ দেখুন:

CROSS APPLY প্রয়োগ কোন সহজ JOIN শর্ত আছে যে জিনিস ভাল কাজ করে।

টি 3 থেকে প্রতিটি রেকর্ডের জন্য এটি টি 3 থেকে 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

তাত্ক্ষণিক

ক্রস প্রয়োগ ব্যবহার করার মূল উদ্দেশ্য কি?

আমি পড়েছি (অস্বাভাবিকভাবে, ইন্টারনেটে পোস্টের মাধ্যমে) যেগুলি cross apply পরে বৃহত্তর ডেটা সেট নির্বাচন করার সময় cross apply আরও কার্যকরী হতে পারে। (পেজিং মনে আসে)

আমি জানি যে CROSS APPLY একটি ডানদিকের হিসাবে একটি UDF প্রয়োজন হয় না।

সর্বাধিক 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

APPLY অপারেটরের সারাংশ FROM ধারাবাহিকের অপারেটরের বাম এবং ডানদিকের পারস্পরিক সম্পর্ককে অনুমোদন করা।

যোগদান বিপরীতে, ইনপুট মধ্যে পারস্পরিক সম্পর্ক অনুমোদিত নয়।

APPLY অপারেটরের সাথে সম্পর্কের কথা বলার অর্থ, ডানদিকে আমরা বলতে পারি:

  • একটি derived টেবিল - একটি উপনাম সঙ্গে একটি সম্পর্কযুক্ত subquery হিসাবে
  • একটি টেবিল মূল্যবান ফাংশন - পরামিতিগুলির সাথে একটি ধারণাগত দৃশ্য, যেখানে প্যারামিটারটি বাম পাশে উল্লেখ করতে পারে

উভয় একাধিক কলাম এবং সারি ফিরে আসতে পারেন।


আপনি দুটি টেবিল আছে বিবেচনা করুন।

মাস্টার টেবিল

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 করতে পারে না (এটি কম্পাইল ত্রুটিটি ছুড়ে ফেলে)। শেষ দুই তারিখ খুঁজে পাওয়ার সময়, CROSS APPLY WHERE M.ID=D.ID ভিতরে যোগদান করা হয়, WHERE M.ID=D.ID

2. যখন আমরা ফাংশন ব্যবহার করে INNER JOIN কার্যকারিতা প্রয়োজন।

Master টেবিল এবং একটি function থেকে ফলাফল পাওয়ার প্রয়োজন হলে CROSS APPLY INNER JOIN সাথে প্রতিস্থাপন হিসাবে ব্যবহার করা যেতে পারে।

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 ID[email protected]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-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

ক্রস এর অতিরিক্ত সুবিধা

UNPIVOT জন্য প্রতিস্থাপন হিসাবে ব্যবহার করা যেতে পারে। হয় CROSS APPLY প্রয়োগ বা OUTER APPLY এখানে ব্যবহার করা যেতে পারে, যা বিনিমেয় হয়।

আপনার নীচের টেবিলটি বিবেচনা করুন ( 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

আমার মনে হয় যে ক্রস প্রয়োগ জটিল / নীচের প্রশ্নগুলিতে গণিত ক্ষেত্রগুলির সাথে কাজ করার সময় একটি নির্দিষ্ট ফাঁক পূরণ করতে পারে এবং তাদের আরও সহজ এবং আরও পাঠযোগ্য করে তুলতে পারে।

সহজ উদাহরণ: আপনার একটি DOB আছে এবং আপনি একাধিক বয়স-সম্পর্কিত ক্ষেত্রগুলি উপস্থাপন করতে চান যা আপনার ডেটা উত্সগুলিতে (যেমন কর্মসংস্থান), বয়স, বয়স গোষ্ঠী, বয়স অ্যাকটিহায়ারিং, ন্যূনতম পুনরূদ্ধার তারিখ ইত্যাদির উপর নির্ভর করবে। (উদাহরণস্বরূপ, এক্সেল PivotTables)।

বিকল্প সীমিত এবং কদাচিৎ মার্জিত হয়:

  • JOIN subqueries মূল প্যারামিটারের ডেটা ভিত্তিক ডেটাসেটে নতুন মানগুলি পরিচয় করিয়ে দিতে পারে না (এটি নিজের উপরে থাকা উচিত)।

  • UDFs সুসজ্জিত, কিন্তু তারা সমান্তরাল অপারেশন প্রতিরোধ ঝোঁক ঝোঁক। এবং একটি পৃথক সত্তা হচ্ছে একটি ভাল (কম কোড) বা খারাপ (কোডটি কোথায়) জিনিস হতে পারে।

  • জংশন টেবিল। কখনও কখনও তারা কাজ করতে পারে, কিন্তু শীঘ্রই আপনি প্রচুর ইউএনওনিয়ান সহ subqueries যোগদান করছেন। সপ্তাহের দিন.

  • আপনার একাউন্টের আরেকটি একক উদ্দেশ্য তৈরি করুন, আপনার গণনাগুলি আপনার মূল প্রশ্নের মাধ্যমে মাঝামাঝি তথ্য প্রাপ্তির প্রয়োজনীয়তা অনুমান করে না।

  • মধ্যবর্তী টেবিল। হ্যাঁ ... সাধারণত এটি কাজ করে এবং প্রায়ই তারা একটি ভাল বিকল্প হিসাবে ইন্ডেক্স এবং দ্রুত হতে পারে তবে UPDATE বিবৃতি সমান্তরাল না থাকার কারণে কর্মক্ষমতাও ছাড়তে পারে এবং ক্যাসকেড সূত্র (পুনঃব্যবহারের ফলাফল) কে কয়েকটি ক্ষেত্র আপডেট করার অনুমতি দেয় না। একই বিবৃতি। এবং কখনও কখনও আপনি শুধু একটি পাসে জিনিস করতে চাই।

  • Nesting প্রশ্ন। হ্যাঁ কোনও পর্যায়ে আপনি আপনার সমগ্র ক্যোয়ারিতে বন্ধনী রাখতে পারেন এবং এটি উপসর্গ হিসাবে ব্যবহার করতে পারেন যার উপর আপনি উত্স ডেটা এবং গণনাকৃত ক্ষেত্রগুলি একইভাবে কাজে লাগাতে পারেন। কিন্তু আপনি কুৎসিত পায় আগে এটি শুধুমাত্র তাই করতে পারেন। খুব কুৎসিত.

  • কোড পুনরাবৃত্তি। 3 লম্বা (কেস ... ELSE ... END) বিবৃতি সর্বাধিক মান কি? যে পাঠযোগ্য হতে হবে!

    • নিজেদের ক্লান্ত জিনিসগুলি গণনা করতে আপনার ক্লায়েন্টকে বলুন।

আমি কি কিছু রেখে গেলাম? সম্ভবত, তাই মন্তব্য করতে বিনা দ্বিধায়। কিন্তু হেই, ক্রস প্রয়োগটি এমন পরিস্থিতিতে একটি CROSS APPLY (select tbl.value + 1 as someFormula) as crossTbl মতো: আপনি কেবল একটি সাধারণ CROSS APPLY (select tbl.value + 1 as someFormula) as crossTbl এবং CROSS APPLY (select tbl.value + 1 as someFormula) as crossTbl ! আপনার নতুন ক্ষেত্রটি এখন ব্যবহারের জন্য প্রস্তুত। কার্যত এটি আপনার উত্স ডেটাতে সর্বদা আছে।

ক্রস আবেদন মাধ্যমে চালু মান ...

  • মিশ্রণে পারফরম্যান্স, জটিলতা বা পঠনযোগ্যতা বিষয়গুলি যুক্ত না করে এক বা একাধিক গণনা ক্ষেত্র তৈরি করতে ব্যবহার করুন
  • JOINS এর মতো, পরবর্তী ক্রস প্রয়োগ বিবৃতিগুলি নিজেদেরকে উল্লেখ করতে পারে: CROSS APPLY (select crossTbl.someFormula + 1 as someMoreFormula) as crossTbl2
  • আপনি পরবর্তী JOIN শর্তগুলিতে একটি ক্রস প্রয়োগ দ্বারা প্রবর্তিত মান ব্যবহার করতে পারেন
  • একটি বোনাস হিসাবে, টেবিল মূল্যবান ফাংশন দৃষ্টিভঙ্গি আছে

ড্যাং, কিছুই তারা করতে পারে না!


এখানে একটি নিবন্ধ যা এটি ব্যাখ্যা করে, তাদের পারফরম্যান্স পার্থক্য এবং JOINS এর উপরে ব্যবহার করে।

এসকিউএল সার্ভার ক্রস প্রয়োগ এবং জুড়ে যোগ প্রয়োগ

এই নিবন্ধে প্রস্তাবিত হিসাবে, স্বাভাবিক যোগদান অপারেশনগুলির জন্য (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)

যে, আপনি ফাংশন সঙ্গে সম্পর্কযুক্ত আছে। এটি INNER JOIN ব্যবহার করে করা যাবে না, যা আপনাকে "মাল্টি-পার্ট আইডেন্টিফায়ার" ডি। ডিপমেন্টআইড "আবদ্ধ করতে পারে না।" এখানে প্রতিটি সারি পড়া হয় হিসাবে মান ফাংশন পাস করা হয়। আমার কাছে শীতল শব্দ। :)


এটি ইতিমধ্যেই প্রযুক্তিগতভাবে খুব ভাল উত্তর দেওয়া হয়েছে, তবে এটি কীভাবে এটি অত্যন্ত উপকারী হিসাবে আমার একটি কংক্রিট উদাহরণ দেয়:

চলুন আপনার দুটি টেবিল আছে, গ্রাহক এবং আদেশ। গ্রাহকদের অনেক আদেশ আছে।

আমি এমন একটি দৃশ্য তৈরি করতে চাই যা আমাকে গ্রাহকদের সম্পর্কে এবং তাদের সর্বশেষতম ক্রম সম্পর্কে বিবরণ দেয়। শুধু যোগদান সঙ্গে, এই কিছু স্ব-যোগদান এবং সমষ্টি যা সুন্দর হবে না। কিন্তু ক্রস প্রয়োগের সাথে এটির সুপার সহজ:

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

ক্রস প্রয়োগটি subquery এর প্রতিস্থাপন করার জন্য ব্যবহার করা যেতে পারে যেখানে আপনি উপধারার একটি কলাম প্রয়োজন

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

ক্রস প্রয়োগটি যখন পারফরম্যান্সের সাথে বিশাল পার্থক্য করে তখন এটি একটি উদাহরণ:

ক্রস ব্যবহার করে শর্তাবলীতে যোগদান অপ্টিমাইজ করার জন্য আবেদন করুন

অভ্যন্তরীণ যোগদানের প্রতিস্থাপন করার পাশাপাশি আপনি স্কলার ইউডিএফগুলি অন্তর্ভুক্ত করার জন্য পারফরম্যান্সের শাস্তি ছাড়াই ট্রানকেটিং তারিখগুলির মতো কোড পুনঃব্যবহার করতে পারেন, উদাহরণস্বরূপ: ইনলাইন UDF সহ মাসের তৃতীয় বুধবার গণনা করা


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 এক্সসি 2 + এর লেটারাল ডেরিভেড টেবিল এবং পোস্টগ্র্রেস্কল (9 .3+) এর লেটারাল সাবকুয়ারি রয়েছে যা একই রকম প্রভাবতে ব্যবহার করতে পারে।







cross-apply