مقدمه :
عمل صفحه بنديِ يک پايگاه داده بزرگ در برنامه هاي وب مشکل شناخته شده اي است. بطور خلاصه شما نمي خواهيد که همه ي نتايج درخواستتان در يک صفحه وب نشان داده شوند، پس نوعي ترتيب نمايش صفحه اي مناسب است. در حاليکه اين کار در ASP ساده نبود. در کنترل DataGrid در ASP.NET اين کار را بصورت چند خط کد، ساده ميکند. پس PAGING در ASP.NET ساده است، ولي رفتار پيش فرضِ DataGrid به اين شکل است که همه نتايج ثبت شده از درخواستهاي شما از سرور SQL را دريافت و به برنامه ASP.NET فرستاده. اگر درخواست شما مليونها رکورد را باز گرداند باعث مشکلات جدي در اجرا مي شود. (اگر مطمئن نيستيد، اين درخواست را در برنامه وب اجرا کنيد و اشغال شدن حافظه در قسمت ASP.NET_WP.EXE را در برنامه Taskmanager ملاحظه کنيد). به همين خاطر راه کار PAGING هنگاميکه مي خواهيم تنها رديفها را از صفحه بگيريم نياز است.
مقالات و POSTهاي زيادي در رابطه با اين مشکل نوشته شده و چندين راه حل پيشنهاد شده. هدف در اينجا اين نيست که روشي پيشنهاد کنيم که تمامي مسائل را حل کند، بلکه قصد داريم روشهاي فعلي را بهبود بخشيم و برنامه آزمايشي طرح کنيم تا شما خود به تنهاي آنرا ارزيابي کنيد. اين يک مقاله مفيد براي شروع است که روشهاي متفاوتي را توضيح و چندين نتيجه آزمايشي را شرح مي دهد:
چگونه از طريق Recordset صفحه بندي انجام دهيم :
اولاً نيمي از روشها از ADO قديمي استفاده مي کنند و مخصوص ASP قديمي نوشته شده اند. بقيه متدها روند ذخيره سرور SQL هستند. برخي از آنها زمان پاسخ ضعيفي بدست مي آورند، همانطور که در پايين صفحه در نتايج اجراي مولف مي بينيد. ولي چند تا از آنها توجه مرا جلب کرده اند.
Generalization:
سه روشي که ما قصد دارم تا از نزديک آنها را مورد برسي قرار دهم TempTable, DynamicSQL, Rowcount هستند. در ادامه متن من از روش دوم استفاده خواهم کرد که ASC-Desc نام دارد من فکر نمي کنم DynamicSQL نام خوبي بود، چون شما مي توانيد منطق DynamicSQL را به بقيه روشها اعمال کنيد. مشکل عام در تمام اين روشها اين است که بايد ارزيابي کنيد که کدام ستون را براي تنظيم (SORTING) انتخاب مي کند و به احتمال زياد فقط ستون PK نيست. اين مسئله باعث مشکلات جديدي مي شود، به اين دليل که براي هر درخواست که مي خواهيد از طريق PAGING نشان دهيد بايد به همان تعداد، درخواست PAGING بدهيد. که تعداد مختلفي ستونهاي مرتب داريد.
اين يعني يا شما روش ذخيره متفاوت براي هر ستون (بدون توجه به روش PAGING استفاده شده) داريد يا بايد آنرا به يک روش ذخيره با کمک Dynamic SQL تعميم دهيد.
اين کار اثر جزئي در اجرا دارد ولي اگر بخواهيد تعداد زياد درخواست از اين طريق انجام دهيد قابليت نگهداري(maintainability) افزايش پيدا خواهد کرد. پس ما سعي مي کنيم تا تمام روش هاي ذخيره شده در اين متن را با Dynamic SQL تعميم دهيم. ولي در برخي موارد اين امکان وجود دارد تا به سطح خاصي از تعميم برسيم، پس لازم است تا روشهاي مجزا ذخيره را براي درخواستهاي پيچيده بنويسيم.
دومين مشکل با اجازه مرتب کردن ستوني از غير ستون PK، در اين است که اگر تنها آن ستونها به جهتي ايندکس نشده باشند، هيچ کدام از اين متدها و روشها کارامد نخواهند بود. در تمام اينها اول بايد يک منبع صفحه بندي ، مرتب شده باشد وهمانطور که مي دانيد، هزينه استفاده از مرتب سازي براي ستونهاي None-indexed بسيار گزاف است. زمان پاسخ دهي آنقدر بالاست که تمامي پروسيجرها در اين مورد در اصل بي فايده هستند. ( زمان پاسخ دهي از چند ثانيه به چند دقيقه بسته به اندازه جدول و زمان واکشي شروع رکورد، متغير است). فهرستوار کردن (Indexing) برخي ستونها مشکلات اجرا بيشتري بوجود مي آورند و ممکن است ناخواسته هم باشند. مثلاً اين امکان وجود دارد که سرعت شما در شرايطي که Import هاي روزانه زيادي داشته باشيد، پايين بيايد.
TempTable :
اولين موردي که روي آن بحث خواهم کرد متد temptable است. در حقيقت اين يک روشي است که بسيار پيشنهاد مي شود و من چندين با آن مواجه شدم.
اين مقاله ديگري است که به همراه مثال و توضيحات چگونگي استفاده از صفحه بندي با DataGrid را شرح مي دهد :
ASP.NET DataGrid Paging Part 2 - Custom Paging
اين متد در هر دو مقاله مي تواند، بوسيله کپي کردن داده PK در temptable و بعد الصاق آن با درخواست هاي اصلي، بهبود پيدا کند .
اين روش با کپي کردن ستونها به temptable بهبود پيدا مي کند تا زمانيکه به آخرين رديف برسيم (SELECT TOP EndRow…) ولي نکته اينجاست که در بدترين حالت - براي جدولي با يک ميليون رکورد شما يک ميليون رکورد در يک TempTable نيز خوهيد داشت. با توجه به نکات و با توجه به نتايج مقاله بالا تصميم گرفتيم تا اين روش را از آزمايش خود حذف کنيم.
Asc-Desc
اين متد از default ordering در subquery استفاده مي کند و سپس reverse ordering را بکار مي برد. دستور اينگونه است :
Full Code – Paging_Asc_Desc
RowCount
منطق اصلي اين روش بر اساس اصطلاح SQL SET ROWCOUNT است تا هم از رديفهاي ناخواسته صرفه نظر کند وهم آنهايي که نياز هستند را واکشي کند.
Full Code – Paging_RowCount
SubQuery
دو روش ديگر را نيز در نظر گرفته ام، و آنها از منابع مختلفي گرفته شده اند. اولي درخواست سه تايي معروف است با متد SubQuery.کاملترين روش، روشي است که در مقاله بعدي پيدا کردم.
Server-Side Paging with SQL Server
صفحه بندي از طرف سرور با استفاده از SQL Server :
گرچه بايد عضو شويد، ولي فايل a.zip با پروسيجرذخيره شده SubQuery موجود است. فايلِِ Listing_04.SELECT_WITH_PAGINGStoredProcedure شامل Dynamic SQL کامل است. من منطق کلي مشابه براي تمامي روشهاي ذخيره شده در اين متن استفاده کردم. در اينجا يک قاعده کلي به همراه لينکي به تمام پروسيجرها وجود دارد ( من کد اصلي را کمي کوتاه کردم، چون بخش شمارش رکوردها در هدف آزمايش من بي فايده بود).
Full Code – Paging_SubQuery
Cursor
من آخرين روش را هنگاميکه در google مي گشتم پيدا کردم، شما مي توانيد رشته اصلي را در اينجا پيدا کنيد. اين روش از cursor پوياي طرف سرور استفاده مي کند. بسياري از مردم از cursor هايي که قابليت اجراي ضعيف دارند به دليل طبيعت غير وابسته و طبيعت ترتيبي آنها دوري مي کنند. مسئله اين است که صفحه بندي يک کار ترتيبي است و از هر متدي که استفاده کنيد بايد به شکلي به رديف شروع باز گرديد. در تمامي روشهاي پيشين با انتخاب تمامي رديف ها قبل از رديف شروع به علاوه رديف مورد نظر اين کار انجام مي شود، که پس از آن تمام رديفهاي قبل حذف مي شوند.
Dynamic cursor قابليت FETCH RELATIVE را دارد که پرش جادويي انجام مي دهد. منطق اصلي اينگونه است :
Full Code – Paging_Cursor
Generalization of Complex Queries :
تعميم درخواستهاي پيچيده :
همانطور که قبلاً اشاره شده همه روشها با Dynamic SQL تعميم داده مي شوند. پس در تئوري ، آنها مي توانند با تمام درخواستهاي پيچيده کار کنند. اين يک نمونه درخواست پيچيده اي است که با پايگاه داده Northwind کار مي کند.
فراخواني پروسيجر Paging که صفحه دوم را باز مي گرداند شبيه به اين است :
توجه داشته باشيد در درخواست اصلي، نامهاي مستعار در عبارتOrderby استفاده مي شوند و نمي توانيد در روش Paging اين کار را انجام دهيد، چون وقتگيرترين کار در بين همه اينها پرش از رديفهاي قبل به رديف اول است. اين از روش هاي مختلفي انجام مي شود ولي قانون اين نيست که همه فيلدهاي لازم را از اول واکشي کنيد و فقط ستونهاي PK بايد واکشي شوند (در زماني که از متد RowCount استفاده مي شود، ستونها مرتب واکشي مي شوند) که سرعت اين کار را بالا مي برند. تمام فيلدهاي مورد نياز فقط براي رديفهاي که مطعلق به صفحه درخواست شده هستند واکشي مي شوند. بنابراين نامهاي مستعار فيلدها تا آخرين درخواست وجود ندارند و ستونهاي مرتب، بايد زودتر استفاده شوند ( در درخواستهاي پرش رديف. row skipping queries).
مشکل ديگر روش Rowcount اين است که آن، طوري تعميم يافته که فقط با يک ستون درعبارت ORDER BY کار مي کند. همينطور براي روش هاي Asc-Desc و Cursor، اگرچه مي توانند با چند ستون مرتب کار کنند، ولي نياز است تا فقط يک ستون در PK وجود داشته باشد. حدس مي زنم که اين با Dynamic SQL بيشتري حل شود، ولي به نظر من ارزشي ندارد.گرچه اين شرايط احتمال وقوع زيادي دارند ولي خيلي عادي نيستند. حتي اگر هم باشند، شما هميشه مي توانيد يک پروسيجر جداگانه Paging، با دنبال کردن قانونهاي بالا بنويسيد.
Performance Testing
تست اجرا :
من اين چهار روش را در آزمايشاتم بکار بردم، اگر شما روش بهتري داريد خوشحال مي شوم درباره آن بدانم. عليرغم اين مي خواستم اين روشها را مقايسه کنم و کيفيت آنها را اندازه گيري کنم. اولين فکر اين بود که يک برنامه کاربردي تست Asp.net با صفحه بندي dataGride بنويسم و سپس پاسخ صفحه (page Response)را اندازه گيري کنم . با اين حال مدت زمان پاسخ را در روش هاي ذخيره نشان نمي دهد. پس برنامه Consol، به نظر مناسب تر مي رسيد. همچنين من يک برنامه کاربردي وب نه براي تست اجرا بلکه به عنوان مثال، که چگونه صفحه بندي معمولِ datagride با اين روشهاي ذخيره شده کار مي کند، را نيز استفاده کرده ام. که هر دوي آنها از راه حل Paging Test استفاده شده اند.
من از جدولي که بطور خودکار توليد شده (auto Generated) استفاده کردم و تقريباً 500000 رکورد را در آن وارد کرده ام. اگر شما جدول بزرگي نداريد تا با آن آزمايش کنيد، مي توانيد از طرح جدول ما و پروسيجرهاي ذخيره شده براي Generate کردن دادها استفاده کنيد.
من يک ستون ID براي PK نمي خواستم، پس در عوض از uniqueidentifier استفاده کردم. اگر از اين متن استفاده کنيد شايد بخواهيد بعد از ساختن جدول به آن شناسه يا ID اضافه کنيد. اعدادي که با Pk به ترتيب شده اند را اضافه کنيد و شما نشانه اي داريد که زمانيکه از روش Paging با ترتيب PK استفاده مي کنيد، صفحه صحيح واکشي شده است.
نظر به اينکه performance testing بر طبق آن بود، به اين شکل است که، پروسيجر خاصِ ذخيره را به دفعات زياد از طريق حلقه صدا مي زنيم و ميانگين زمان پاسخ را اندازه گيري مي کنيم. همينطور براي اينکه خطا و انحراف Cache را حذف کنيم و بخاطر اينکه شرايط واقعي را به طور دقيق نشان دهيم- چندين فراخواني، به يک پروسيجر ذخيره با صفحه مشابهِ واکشي شده ، بنظر کار نامناسبي است. پس به، ترتيب تصادفي از روش ذخيره شده مشابه، با تعداد صفحه هاي متفاوت نياز داريم.
درک اين مساله که، زمان پاسخ به فاصله اي که صفحه واکشي شده از مکان شروع Resultset بستگي دارد، موضوع چندان پيچيده اي نيست. هرچه رکورد شروع دورتر باشد از رکوردهاي بيشتري بايد پرش کنيم. و به همين دليل من 20 صفحه اول را در شکل نگذاشتم. در عوض از 2N سري صفحه استفاده کردم، ضمناً يک حلقه 1000 تايي هم تنظيم شده است. پس تقريباً هر صفحه حدوداً 1000 بار واکشي شد.
نتيجه :
اينها نتايجي است که من بدست آوردم. Paging_Results (MS Excell file)
نتيجه کلي :
روشها با ترتيب، از بهترين به اين شکل اجرا شده اند، Subquery, Asc-Desc ,Cursor, RowCount .
رفتار در بخشهاي پايين تر خيلي جالب بود، چون در بسياري از مواقع، شما به ندرت بيشتر از پنج صفحه اول را جستجو مي کنيد، پس روش subquery مي تواند نيازهاي شما را در اين مورد تآمين کند.
اينها همه به اندازه Resultset ، و پيش بيني اينکه هر چند وقت يکبار صفحات دور واکشي مي شوند، بستگي دارند. شما مي توانيد از ترکيبي از روشها نيز استفاده کنيد. خود من تصميم گرفتم تا از متد Rowcount در هر جايي که ممکن است استفاده کنم. که اين روش حتي براي صفحه اول هم بخوبي کار مي کند. منظور از "هرجايي که ممکن است " اين است که براي مواردي که تعميم دادن اين روش مشکل است، خود من از روش Cursor استفاده مي کنم ( احتمالاً به همراه Subquery براي چند صفحه اول).
دليل اصلي نوشتن اين مقاله اين بود که من از طرف انجمن برنامه نويسي حمايت مي شدم. تا چند هفته ديگر هم من روي يک پروژه جديد کار مي کنم. تحقيقات اوليه نشان داد که جدولهاي بزرگي وجود دارد و اين جدولها در درخواستهاي مشترکِ پيچيده مورد استفاده قرار خواهند گرفت و نتايج آنها در برنامه هاي کاربردي ASP.NET نشان داده خواهد شد (به همراه مرتب سازي و صفحه بندي). به همين دليل من وقت زيادي براي تحقيق و پيدا کردن بهترين روش Paging صرف کردم و فقط کيفيت اجرا براي من مهم نبود بلکه استفاده و نگهداري آن نيز جالب بود.
هم اکنون نتيجه اي که صرف اين تحقيقات کردم را مي گيرم. شما مي توانيد در پايين يک پست از طرف C. v. Berkel ببينبد که يک ايراد از روش Rowcount گرفته، که اين روش زماني که ستون منحصر به فرد و Unique نباشد جواب نخواهد داد. روش Rowcount در آزمايش من بهترين پاسخ را داشت ولي الان واقعاً در نظر دارم اصلاً از آن استفااده نکنم. در اکثر موارد بترتيب کردن ستونها (بجز ستون PK) منحصربه فرد نيست، که باعث مي شود روش Cursor که سريعترين و قابل اجراترين روش در اکثر شرايط است انتخاب کنم که مي تواند با روش Subquery براي چند صفحه اول استفاده شود و احتمالاً از روش Rowcount در ستونهاي منحصربه فرد مرتب .
مورد ديگر که ذکرآن پر ارزش است، ضعف روش ASC-Desc است. هميشه براي چند صفحه آخر تعداد PageSize رکوردهاي را برمي گرداند و نه تعداد حقيقي را ( که احتمال دارد کمتر از Pagesize باشد). تعداد صحيح قابل شمارش است ولي از آنجاييکه که قصد ندارم از اين روش استفاده کنم (بخاطر نحوه اجراي آن ) پس نيازي به گسترش بحث نيست.
معرفي سايت مرتبط با اين مقاله