في تطبيقات الويب، يعد ترقيم الصفحات لمجموعة نتائج قاعدة بيانات كبيرة مشكلة معروفة. ببساطة، أنت لا تريد أن يتم عرض كافة بيانات الاستعلام على صفحة واحدة، لذا فإن العرض مع الترحيل أكثر ملاءمة. على الرغم من أن هذه ليست مهمة بسيطة في ASP التقليدي، إلا أن عنصر التحكم DataGrid في ASP.NET يبسط هذه العملية إلى بضعة أسطر فقط من التعليمات البرمجية. لذلك، في asp.net، يكون الترحيل بسيطًا جدًا، لكن حدث الترحيل الافتراضي DataGrid سيقرأ جميع السجلات من قاعدة البيانات ويضعها في تطبيق الويب asp.net. عندما يكون لديك أكثر من مليون بيانات، سيؤدي ذلك إلى مشكلات خطيرة في الأداء (إذا كنت لا تصدق ذلك، يمكنك تنفيذ استعلام في التطبيق الخاص بك وإلقاء نظرة على استهلاك ذاكرة aspnet_wp.exe في حالة مدير المهام) وهذا هو السبب من الضروري تخصيص سلوك الترحيل، وذلك لضمان الحصول على سجلات البيانات التي تتطلبها الصفحة الحالية فقط.
هناك العديد من المقالات والمنشورات حول هذه المشكلة على الإنترنت، بالإضافة إلى بعض الحلول الناضجة. هدفي من كتابة هذه المقالة ليس أن أعرض لك إجراءً مخزنًا سيحل جميع مشكلاتك، ولكن تحسين الأساليب الحالية وتزويدك بتطبيق للاختبار حتى تتمكن من القيام بذلك وفقًا لاحتياجاتك.
لكنني لست راضيًا تمامًا عن الأساليب المقدمة حاليًا عبر الإنترنت. أولاً، يتم استخدام ADO التقليدي، والذي من الواضح أنه مكتوب لـ ASP "القديم". الطرق المتبقية هي إجراءات SQL Server المخزنة، وبعضها غير قابل للاستخدام بسبب أوقات الاستجابة البطيئة للغاية، كما ترون من نتائج الأداء في نهاية المقالة، ولكن هناك القليل منها الذي لفت انتباهي.
التعميم
أريد أن أقوم بتحليل الطرق الثلاث المستخدمة حاليًا بشكل دقيق، وهي الجداول المؤقتة (TempTable)، وSQL الديناميكية (DynamicSQL)، وعدد الصفوف (Rowcount). فيما يلي، أفضّل تسمية الطريقة الثانية بطريقة (تصاعدي-تنازلي) Asc-Desc. لا أعتقد أن SQL الديناميكي هو اسم جيد لأنه يمكنك أيضًا تطبيق منطق SQL الديناميكي بطريقة أخرى. المشكلة الشائعة في كل هذه الإجراءات المخزنة هي أنه يتعين عليك تقدير الأعمدة التي ستقوم بالفرز عليها، وليس فقط أعمدة المفاتيح الأساسية (PK Columns)، والتي يمكن أن تؤدي إلى سلسلة من المشكلات - لكل استعلام، تحتاج إلى عرضه من خلال الترحيل، مما يعني أنه لكل عمود فرز مختلف يجب أن يكون لديك العديد من استعلامات الترحيل المختلفة، مما يعني أنه يمكنك إما إجراء إجراء مخزن مختلف لكل عمود فرز (بغض النظر عن طريقة الترحيل المستخدمة)، أو يجب عليك ضع هذه الوظيفة في إجراء مخزن بمساعدة SQL الديناميكي. لهاتين الطريقتين تأثير بسيط على الأداء، لكنهما تزيدان من قابلية الصيانة، خاصة إذا كنت بحاجة إلى استخدام هذه الطريقة لعرض استعلامات مختلفة. لذلك، سأحاول في هذه المقالة استخدام SQL الديناميكي لتلخيص جميع الإجراءات المخزنة، ولكن لبعض الأسباب، لا يمكننا تحقيق سوى عالمية جزئية، لذلك لا يزال يتعين عليك كتابة إجراءات مخزنة مستقلة للاستعلامات المعقدة.
المشكلة الثانية في السماح بجميع حقول الفرز، بما في ذلك أعمدة المفاتيح الأساسية، هي أنه إذا لم تتم فهرسة هذه الأعمدة بشكل صحيح، فلن تساعد أي من هذه الطرق. في كل هذه الطرق، يجب فرز مصدر الترحيل أولاً بالنسبة لجداول البيانات الكبيرة، تكون تكلفة الفرز باستخدام أعمدة غير فهرس ضئيلة. في هذه الحالة، لا يمكن استخدام جميع الإجراءات المخزنة في المواقف الفعلية بسبب وقت الاستجابة الطويل. (يختلف الوقت المقابل من بضع ثوان إلى بضع دقائق، حسب حجم الجدول والسجل الأول الذي سيتم الحصول عليه). يمكن أن تؤدي الفهارس الموجودة في الأعمدة الأخرى إلى حدوث مشكلات إضافية غير مرغوب فيها في الأداء، على سبيل المثال، يمكن أن تصبح بطيئة جدًا إذا قمت باستيراد الكثير من البيانات كل يوم.
الجدول المؤقت
أولاً، سأتحدث عن طريقة الجدول المؤقت، وهو حل موصى به على نطاق واسع وقد واجهته عدة مرات في مشاريعي. دعونا نلقي نظرة على جوهر هذه الطريقة:
CREATE TABLE #Temp(
معرف int هوية المفتاح الأساسي،
PK /*هنا يذهبPKtype*/
)
INSERT INTO #Temp SELECT PK FROM Table ORDER BY SortColumn
SELECT FROM Table JOIN # Temp temp ON Table.PK = temp .PK ORDER BY temp .ID WHERE ID > @StartRow AND ID< @EndRow
عن طريق نسخ كافة الصفوف إلى مؤقت في يمكننا تحسين الاستعلام بشكل أكبر (SELECT TOP EndRow...)، ولكن المفتاح هو السيناريو الأسوأ - الجدول الذي يحتوي على مليون سجل سينشئ جدولًا مؤقتًا يحتوي على مليون سجل.
بالنظر إلى هذا الموقف والنظر في نتائج المقالة أعلاه، قررت التخلي عن الطريقةالتصاعدية التنازلية
في الاختبار الذي أجريته.
تستخدم هذه الطريقة الفرز الافتراضي في الاستعلام الفرعي والفرز العكسي في الاستعلام الرئيسي.
أعلن @temp TABLE(
PK /* نوع PK */
ليست فارغة الابتدائية
)
أدخل في @temp حدد TOP @PageSize PK من
(
حدد الجزء العلوي (@StartRow + @PageSize)
بك،
SortColumn /* إذا كان عمود الفرز مختلفًا عن PK، فيجب أن يكون SortColumn
يتم جلبها أيضًا، وإلا فإن PK فقط ضروري
*/
الترتيب حسب عمود الترتيب
/*
الترتيب الافتراضي-عادةً ASC
*/
)
ترتيب حسب SortColumn
/*
الترتيب الافتراضي المعكوس - عادةً DESC
*/
اختر من الجدول JOIN @Temp temp ON Table .PK= temp .PK
ترتيب حسب عمود الفرز
/*
com.defaultorder
*/
لحساب الصفوف
على تعبير SET ROWCOUNT في SQL، بحيث يمكن تخطي الصفوف غير الضرورية ويمكن الحصول على سجلات الصفوف المطلوبة:
DECLARE @Sort /* نوع عمود الفرز */
SET ROWCOUNT @ StartRow
حدد @Sort=SortColumn من الجدول ORDER BY SortColumn
SET ROWCOUNT @PageSize
اختر من الجدول حيث SortColumn >= @Sort ORDER BY SortColumn
هناك طريقتان أخريانللاستعلام الفرعي
أخذتهما بعين الاعتبار، ومصادرهما مختلفة. الأول هو الاستعلام الثلاثي المعروف أو طريقة الاستعلام الذاتي. وفي هذه المقالة، أستخدم أيضًا منطقًا شائعًا مشابهًا يشمل كافة الإجراءات المخزنة الأخرى. الفكرة هنا هي الاتصال بالعملية بأكملها، لقد أجريت بعض التخفيضات على الكود الأصلي نظرًا لعدم الحاجة إلى عدد السجلات في اختباراتي)
SELECT FROM Table WHERE PK IN(
حدد TOPPageSize PK من الجدول حيث لا يوجد PK
(
حدد TOPStartRow PK من الجدول بالترتيب حسب SortColumn)
ترتيب حسب عمود الفرز)
الترتيب حسب
مؤشر
SortColumnأثناء النظر إلى مجموعة مناقشة Google، وجدت الطريقة الأخيرة. تستخدم هذه الطريقة مؤشرًا ديناميكيًا من جانب الخادم. يحاول العديد من الأشخاص تجنب استخدام المؤشرات لأنها غير ذات صلة وغير فعالة بسبب ترتيبها، ولكن بالنظر إلى الوراء، فإن الترحيل هو في الواقع مهمة منظمة، بغض النظر عن الطريقة التي تستخدمها، يجب عليك العودة إلى سطر البداية للتسجيل. في الطريقة السابقة، عليك أولاً تحديد جميع الصفوف قبل بدء التسجيل، وإضافة الصفوف المطلوبة للتسجيل، ثم حذف جميع الصفوف السابقة. تحتوي المؤشرات الديناميكية على خيار FETCH RELATIVE الذي يقوم بإجراء قفزات سحرية. المنطق الأساسي هو كما يلي:
DECLARE @PK /* PKType */
DECLARE @tblPK
TABLE(
PK /*PKType*/ ليس مفتاحًا أساسيًا فارغًا
)
قم بتعريف مؤشر ترحيل الصفحات DYNAMICREAD_ONLY لـ
حدد @PK من الجدول بالترتيب حسب SortColumn،
وافتح PagingCursor
جلب @StartRow النسبي من PagingCursor إلى @PK
بينما @PageSize>0 و@@FETCH_STATUS =0
يبدأ
أدخل قيم @tblPK(PK)(@PK)
جلب التالي من PagingCursor إلى @PK
تعيين @PageSize = @PageSize - 1
إغلاق
النهاية
مؤشر الترحيل
إلغاء التخصيص
PagingCursor
حدد من الجدول JOIN @tblPK temp ON Table .PK= temp .PK
تعميم الاستعلامات المعقدة
في ORDER BY SortColumn
أشرت من قبل إلى أن جميع الإجراءات المخزنة تستخدم SQL الديناميكي لتحقيق التعميم، لذلك من الناحية النظرية يمكنهم استخدام أي نوع من الاستعلامات المعقدة. يوجد أدناه مثال لاستعلام معقد يستند إلى قاعدة بيانات Northwind.
حدد Customers.ContactName AS العميل، Customers.Address + ' ، ' + Customers.City + '، '+ Customers.Country
عنوان AS، SUM([تفاصيل الطلب].سعر الوحدة*[تفاصيل الطلب] .الكمية)
AS [إجمالي الأموال التي تم إنفاقها]
من العملاء
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN [ OrderDetails ] ON Orders.OrderID = [ OrderDetails ].
معرف الطلب حيث Customers.Country <> 'USA' AND Customers.Country <> 'Mexico '
المجموعة حسب العملاء. اسم جهة الاتصال، العملاء. العنوان، مدينة العملاء، العملاء. البلد
HAVING(SUM([OrderDetails].UnitPrice * [ OrderDetails ] .Quantity)) > 1000
ORDER BY Customer DESC ، يقوم العنوان DESC
بإرجاع استدعاء تخزين الترحيل للصفحة الثانية كما يلي:
اسم إجراء EXEC
/*Tables */
'
عملاء
INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
INNER JOIN [OrderDetails] ON Orders.OrderID=[OrderDetails].OrderID
'
,
/* بي كي */
'
Customers.CustomerID
'
,
/* الترتيب */
'
Customers.ContactName DESC،Customers.AddressDESC
'
,
/*رقم الصفحة*/
2
,
/*حجم الصفحة*/
10
،
/*الحقول */
'
العملاء. اسم جهة الاتصال كعميل،
Customers.Address+'' , '' +Customers.City+ '' , '' +Customers.Country ASAddress, SUM([OrderDetails).UnitPrice*[OrderDetails].Quantity)AS[Totalmoneyspent]
'
,
/*تصفية */
'
Customers.Country<>'' USA '' ANDCustomers.Country<> '' المكسيك ''' ,
/*GroupBy */
'
Customers.CustomerID،Customers.ContactName،Customers.Address،
العملاء. المدينة، العملاء. البلد
HAVING(SUM([OrderDetails].UnitPrice*[OrderDetails].Quantity))>1000
تجدر الإشارة إلى أنك استخدمت أسماء مستعارة في عبارة ORDER BY في الاستعلام الأصلي، ولكن من الأفضل عدم القيام بذلك في إجراء مخزن مقسم إلى صفحات، لأن تخطي الصفوف قبل البدء في التسجيل يستغرق وقتًا طويلاً
.
في الواقع، هناك العديد من طرق التنفيذ، ولكن المبدأ ليس تضمين جميع الحقول في البداية، بل تضمين عمود المفتاح الأساسي فقط (أي ما يعادل عمود الفرز في طريقة RowCount)، والذي يمكن أن يسرع إكمال عملية التنفيذ. مهمة. فقط في صفحة الطلب يتم الحصول على كافة الحقول المطلوبة. علاوة على ذلك، لا توجد أسماء مستعارة للحقول في الاستعلام النهائي، وفي استعلامات صف التخطي، يجب استخدام أعمدة الفهرس مسبقًا.
هناك مشكلة أخرى في الإجراء المخزن RowCount لتحقيق التعميم، يُسمح بعمود واحد فقط في عبارة ORDER BY. وهذه أيضًا مشكلة في الأسلوب التصاعدي التنازلي وأسلوب المؤشر، على الرغم من إمكانية فرز عدة أعمدة ويجب التأكد من وجود حقل واحد فقط في المفتاح الأساسي. أعتقد أن هذا يمكن حله باستخدام لغة SQL أكثر ديناميكية، ولكن في رأيي لا يستحق الأمر ذلك. في حين أن مثل هذه المواقف ممكنة، إلا أنها لا تحدث كثيرًا. عادةً يمكنك استخدام المبادئ المذكورة أعلاه لصفحة الإجراءات المخزنة بشكل مستقل.
اختبار الأداء
في الاختبار، استخدمت أربع طرق، إذا كان لديك طريقة أفضل، سأكون مهتمًا بمعرفتها. على أية حال، أنا بحاجة لمقارنة هذه الأساليب وتقييم أدائها. بادئ ذي بدء، فكرتي الأولى هي كتابة تطبيق اختبار asp.net يحتوي على DataGrid للترحيل، ثم اختبار نتائج الصفحة. بالطبع، هذا لا يعكس وقت الاستجابة الحقيقي للإجراء المخزن، لذا فإن تطبيق وحدة التحكم أكثر ملاءمة. لقد قمت أيضًا بتضمين تطبيق ويب، ولكن ليس لاختبار الأداء، ولكن كمثال على عمل صفحات DataGrid المخصصة والإجراءات المخزنة معًا.
في الاختبار، استخدمت جدول بيانات كبير تم إنشاؤه تلقائيًا وأدخلت حوالي 500000 قطعة من البيانات. إذا لم يكن لديك مثل هذا الجدول لتجربته، فيمكنك النقر هنا لتنزيل تصميم الجدول والبرنامج النصي للإجراء المخزن لتوليد البيانات. بدلاً من استخدام عمود مفتاح أساسي متزايد تلقائيًا، استخدمت معرفًا فريدًا لتحديد السجل. إذا استخدمت البرنامج النصي الذي ذكرته أعلاه، فقد تفكر في إضافة عمود الزيادة التلقائية بعد إنشاء الجدول. سيتم فرز بيانات الزيادة التلقائية رقميًا بناءً على المفتاح الأساسي. وهذا يعني أيضًا أنك تنوي استخدام إجراء مخزن مرقّم مع فرز المفتاح الأساسي للحصول على بيانات الصفحة الحالية.
من أجل تنفيذ اختبار الأداء، قمت باستدعاء إجراء مخزن محدد عدة مرات من خلال حلقة ثم قمت بحساب متوسط وقت الاستجابة. مع الأخذ في الاعتبار أسباب التخزين المؤقت، من أجل تصميم الموقف الفعلي بشكل أكثر دقة - فإن الوقت الذي تستغرقه نفس الصفحة للحصول على بيانات لاستدعاءات متعددة لإجراء مخزن عادة ما يكون غير مناسب للتقييم، لذلك، عندما نطلق على نفس الإجراء المخزن، يجب أن يكون رقم الصفحة المطلوبة لكل مكالمة عشوائيًا. بالطبع يجب أن نفترض أن عدد الصفحات ثابت، 10-20 صفحة، ويمكن الحصول على البيانات بأرقام صفحات مختلفة عدة مرات ولكن بشكل عشوائي.
شيء واحد يمكننا أن نلاحظه بسهولة هو أن وقت الاستجابة يتم تحديده من خلال مسافة بيانات الصفحة التي سيتم الحصول عليها بالنسبة إلى موضع البداية لمجموعة النتائج، وكلما ابتعدنا عن موضع البداية لمجموعة النتائج، زاد عدد السجلات تم تخطي هذا أيضًا سبب عدم تضمين أفضل 20 في تسلسلي العشوائي. كبديل، سأستخدم 2^n من الصفحات، وحجم الحلقة هو عدد الصفحات المختلفة المطلوبة * 1000، لذلك يتم جلب كل صفحة 1000 مرة تقريبًا (سيكون هناك بالتأكيد انحراف لأسباب عشوائية)
النتائج
هنا هي نتائج الاختبار الخاصة بي: 
الاستنتاج
تم إجراء الاختبارات بالترتيب من الأفضل إلى الأسوأ أداءً - عدد الصفوف، المؤشر، تصاعدي تنازلي، الاستعلام الفرعي. أحد الأشياء المثيرة للاهتمام هو أن الأشخاص نادرًا ما يزورون الصفحات بعد الصفحات الخمس الأولى، لذلك قد تناسب طريقة الاستعلام الفرعي احتياجاتك في هذه الحالة، اعتمادًا على حجم مجموعة النتائج الخاصة بك ومدى بعدها للتنبؤ بتكرار تكرارات الصفحة ، فمن المحتمل أيضًا أن تستخدم مجموعة من هذه الطرق. لو كنت أنا، كنت أفضل طريقة حساب الصفوف على أي حال، فهي تعمل بشكل جيد، حتى بالنسبة للصفحة الأولى، فإن "أي حالة" هنا تمثل بعض الحالات التي يصعب فيها التعميم، في هذه الحالة، سأستخدم المؤشر. (ربما سأستخدم طريقة الاستعلام الفرعي في الأولين، وطريقة المؤشر بعد ذلك)