UNiComp.iR

معرفی:

صفحه بندی نتایج حاصل از اجرای جستجو روی بانک اطلاعاتی در برنامه‌های 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 با یک مجموعه از اعداد مختلف نیاز است.

نتایج تست: