धुरी

आधुनिक सूचना प्रणालियों में, निर्णय लेने की प्रक्रिया अक्सर समेकित सूचना पर आधारित होती है। व्यवहार में, जब व्यापार तर्क विकसित होता है जो इस तरह की जानकारी पर काम करता है, तो पंक्तियों को कॉलम में बदलना बहुत बार आवश्यक होता है।

T-SQL सिंटैक्स इस रूपांतरण को करने के लिए एक अलग PIVOT निर्माण प्रदान करता है। यह ध्यान देने योग्य है कि PIVOT अभी तक SQL Server 2000 में समर्थित नहीं था, इसलिए इसी तरह के कार्यों को कई CASE WHENs के माध्यम से हल किया गया था।

वास्तव में, मैंने PIVOT होने पर CASE का उल्लेख क्यों किया? वास्तव में, परिभाषा के अनुसार, PIVOT अधिक सुरुचिपूर्ण डिजाइन है और, तदनुसार, अधिक कुशल होना चाहिए।

आइए इसे व्यवहार में देखें ...

आइए एक तालिका बनाएं जिसमें कर्मचारियों को कार्यस्थलों में प्रवेश करने के बारे में जानकारी होगी।

IF OBJECT_ID('dbo.WorkOut', 'U') IS NOT NULL DROP TABLE dbo.WorkOut GO CREATE TABLE dbo.WorkOut ( DateOut DATETIME NOT NULL, EmployeeID INT NOT NULL, CONSTRAINT PK_WorkOut PRIMARY KEY CLUSTERED (DateOut, EmployeeID) ) GO 

और इसे परीक्षण डेटा के साथ भरें:

 INSERT INTO dbo.WorkOut (EmployeeID, DateOut) SELECT TOP 1500000 ao.[object_id], ao1.modify_date FROM sys.all_objects ao CROSS JOIN sys.all_objects ao1 

अगला, हम एक PIVOT अनुरोध लिखेंगे जो प्रत्येक कर्मचारी के लिए दिन से बाहर निकलने की संख्या लौटाएगा:

 SELECT * FROM ( SELECT EmployeeID , [WeekDay] = DATENAME(WEEKDAY, DateOut) FROM dbo.WorkOut ) t PIVOT ( COUNT([WeekDay]) FOR [WeekDay] IN ( Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday ) ) p 

अनुरोध निष्पादित करते समय, हमें निम्नलिखित योजना और लीड समय मिलेगा:



SQL सर्वर निष्पादन समय:
सीपीयू समय = 5662 एमएस, बीता हुआ समय = 8075 एमएस।

योजना पर, आप ऑपरेटरों को सॉर्ट और हैश मैच देख सकते हैं। उनका प्रभावी संचालन इन आंकड़ों को संसाधित करने के लिए आने वाले डेटा के आकार और भौतिक मेमोरी की उपलब्ध मात्रा पर निर्भर करता है।

यदि मेमोरी की आवश्यक मात्रा आवंटित करना संभव नहीं है, तो परिणामों की प्रोसेसिंग टेम्पेडब डेटाबेस (विस्मयादिबोधक चिह्न) में होगी - इससे डिस्क सबसिस्टम पर ध्यान देने योग्य भार आ सकता है और क्वेरी निष्पादन समय बढ़ सकता है:



SQL सर्वर निष्पादन समय:
सीपीयू समय = 6193 एमएस, बीता हुआ समय = 9571 एमएस।

आइए देखते हैं कि CASE WHEN की स्थितियों से कार्यक्षमता में समान निर्माण कैसे होता है:

 SELECT EmployeeID , Monday = COUNT(CASE WHEN [WeekDay] = 'Monday' THEN 1 END) , Tuesday = COUNT(CASE WHEN [WeekDay] = 'Tuesday' THEN 1 END) , Wednesday = COUNT(CASE WHEN [WeekDay] = 'Wednesday' THEN 1 END) , Thursday = COUNT(CASE WHEN [WeekDay] = 'Thursday' THEN 1 END) , Friday = COUNT(CASE WHEN [WeekDay] = 'Friday' THEN 1 END) , Saturday = COUNT(CASE WHEN [WeekDay] = 'Saturday' THEN 1 END) , Sunday = COUNT(CASE WHEN [WeekDay] = 'Sunday' THEN 1 END) FROM ( SELECT EmployeeID , [WeekDay] = DATENAME(WEEKDAY, DateOut) FROM dbo.WorkOut ) t GROUP BY EmployeeID 

जब निष्पादित किया जाता है, तो हमें एक सरल योजना मिलती है। इस मामले में, निष्पादन का समय PIVOT (निश्चित रूप से, त्रुटि के मार्जिन के भीतर) से बहुत अलग नहीं होगा:



SQL सर्वर निष्पादन समय:
सीपीयू समय = 5201 एमएस, बीता हुआ समय = 8400 एमएस।

स्मृति से बाहर चलने की स्थितियों में, हमें निम्नलिखित परिणाम मिलते हैं:



SQL सर्वर निष्पादन समय:
सीपीयू समय = 6006 एमएस, बीता हुआ समय = 13883 एमएस।

प्राप्त डेटा से थोड़ा अवलोकन किया जा सकता है - जब किसी एकल स्तंभ पर डेटा एकत्र करते हैं, तो PIVOT डिज़ाइन पर स्पष्ट लाभ होता है। यहां तक ​​कि ऐसी स्थिति में जहां प्रसंस्करण परिणामों के लिए स्मृति की कमी है।

अब देखते हैं कि शुद्ध कॉलम को बढ़ाते समय ये उदाहरण कैसे व्यवहार करते हैं जिसके द्वारा एकत्रीकरण हो रहा है।

1. अनुभाग द्वारा समूहीकरण: कर्मचारी + वर्ष:

 SELECT EmployeeID , [Year] , Monday = COUNT(CASE WHEN [WeekDay] = 'Monday' THEN 1 END) , ... , Sunday = COUNT(CASE WHEN [WeekDay] = 'Sunday' THEN 1 END) FROM ( SELECT EmployeeID , [Year] = YEAR(DateOut) , [WeekDay] = DATENAME(WEEKDAY, DateOut) FROM dbo.WorkOut ) t GROUP BY EmployeeID, [Year] 

SQL सर्वर निष्पादन समय:
सीपीयू समय = 5569 एमएस, बीता हुआ समय = 9200 एमएस।

 SELECT * FROM ( SELECT EmployeeID , [Year] = YEAR(DateOut) , [WeekDay] = DATENAME(WEEKDAY, DateOut) FROM dbo.WorkOut ) t PIVOT ( COUNT([WeekDay]) FOR [WeekDay] IN ( Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday ) ) p 

SQL सर्वर निष्पादन समय:
CPU समय = 5454 ms, बीता हुआ समय = 8878 ms।

यदि आप योजनाओं की तुलना करते हैं, तो आप देख सकते हैं कि PIVOT का उपयोग करते समय हैश मैच ऑपरेशन अधिक महंगा है, लेकिन रनटाइम अन्यथा इंगित करता है।

2. अनुभाग द्वारा समूहीकरण: कर्मचारी + वर्ष + महीना

 SELECT EmployeeID , [Year] , [Month] , Monday = COUNT(CASE WHEN [WeekDay] = 'Monday' THEN 1 END) , ... , Sunday = COUNT(CASE WHEN [WeekDay] = 'Sunday' THEN 1 END) FROM ( SELECT EmployeeID , [Year] = YEAR(DateOut) , [Month] = MONTH(DateOut) , [WeekDay] = DATENAME(WEEKDAY, DateOut) FROM dbo.WorkOut ) t GROUP BY EmployeeID, [Year], [Month] 

SQL सर्वर निष्पादन समय:
सीपीयू समय = 6365 एमएस, बीता हुआ समय = 9979 एमएस।

 SELECT * FROM ( SELECT EmployeeID , [Year] = YEAR(DateOut) , [Month] = MONTH(DateOut) , [WeekDay] = DATENAME(WEEKDAY, DateOut) FROM dbo.WorkOut ) t PIVOT ( COUNT([WeekDay]) FOR [WeekDay] IN ( Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday ) ) p 

SQL सर्वर निष्पादन समय:
सीपीयू समय = 6193 एमएस, बीता हुआ समय = 9861 एमएस।

तथ्य की बात के रूप में, स्थिति खुद को दोहराती है - SQL सर्वर PIVOT निर्माण का अधिक महंगा मूल्यांकन करता है।

लेकिन रनटाइम फिर से अपनी जगह पर सब कुछ डाल देता है।

इससे छोटे निष्कर्ष निकाले जा सकते हैं: अधिकांश परिस्थितियों में, PIVOT निर्माण का उपयोग करके, आप स्तंभों को जल्दी से पंक्तियों में बदल सकते हैं।

इस मामले में एक छोटी टिप्पणी निम्नलिखित है: उन स्तंभों की संख्या में वृद्धि, जिनके साथ एकत्रीकरण बढ़ता है, PIVOT और CASE WHEN के बीच निष्पादन समय में अंतर कम हो जाएगा और एक निश्चित बिंदु पर माप त्रुटि के भीतर होगा।

सभी प्रयोग SQL सर्वर 2012 SP1 (11.00.3128) पर किए गए थे।