معرفي:
صفحه بندي نتايج حاصل از اجراي جستجو روي بانك اطلاعاتي در برنامه‌هاي ASP.NET از مشكلات معروف مي‌باشد. به بيان مختصر، شما نمي‌خواهيد كه تمام نتايج حاصل از پرس و جو (Query ) را در يك صفحه نمايش دهيد مثلاً اگر يك ميليون ركورد داشته باشيد، صفحه شما بسيار بزرگ و غير قابل دسترسي خواهد شد. بنابراين دسته بندي نتايج مثل اعدادي كه در پائين سايت google مي‌بينيد و نتايج را صفحه به صفحه به شما نشان مي‌دهد، كاملاً ضروري است. در صورتي كه در ASP مدل قديمي صفحه بندي عمل سختي بود، اما اين كار در ASP.NET با تنها چند خط كه توسط كنترل Data Grid قابل پياده سازي است. بنابراين صفحه بندي در ASP.NET ساده است، اما حالت پيش فرض Data Grid براي صفحه بندي، واكنشي تمام ركوردهاي حاصل از پرس و جو از بانك اطلاعاتي به برنامه ASP.NET و سپس صفحه بندي آنهاست (اگر پرس و جوي شما يك ميليون ركورد برگرداند، برنامه شما دچار مشكلات زيادي در رابطه با بازدهی و راندمان خواهد شد. اگر شما مي‌خواهيد اين مشكل را لمس كنيد، سعي كنيد چنين Query را در برنامه خود اجرا كنيد و نتيجه را در يك Data Grid صفحه بندي كنيد، و سپس حافظه مصرفي توسط پروسس aspnet-wp.exe را هنگام اجراي صفحه وب خود مشاهده كنيد) بنابراين نياز به يك راه حل صفحه بندي خاص است كه تنها اطلاعات ركودهاي همان صفحه را واكنشي كند. لازم به ذكر است كه صفحه بندي خود Data Grid همه ركوردها را واكشي مي‌كند و سپس در برنامه ASP.NET سعي در صفحه بندي و استخراج نتايج آن صفحه مي‌كند، كه همين مسئله مشكل صفحه بندي Data Grid است و مي‌بايست اين عمل در سطح بانك اطلاعاتي انجام و تنها ركوردهاي همان صفحه درخواستي به برنامه ASP.NET ارسال شود.
مقالات بسياري درباره اين مشكل و راه‌حل آن ارائه شده است. هدف من از اين مقاله اين نيست كه يك روش جديد و جالب را به شما نشان دهم بلكه بهبود روش‌هاي قديمي و فراهم آوردن يك برنامه براي تست انواع روش‌هاست تا شما بتوانيد روش مناسب خود را انتخاب كنيد. اين مقاله يك نقطه شروع خوب براي نشان دادن روش هاي مختلف و نيز نتايج كارائي آنهاست.
چگونه مي‌توان بوسيله Recordset صفحه بندي را انجام دهيم؟
من از بيشتر اين روش‌ها راضي شدم. اول اينكه نصف اين روش‌ها با ADO قديمي كه در مدل ASP قديمي نوشته شده بودند، استفاده مي‌كردند. بقيه نيز به صورت procedure Stored هايي SQL server پياده سازي شده بودند. بعضي از آنها زمان پاسخ بدي داشتند كه شما مي‌توانيد در انتهاي اين مقاله نتايج تست چند روش را مشاهده كنيد.
نتيجه گيري:
من تصميم گرفتم 3 روش را با دقت بررسي كنم. كه اين روش‌ها توسط نويسندگان آنها با نام‌هاي TempTable ، Dynamic SQL و Row Count نام گذاري شده است. من به روش دوم در اين مقاله نام Asc – Desc را مي‌دهم زيرا من فكر نمي‌كنم كه Dynamic SQL نام مناسبي باشد، چون شما مي‌توانيد به بقيه روش‌ها نيز نام Dynamic SQL را بدهيد.
يك مشكل عمومي با هر سه اين procedure Stored ها اينست كه شما مجبوريد كه تعيين كنيد كه كدام ستون (Cloumns) را شما مي‌توانيد sort كنيد و كدام را نمي‌توانيد. شايد فقط مي‌توانيد فيلدهاي كليد اصلي را بتوانيد sort كنيد. اين مسئله شامل مجموعه‌ايي از مشكلات است. براي هر Query كه شما مي‌خواهيد نتايج آن را صفحه بندي كنيد، ستون‌هاي متفاوتي بايد sort شوند.
اين به اين معني است كه شما براي هر كدام procedure Stored هاي مختلفي (صرف نظر از اينكه كدام روش صفحه بندي را استفاده مي‌كنيد) براي sort هر ستون داريد و يا سعي مي‌كنيد يك procedure Stored كلي با استفاده از dynamic SQL بنويسيد.
اما در بعضي از موارد فقط امكان تعميم sp به يك سطح معين است و به صورت كلي و بنابراين ما مجبور خواهيم شد كه sp هاي مجزايي براي بعضي از Query هاي پيچيده بنويسيم.
مشكل دوم اجازه دادن به ساير ستون‌ها براي شركت در sorting در كنار كليد اصلي است. و اگر آن ستون‌ها index نشده باشند در بعضي روش‌ها اين امكان وجود نخواهد داشت. در تمام روش‌ها منبع صفحه بندي، بايد ابتدا مرتب سازي (sort) شود و هزينه استفاده از مرتب سازي بوسيله ستون‌هاي index نشده، براي جداول بزرگ بسيار زياد خواهد بود. به طوري كه زمان‌هاي پاسخ آنقدر زياد بودند كه در اين موارد عملاً غير كاربردي مي‌شدند (زمان‌هاي پاسخ از چند ثانيه تا چند دقيقه بسته به سايز جداول و ركورد شروع واكشي، مي‌باشد)
index كردن ستون‌هاي ديگر موجب افزايش بازدهی در اين موارد مي‌شود ولي ممكن است نامطلوب باشد. مثلاً هنگامي كه شما روزانه داده‌هاي زيادي را وارد كنيد، چندان مطلوب نخواهد بود.
Temp Table :
اولين روشي كه مي‌خواهم به بررسي آن بپردازيم Temp Table است. اين روش واقعاً به صورت گسترده‌ايي استفاده مي شود و من مراتب زيادي به آن برخورد كرده‌ام اينجا يك مقاله ديگر است كه اين روش را توضيح مي‌دهد و يك مثال براي چگونگي صفحه بندي سفارش شده براي استفاده در Data Grid نشان مي‌دهد.
روش ها در هر دو مقاله مي‌توانند بوسيله كپي داده‌هاي كليد اصلي در يك جدول موقت بهينه شود و سپس با Query اصلي join شود. بنابراين، اساس اين روش مي‌تواند به صورت زير باشد.
CREATE TABLE #Temp (
ID int IDENTITY PRIMARY KEY,
PK /* here goes PK type */
)
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 End row) ، اما اين روش براي بعضي موارد بد است. مثلاً براي يك دول با 1 ميليون ركورد، شما بايد تا انتهاي 1 ميليون ركورد را در جدول موقت كپي كنيد. با توجه اين موارد و نتايج بالا من تصميم گرفتم اين روش را از تست خودم حذف كنم.
Asc – Desc :
اين روش از ترتيب اوليه در يك sub Query استفاده مي‌كند و سپس ترتيب معكوس را به آن اعمال مي‌كند.
قاعده كل آن به صورت زير است:
DECLARE @temp TABLE (
PK /* PK Type */ NOT NULL PRIMARY
)
INSERT INTO @temp
SELECT TOP @PageSize PK FROM (
SELECT TOP (@StartRow + @PageSize)
PK,
SortColumn /*If sorting column is defferent from the PK, SortColumn must
be fetched as well, otherwise just the PK is necessary */
ORDER BY SortColumn /* default order – typically ASC */)
ORDER BY SortColumn /* reversed default order – typically DESC */
SELECT ... FROM Table JOIN @Temp temp ON Table.PK = temp.PK
ORDER BY SortColumn /* default order */
Row – Count :
استدلال ساده اين روش تكيه كردن به عبارت SET Raw Count براي ناديده گرفتن سرحهاي ناخواسته و واكشي سطرهاي مورد نياز است.
DECLARE @Sort /* the type of the sorting column */
SET ROWCOUNT @StartRow
SELECT @Sort = SortColumn FROM Table ORDER BY SortColumn
SET ROWCOUNT @PageSize
SELECT ... FROM Table WHERE SortColumn >= @Sort ORDER BY SortColumn
Sub Query :
2 روش اضافه‌تر نيز من در بررسي خودم لحاظ كردم و از منابع ديگري آنها را پيدا كردم. اولين آنها روش معروف triple Query و يا روش sub Query است، اصلي‌ترين روشي كه من در مقاله زير پيدا كردم.
قاعده كل آن به صورت زير است:
SELECT ... FROM Table WHERE PK IN
(SELECT TOP @PageSize PK FROM Table WHERE PK NOT IN
(SELECT TOP @StartRow PK FROM Table ORDER BY SortColumn)
ORDER BY SortColumn)
ORDER BY SortColumn
Cursor :
من آخرين روش را هنگام جستجو در گروه‌هاي google پيدا كردم. شما مي‌توانيد اين روش را از اينجا مطالعه كنيد. اين روش از اشاره گر پوياي سمت كلانيت استفاده مي‌كند. افراد زيادي ميل دارند كه از اشاره‌گرهاي سمت سرود استفاده نكنند، آنها معمولاً بازدهی ضعيفي دارند به دليل اينكه بانكهاي اطلاعاتي آنها غير رابطه‌اي و حالت ترتيبي است.
مطلبي كه وجود دارد اين است كه صفحه بندي يك عمليات ترتيبي است و هر روشي شما را مجبور به رسيدن به سطر شروع مي‌كند. در همه روش‌هاي قبلي كه گفتم اين عمل با انتخاب همه سطرهاي قبل از سطر شروع به اضافه سطرحهاي مورد نياز و سپس ناديده گرفتن تمام سطرهاي پيشين سطر شروع انجام مي‌گرفت. اشاره‌گر پويا گزينه fetch Relative را دارد كه پرسش جالبي را انجام مي‌دهد و اصول كلي آن به صورت زير است.
DECLARE @PK /* PK Type */
DECLARE @tblPK TABLE (
PK /* PK Type */ NOT NULL PRIMARY KEY
)
DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
SELECT @PK FROM Table ORDER BY SortColumn
OPEN PagingCursor
FETCH RELATIVE @StartRow FROM PagingCursor INTO @PK
WHILE @PageSize > 0 AND @@FETCH_STATUS = 0
BEGIN
INSERT @tblPK(PK) VALUES(@PK)
FETCH NEXT FROM PagingCursor INTO @PK
SET @PageSize = @PageSize - 1
END
CLOSE PagingCursor
DEALLOCATE PagingCursor
SELECT ... FROM Table JOIN @tblPK temp ON Table.PK = temp.PK
ORDER BY SortColumn
تعميم Query هاي پيچيده:
در ادامه نكاتي كه قبلاً گفتم، تمام پروپيجراي استفاده شده با Dynamic SQL تعميم داده شده‌اند. بدين معني كه، در تئوري آنها مي‌توانند با هر نوع Query پيچيده ايي كار كنند. اين يك مثال از Query هاي پيچيده است كه روي بانك اطلاعاتي Northwind اجرا مي‌شود.
SELECT Customers.ContactName AS Customer,
Customers.Address + ', ' + Customers.City + ', ' +
Customers.Country AS Address,
SUM([Order Details].UnitPrice*[Order Details].Quantity) AS
[Total money spent]
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
WHERE Customers.Country <> 'USA' AND Customers.Country <> 'Mexico'
GROUP BY Customers.ContactName, Customers.Address, Customers.City,
Customers.Country
HAVING (SUM([Order Details].UnitPrice*[Order Details].Quantity))>1000
ORDER BY Customer DESC, Address DESC
فراخوانی SP صفحه بندی که رکوردهای صفحه دوم را برمی گرداند شبیه زیر است:
EXEC ProcedureName
/* Tables */
'Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID',
/* PK */
'Customers.CustomerID',
/* ORDER BY */
'Customers.ContactName DESC, Customers.Address DESC',
/* PageNumber */
2,
/* Page Size */
10,
/* Fields */
'Customers.ContactName AS Customer,
Customers.Address + '', '' + Customers.City + '', '' + Customers.Country
AS Address,
SUM([Order Details].UnitPrice*[Order Details].Quantity) AS [Total money spent]',
/* Filter */
'Customers.Country <> ''USA'' AND Customers.Country <> ''Mexico''',
/*Group By*/
'Customers.CustomerID, Customers.ContactName, Customers.Address,
Customers.City, Customers.Country
HAVING (SUM([Order Details].UnitPrice*[Order Details].Quantity))>1000'
يك نكته ديگر اينكه در پرس و جوي اصلي در قسمت CRDER BY از aliases استفاده شده است. اما شما نمي‌توانيد در sp صفحه بندي چنين كاري را انجام دهيد، زيرا كاري است كه بيشترين ميزان زمان را مصرف مي‌كند. در تمام روش‌ها پيمايش و ناديده گرفتن سطرها قبل از سطر شروع است. اين عمل به روش‌هاي مختلفي انجام مي‌گيرد اما قاعده كلي آن اين است كه در ابتدا همه فيلدهاي مورد نياز واكشي نمي‌شوند، بلكه ستون‌هاي كليد اصلي (در روش Row – Count ستون مرتب سازي) واكشي مي‌شوند كه اين باعث افزايش سرعت مي‌گردد. تمام فيلدهاي مورد نياز براي ركوردهايي كه مربوط به صفحه درخواست شده مي‌باشند، فقط واكشي مي‌شود. بنابراين aliases تا پرس و جوي پاياني وجود ندارد و ستون‌هاي مرتب سازي از ابتدا استفاده مي‌شوند (در پيمايش و ناديده گرفتن ركوردها تا سطر شروع)
نتايج تست بازدهی:
من از اين 4 روش در تست خودم استفاده كردم مي‌خواهم اين 4 روش را با هم مقايسه و ميزان زمان پاسخ درخواست يك صفحه را اندازه گيري كنم. با اين حال، اين زمان نمي‌تواند زمان واقعي پاسخ باشد، بنابراين من آن را در يك برنامه Console نوشتم.
من همچنين يك برنامه web به پروژه خودم اضافه كردم، نه براي تست بازدهی بلكه بيشتر به عنوان يك مثال كه چگونگي صفحه بندي سفارش شده را با Data Grid به شما نشان دهم.
من از يك جدول بزرگ (كه سطرهاي آن به صورت خودكار توليد شده) براي تست خودم استفاده كردم و حدود 000/500 ركورد به آن اضافه نمودم اگر شما يك جدول بزرگ نداريد مي‌توانيد اين اسكريپت و sp را براي توليد خودكار اطلاعات و ركوردها از اينجا دانلود و استفاده كنيد.
من از ستون identify براي كليد اصلي خودم استفاده نكردم و به جاي آن از uniqueidentifier استفاده كردم. شما مي‌توانيد يك فيلد identify به جدول خود اضافه كنيد تا هنگامي كه صفحه‌اي را واكشي كرديد با ترتيب كليد اصلي، صحت صفحه دريافتي را با آن چك كنيد.
ايده پشت تست بازدهی اينست كه در يك حلقه دفعات زيادي stored procedure را فراخواني و سپس زمان‌هاي پاسخ متوسط اندازه گيري شود. همچنين به منظور حذف كردن اختلاف Coching و ايجاد حالت واقعي و دقت بيشتر، چندين فراخواني بر روي يك sp با واكشي همان صفحه در هر بار نامناسب است. بنابراين يك توالي تصادفي از اعداد با همان sp با يك مجموعه از اعداد مختلف نياز است.
