أداء العمود المحسوب في SQL Server

تم إعداد ترجمة المقال خصيصًا لطلاب دورة "مطور خادم MS SQL" .

---

يمكن أن تتسبب الأعمدة المحسوبة في حدوث مشكلات في الأداء يصعب تشخيصها. تتناول هذه المقالة عددًا من المشكلات وبعض الطرق لحلها.



تعتبر الأعمدة المحسوبة طريقة مناسبة لتضمين العمليات الحسابية في تعريفات الجدول. لكنها يمكن أن تسبب مشاكل في الأداء ، خاصةً عندما تصبح التعبيرات أكثر تعقيدًا ، وتصبح التطبيقات أكثر تطلبًا ، وتستمر أحجام البيانات في النمو.



العمود المحسوب هو عمود افتراضي يتم حساب قيمته بناءً على القيم الموجودة في الأعمدة الأخرى في الجدول. بشكل افتراضي ، لا يتم تخزين القيمة المحسوبة فعليًا ، ولكن بدلاً من ذلك يحسبها SQL Server عند كل طلب عمود. يؤدي هذا إلى زيادة الحمل على المعالج ، ولكنه يقلل من كمية البيانات التي يجب الاحتفاظ بها عند تغيير الجدول.



غالبًا ما تكون الأعمدة المحسوبة غير المستمرة مكثفة لوحدة المعالجة المركزية ، مما يؤدي إلى إبطاء الاستعلامات وتجميد التطبيقات. لحسن الحظ ، يوفر SQL Server عدة طرق لتحسين أداء الأعمدة المحسوبة. يمكنك إنشاء أعمدة محسوبة ثابتة أو فهرستها أو القيام بكليهما.



من أجل العرض التوضيحي ، قمت بإنشاء أربعة جداول مماثلة وملأتهم ببيانات متطابقة من قاعدة البيانات التجريبية WideWorldImporters. يحتوي كل جدول على نفس العمود المحسوب ، لكن هناك جدولين بهما وجود وفهرس. النتيجة هي الخيارات التالية:

• الجدول Orders1عبارة عن عمود محسوب غير دائم.
• الجدول Orders2عبارة عن عمود محسوب مستمر.
• الجدول Orders3عبارة عن عمود محسوب غير دائم مع فهرس.
• الجدول Orders4عبارة عن عمود محسوب مستمر به فهرس.

التعبير المحسوب بسيط للغاية ومجموعة البيانات صغيرة جدًا. ومع ذلك ، يجب أن يكون كافياً لتوضيح مبادئ الأعمدة المحسوبة المستمرة والمفهرسة وكيف يساعد ذلك في حل مشاكل الأداء.

عمود محسوب غير محفوظ

ربما في حالتك قد ترغب في استخدام أعمدة محسوبة غير ثابتة لتجنب تخزين البيانات أو إنشاء الفهارس أو استخدامها مع عمود غير محدد. على سبيل المثال ، سيتعامل SQL Server مع UDF العددي على أنه غير محدد إذا كان WITH SCHEMABINDING مفقودًا من تعريف الوظيفة. إذا حاولت إنشاء عمود محسوب مستمر باستخدام هذه الوظيفة ، فسوف تحصل على خطأ يفيد بأنه لا يمكن إنشاء العمود المستمر.



ومع ذلك ، تجدر الإشارة إلى أن الوظائف المخصصة يمكن أن تخلق مشاكل الأداء الخاصة بها. إذا كان الجدول يحتوي على عمود محسوب بوظيفة ، فلن يستخدم محرك الاستعلام التزامن (إلا إذا كنت تستخدم SQL Server 2019). حتى في حالة عدم تحديد العمود المحسوب في الاستعلام. بالنسبة لمجموعة البيانات الكبيرة ، يمكن أن يكون لهذا تأثير كبير على الأداء. يمكن أن تؤدي الوظائف أيضًا إلى إبطاء تنفيذ التحديثات والتأثير على كيفية حساب المُحسِّن لتكلفة استعلام في عمود محسوب. هذا لا يعني أنه لا يجب عليك أبدًا استخدام الوظائف في عمود محسوب ، ولكن بالتأكيد يجب التعامل معها بحذر.



سواء كنت تستخدم وظائف أم لا ، فإن إنشاء عمود محسوب غير دائم أمر بسيط جدًا. التعليمات التاليةCREATE TABLEيعرّف جدولاً Orders1يتضمن عمودًا محسوبًا Cost.

USE WideWorldImporters;
GO
DROP TABLE IF EXISTS Orders1;
GO
CREATE TABLE Orders1(
  LineID int IDENTITY PRIMARY KEY,
  ItemID int NOT NULL,
  Quantity int NOT NULL,
  Price decimal(18, 2) NOT NULL,
  Profit decimal(18, 2) NOT NULL,
  Cost AS (Quantity * Price - Profit));

INSERT INTO Orders1 (ItemID, Quantity, Price, Profit)
SELECT StockItemID, Quantity, UnitPrice, LineProfit
FROM Sales.InvoiceLines
WHERE UnitPrice IS NOT NULL
ORDER BY InvoiceLineID;

لتعريف عمود محسوب ، حدد اسمه متبوعًا بالكلمة الأساسية والتعبير AS. في مثالنا، نحن ضرب Quantityمن قبل Priceوطرح Profit. بعد إنشاء الجدول ، Sales.InvoiceLinesنملأه بـ INSERT باستخدام بيانات من جدول قاعدة بيانات WideWorldImporters. بعد ذلك ، نقوم بتنفيذ SELECT.

SELECT ItemID, Cost FROM Orders1 WHERE Cost >= 1000;

يجب أن يُرجع هذا الاستعلام 22973 صفاً ، أو جميع الصفوف الموجودة في قاعدة بيانات WideWorldImporters. تظهر خطة تنفيذ هذا الاستعلام في الشكل 1.





الشكل 1. خطة التنفيذ للاستعلام مقابل جدول الطلبات 1



أول شيء يجب ملاحظته هو فحص الفهرس العنقودي ، وهو ليس طريقة فعالة للحصول على البيانات. لكن هذه ليست المشكلة الوحيدة. لنلقِ نظرة على عدد القراءات المنطقية (القراءات المنطقية الفعلية) في خصائص مسح الفهرس العنقودي (انظر الشكل 2).





الشكل 2. قراءة منطقية للاستعلام عن جدول Orders1



عدد القراءات المنطقية (في هذه الحالة 1108) هو عدد الصفحات التي تمت قراءتها من ذاكرة التخزين المؤقت للبيانات. الهدف هو محاولة تقليل هذا الرقم قدر الإمكان. لذلك ، من المفيد تذكرها ومقارنتها بالخيارات الأخرى.



يمكن أيضًا الحصول على عدد القراءات المنطقية عن طريق تشغيل العبارة SET STATISTICS IO ONقبل تنفيذ SELECT. لعرض وحدة المعالجة المركزية والوقت الإجمالي - SET STATISTICS TIME ONأو لعرض خصائص عبارة SELECT في خطة تنفيذ الاستعلام.



هناك نقطة أخرى جديرة بالملاحظة وهي أن هناك جملتين لحساب Scalar في خطة التنفيذ. الأول (الموجود على اليمين) هو حساب قيمة العمود المحسوبة لكل صف تم إرجاعه. نظرًا لأنه يتم حساب قيم العمود على الفور ، لا يمكنك تجنب هذه الخطوة باستخدام الأعمدة المحسوبة غير المستمرة إلا إذا قمت بإنشاء فهرس في هذا العمود.



في بعض الحالات ، يوفر العمود المحسوب غير الدائم الأداء المطلوب بدون تخزينه أو استخدام فهرس. لا يؤدي ذلك إلى توفير مساحة التخزين فحسب ، بل يؤدي أيضًا إلى تجنب النفقات الزائدة المرتبطة بتحديث القيم المحسوبة في جدول أو فهرس. ومع ذلك ، في أغلب الأحيان ، يؤدي العمود المحسوب غير الدائم إلى مشاكل في الأداء ، ومن ثم يجب أن تبدأ في البحث عن بديل.

العمود المحسوب المستمر

تتمثل إحدى التقنيات المستخدمة غالبًا في حل مشكلات الأداء في تحديد عمود محسوب على أنه مستمر. باستخدام هذا الأسلوب ، يتم حساب التعبير مقدمًا ويتم تخزين النتيجة مع باقي بيانات الجدول.



لكي يكون العمود ثابتًا ، يجب أن يكون حتميًا ، أي أنه يجب أن يُرجع التعبير دائمًا نفس النتيجة لنفس الإدخال. على سبيل المثال ، لا يمكنك استخدام دالة GETDATE في تعبير عمود ، لأن القيمة المرجعة تتغير دائمًا.



لإنشاء عمود محسوب مستمر ، يجب إضافة كلمة أساسية إلى تعريف العمود PERSISTED، كما هو موضح في المثال التالي.

DROP TABLE IF EXISTS Orders2;
GO
CREATE TABLE Orders2(
  LineID int IDENTITY PRIMARY KEY,
  ItemID int NOT NULL,
  Quantity int NOT NULL,
  Price decimal(18, 2) NOT NULL,
  Profit decimal(18, 2) NOT NULL,
  Cost AS (Quantity * Price - Profit) PERSISTED);

INSERT INTO Orders2 (ItemID, Quantity, Price, Profit)
SELECT StockItemID, Quantity, UnitPrice, LineProfit
FROM Sales.InvoiceLines
WHERE UnitPrice IS NOT NULL
ORDER BY InvoiceLineID;

Orders2يتطابق الجدول تقريبًا مع الجدول Orders1، باستثناء أن العمود Costيحتوي على الكلمة الأساسية PERSISTED. يملأ SQL Server هذا العمود تلقائيًا عند إضافة صفوف أو تعديلها. بالطبع ، هذا يعني أن الطاولة Orders2ستشغل مساحة أكبر من الطاولة Orders1. يمكن التحقق من ذلك باستخدام إجراء مخزن sp_spaceused.

sp_spaceused 'Orders1';
GO
sp_spaceused 'Orders2';
GO

يوضح الشكل 3 إخراج هذا الإجراء المخزن. حجم البيانات في الجدول Orders1هو 8،824 كيلو بايت ، وفي الجدول Orders2- 12،936 كيلو بايت. 4112 كيلو بايت أكثر لتخزين القيم المحسوبة.





الشكل 3. مقارنة حجم جدولي الطلبات 1 و الطلبات 2 على



الرغم من أن هذه الأمثلة تستند إلى مجموعة بيانات صغيرة إلى حد ما ، يمكنك أن ترى كيف يمكن أن تنمو كمية البيانات المخزنة بسرعة. ومع ذلك ، يمكن أن تكون هذه مقايضة إذا تحسن الأداء.



لمعرفة الفرق في الأداء ، قم بإجراء التحديد التالي.

SELECT ItemID, Cost FROM Orders2 WHERE Cost >= 1000;

هذا هو نفس التحديد الذي استخدمته لجدول Orders1 (باستثناء تغيير الاسم). يوضح الشكل 4 خطة التنفيذ.





الشكل 4. خطة التنفيذ لاستعلام في جدول الطلبات 2.



يبدأ هذا أيضًا بمسح الفهرس العنقودي . ولكن هذه المرة ، هناك عبارة واحدة فقط لحساب Scalar لأن الأعمدة المحسوبة لم تعد بحاجة إلى أن يتم حسابها في وقت التشغيل. بشكل عام ، كلما قل عدد الخطوات كان ذلك أفضل. على الرغم من أن هذا ليس هو الحال دائمًا.



أنتج الاستعلام الثاني 1593 قراءة منطقية ، أي أكثر من 1108 قراءة من الجدول الأول بمقدار 485. على الرغم من ذلك ، فإنه يعمل بشكل أسرع من الأول. على الرغم من أن حوالي 100 مللي ثانية ، وأحيانًا أقل من ذلك بكثير. انخفض وقت المعالج أيضًا ، ولكن ليس كثيرًا أيضًا. على الأرجح ، سيكون الفرق أكبر بكثير في الأحجام الأكبر والحسابات الأكثر تعقيدًا.

الفهرس في العمود المحسوب غير الدائم

هناك تقنية أخرى تُستخدم بشكل شائع لتحسين أداء العمود المحسوب وهي الفهرسة. لتكون قادرًا على إنشاء فهرس ، يجب أن يكون العمود محددًا ودقيقًا ، مما يعني أن التعبير لا يمكنه استخدام النوعين الحقيقي والحقيقي (إذا لم يكن العمود ثابتًا). هناك أيضًا قيود على أنواع البيانات الأخرى وكذلك على معلمات SET. للحصول على قائمة كاملة بالقيود ، راجع وثائق SQL Server ، الفهارس في الأعمدة المحسوبة .



يمكنك التحقق مما إذا كان العمود المحسوب غير الدائم مناسبًا للفهرسة من خلال خصائصه. دعنا نستخدم الوظيفة لعرض الخصائص COLUMNPROPERTY. الخصائص هي حتمية ، وغير قابلة للتفسير ، ودقيقة مهمة بالنسبة لنا.

DECLARE @id int = OBJECT_ID('dbo.Orders1')
SELECT
  COLUMNPROPERTY(@id,'Cost','IsDeterministic') AS 'Deterministic',
  COLUMNPROPERTY(@id,'Cost','IsIndexable') AS 'Indexable',
  COLUMNPROPERTY(@id,'Cost','IsPrecise') AS 'Precise';

يجب أن ترجع عبارة SELECT 1 لكل خاصية بحيث يمكن فهرسة العمود المحسوب (انظر الشكل 5).





الشكل 5. التحقق من



إمكانية إنشاء الفهرس بعد التحقق ، يمكنك إنشاء فهرس غير مترابط. بدلاً من تعديل الجدول ، Orders1قمت بإنشاء جدول ثالث ( Orders3) وقمت بتضمين الفهرس في تعريف الجدول.

DROP TABLE IF EXISTS Orders3;
GO
CREATE TABLE Orders3(
  LineID int IDENTITY PRIMARY KEY,
  ItemID int NOT NULL,
  Quantity int NOT NULL,
  Price decimal(18, 2) NOT NULL,
  Profit decimal(18, 2) NOT NULL,
  Cost AS (Quantity * Price - Profit),
  INDEX ix_cost3 NONCLUSTERED (Cost, ItemID));

INSERT INTO Orders3 (ItemID, Quantity, Price, Profit)
SELECT StockItemID, Quantity, UnitPrice, LineProfit
FROM Sales.InvoiceLines
WHERE UnitPrice IS NOT NULL
ORDER BY InvoiceLineID;

لقد أنشأت فهرسًا غير متفاوت المسافات يشتمل على أعمدة من ItemIDومن Costاستعلام SELECT. بعد إنشاء الجدول والفهرس وتعبئته ، يمكنك تنفيذ عبارة SELECT التالية المشابهة للأمثلة السابقة.

SELECT ItemID, Cost FROM Orders3 WHERE Cost >= 1000;

يوضح الشكل 6 خطة التنفيذ لهذا الاستعلام ، والذي يستخدم الآن فهرس ix_cost3 (بحث الفهرس) غير المجمع بدلاً من إجراء مسح فهرس متفاوت.





الشكل 6. خطة التنفيذ لاستعلام في جدول الطلبات 3



إذا نظرت إلى خصائص عبارة بحث الفهرس ، ستجد أن الاستعلام الآن يؤدي فقط 92 قراءة منطقية ، وفي خصائص عبارة SELECT ، سترى أن وحدة المعالجة المركزية والوقت الإجمالي قد انخفض. الفرق ليس كبيرًا ، ولكن مرة أخرى ، هذه مجموعة بيانات صغيرة.



وتجدر الإشارة أيضًا إلى وجود عبارة Compute Scalar واحدة فقط في خطة التنفيذ ، وليس اثنتين كما في الاستعلام الأول. نظرًا لأنه تم فهرسة العمود المحسوب ، فقد تم بالفعل حساب القيم. هذا يلغي الحاجة إلى حساب القيم في وقت التشغيل ، حتى لو لم يتم تعريف العمود ليكون ثابتًا.

فهرس في العمود المخزن

يمكنك أيضًا إنشاء فهرس على العمود المحسوب الذي تقوم بحفظه. في حين أن هذا سيؤدي إلى تخزين بيانات إضافية وبيانات فهرسة ، إلا أنه قد يكون مفيدًا في بعض الحالات. على سبيل المثال ، يمكنك إنشاء فهرس على عمود محسوب ثابت ، حتى إذا كان يستخدم أنواع البيانات العائمة أو الحقيقية. يمكن أن يكون هذا الأسلوب مفيدًا أيضًا عند العمل مع وظائف CLR ، وعندما لا يمكنك التحقق مما إذا كانت الوظائف حتمية.



البيان التالي CREATE TABLEينشئ جدول Orders4. يتضمن تعريف الجدول كلاً من عمود ثابت Costوفهرس تغطية غير مترابط ix_cost4.

DROP TABLE IF EXISTS Orders4;
GO
CREATE TABLE Orders4(
  LineID int IDENTITY PRIMARY KEY,
  ItemID int NOT NULL,
  Quantity int NOT NULL,
  Price decimal(18, 2) NOT NULL,
  Profit decimal(18, 2) NOT NULL,
  Cost AS (Quantity * Price - Profit) PERSISTED,
  INDEX ix_cost4 NONCLUSTERED (Cost, ItemID));

INSERT INTO Orders4 (ItemID, Quantity, Price, Profit)
SELECT StockItemID, Quantity, UnitPrice, LineProfit
FROM Sales.InvoiceLines
WHERE UnitPrice IS NOT NULL
ORDER BY InvoiceLineID;

بعد إنشاء الجدول والفهرس وتعميمهما ، قم بتنفيذ SELECT.

SELECT ItemID, Cost FROM Orders4 WHERE Cost >= 1000;

يوضح الشكل 7 خطة التنفيذ. كما في المثال السابق ، يبدأ الاستعلام ببحث فهرس غير متفاوت (بحث عن فهرس).





الشكل 7. خطة التنفيذ لاستعلام في جدول Orders4



يؤدي هذا الاستعلام أيضًا 92 قراءة منطقية فقط كالسابقة ، مما يؤدي إلى نفس الأداء تقريبًا. يتمثل الاختلاف الرئيسي بين العمودين المحسوبين ، وبين الأعمدة المفهرسة وغير المفهرسة ، في مقدار المساحة المستخدمة. دعنا نتحقق من ذلك عن طريق تشغيل الإجراء المخزن sp_spaceused.

sp_spaceused 'Orders1';
GO
sp_spaceused 'Orders2';
GO
sp_spaceused 'Orders3';
GO
sp_spaceused 'Orders4';
GO

النتائج موضحة في الشكل 8. كما هو متوقع ، تحتوي الأعمدة المحسوبة المخزنة على المزيد من البيانات والأعمدة المفهرسة بها المزيد من الفهارس.





الشكل 8. مقارنة استخدام المساحة لجميع الجداول الأربعة على



الأرجح ، لن تحتاج إلى فهرسة الأعمدة المحسوبة المخزنة دون سبب وجيه. كما هو الحال مع الأسئلة الأخرى المتعلقة بقاعدة البيانات ، يجب أن يعتمد اختيارك على موقفك المحدد: استفساراتك وطبيعة بياناتك.

العمل مع الأعمدة المحسوبة في SQL Server

العمود المحسوب ليس عمود جدول عادي ويجب التعامل معه بحذر لتجنب تدهور الأداء. يمكن حل معظم مشكلات الأداء من خلال تخزين العمود أو فهرسته ، ولكن كلا الأسلوبين يحتاجان إلى مراعاة مساحة القرص الإضافية وكيفية تغير البيانات. عندما تتغير البيانات ، يجب تحديث قيم العمود المحسوبة في الجدول أو الفهرس ، أو كليهما ، إذا قمت بفهرسة العمود المحسوب المستمر. يمكنك فقط تحديد أي من الخيارات هو الأفضل لحالتك الخاصة. وعلى الأرجح ، سيتعين عليك استخدام جميع الخيارات.

اقرأ أكثر

• مقدمة إلى قواعد بيانات SQL Server 2017 Graph