‫‫استفاده از Stored Procedure و Trigger در NHibernate و تاثیر آنها بر Caching

در رابطه با NHibernate رایج است که هر جا حس کردیم NHibernate یا مهارت خودمان در استفاده از آن دچار محدودیت است فوراً دست به کار شده و ضمن دور زدن مکانیزم NHibernate session managment مستقیماً از SQL در برنامه‌مان استفاده کنیم. مثلاً وقتی که نمی‌توانیم از eventهای مرتبط با Save/Update در NHibernate به درستی استفاده کنیم یک تریگر روی جدول مورد نظر می‌گذاریم. یا مثلا وقتی که سرعت update جدول یا فیلدی خیلی پایین است، حالا یا به علت محدودیت‌های خود NHibernate یا به علت عدم استفاده صحیح خودمان، آن وقت یک Stored Procedure نوشته و عملیات مورد نظر را به جای NHibernate از طریق آن انجام می‌دهیم.

این نوع دور زدن NHibernate به غیر از این که نرم‌افزار را از لحاظ فناوری دو تیکه و ناخوانا می‌کند، مشکل دیگری نیز دارد. چون این طور کارها از دروازه NHibernate session management رد نمی‌شوند، NHibernate هم از تغییرات داده‌ای آنها بی‌خبر مانده و نمی‌تواند Cache را (چه سطح اول و چه سطح دوم) به خوبی به روز رسانی کند. در نتیجه مقادیر موجود در Cache نامعتبر شده و آنچه که از Cache دریافت می‌داریم ممکن است قدیمی و به درد نخور باشد. چون یک تریگر یا sp دور از چشم NHibernate آنها را تغییر داده است. در این طور مواقع تنها کاری که می‌توان کرد عدم استفاده از روش‌های این چنینی یا چشم پوشی از Caching می‌باشد.

‫نگهداری داده‌های حجیم در MS SQL

در خانواده MS SQL نسخه‌های ۲۰۰۰، ۲۰۰۵ و ۲۰۰۸ تعدادی فیلد خاص برای کار با داده‌های حجیمی مثل عکس یا متون خیلی طولانی وجود دارد. هر کدام از اینها شرایط خاص خودشان را دارند که در ادامه بررسی خواهند شد.

char و varchar: حداکثر ۸۰۰۰ کاراکتر

varchar(max)‎: حداکثر ۲ میلیارد کاراکتر – این نوع داده در SQL 2000 پشتیبانی نمی‌شود.

nchar و nvarchar: حداکثر ۴۰۰۰ کاراکتر (به طور کلی ظرفیت انواع یونیکد نصف انواع غیر یونیکد است)

nvarchar(max)‎: حداکثر ۱ میلیارد کاراکتر – این نوع داده در SQL 2000 پشتیبانی نمی‌شود.

binary و varbinary: برای داده‌های غیر متنی و حداکثر ۸۰۰۰ بایت

varbinray(max)‎: برای داده غیر متنی و حداکثر ۲ گیگا بایت.

text: حداکثر ۲ میلیارد کاراکتر – این نوع داده قرار است به زودی حذف شود.

ntext: حداکثر ۱ میلیارد کاراکتر – این نوع داده قرار است به زودی حذف شود.

image: حداکثر ۲ گیگا بایت – این نوع داده قرار است به زودی حذف شود.

FileStream: نوعی ارتباط بین MS SQL و سیستم فایل ویندوز یعنی NTFS – این نوع داده در SQL 2000 پشتیبانی نمی‌شود.

Xml: نگهداری اطلاعات به صورت Xml – این نوع داده در SQL 2000 پشتیبانی نمی‌شود.

توصیه خود مایکروسافت برای نگهداری داده‌های حجیم استفاده از سه نوع varchar(max)‎ و nvarchar(max)‎ و varbinary(max)‎ است. زیرا انواع text و ntext و image دیگر پشیبانی نشده و در نسخ آتی MS SQL برداشته خواهند شد. جهت کسب اطلاعات بیشتر به اینجا و اینجا مراجعه کنید.

Common problems with Named SQL queries

Named SQL queries in NHibernate is a way that you can put a native SQL query in a .hbm.xml file and executed it via IQuery and GetNamedQuery. By working with this feature you may encounter some errors and exceptions. Here I have gathered some common problems that I have seen by myself.

NHibernate.MappingException : unknown class

Occurs when return clause of XML mapping file defines a class that is not defined in the domain. This error does not allow anything work.
—-

NHibernate.HibernateException : Errors in named queries: {QUERY_NAME}

Occurs when return clause of XML defines a class that the class exists but its mapping does not exists. This error does not allow anything work.

NHibernate.ADOException : could not execute query ****
System.IndexOutOfRangeException : COLUMN_NAME

When a column is defined in return clause but does not exists in select clause of query.

NHibernate.ADOException : could not execute query
System.IndexOutOfRangeException : Recstatus

When named query return an entity that is inheriting from another entity but sql query does not return all columns that is used by both entity itself and its parent.

‫Paging کوئری‌های SQL در NHibernate

تصور کنید تعدادی کوئری SQL دارید که می‌خواهید Paging را با استفاده از NHibernate در آنها فعال کرده و بنا به دلایلی اجازه بازنویسی آنها با دیگر APIهای NHibernate نداشته و آنها را صرفاً باید از طریق ObjectDataSaource به کنترل‌های ASP.NET بخورانید. سه راه برای انجام این کار وجود دارد.

راه اول:
استفاده از stored procedureهای کمکی که خاص این قضیه ایجاد شده‌اند. یکی از آنها Paging_RowCount است که توسط دوستم مسعود رمضانی معرفی شده. این ابزارها queryی (و احتمالاً stored procedure) شما دریافت کرده و Paging را روی آنها اعمال می‌کنند. بدی این ابزارها این است که محدودیت‌های زیادی در query دریافتی اعمال و کرده و در خیلی از حالات کار نمی‌کنند. بدی دیگر آنها عدم خوانایی و قابلیت نگهداری پایین آنهاست چون Query ورودی این ابزارها معمولاً به صورت تیکه تیکه در داخل کد C#‎ نگهداری می‌شود. البته من چندان تخصصی روی SQL ندارم و با کل این ابزار به طور کامل کار نکرده‌ام اما همین دلیل دوم برای من کافی است تا از آن دوری کنم.

راه دوم:
استفاده از Named SQL Queries در NHibernate است. در این روش شما می‌توانید با استفاده از SetFirstResult و SetMaxResults وظیفه Paging را به عهده NHibernate بگذارید. در این حالت NHibernate خودش به طور خودکار و پشت پرده مشابه روش اول عمل کرده و Paging را برای شما مهیا می‌نماید. این روش بسیار زیبا، راحت، خوانا و قابل نگهداری است اما بدی آن این است که در بعضی حالات که Query کمی پیچیده می‌شود عمل نمی‌کند. یعنی این که بیشتر برای Queryهای ساده مناسب است.

راه سوم:
وقتی که نمی‌خواهید از روش اول استفاده کنید و روش دوم هم درست کار نمی‌کند مجبورید خودتان وظیفه Paging را به عهده بگیرید. یعنی کاری را که روش‌های اول و دوم به صورت خودکار انجام می‌دادند شما به صورت دستی انجام دهید. روش معمول برای Paging در MS SQL Server که راه‌های اول و دوم هم از آن استفاده می‌کنند روش ROW_NUMBER()‎ است. من خودم برای انجام این کار از یک Query حاضر و آماده که توسط NHibernate تولید می‌شد استفاده می‌کنم. به نمونه اسکریپت زیر توجه فرمایید:


SELECT TOP (@maxResults) * FROM (

select

*

,ROW_NUMBER() OVER(ORDER BY (@orderBy)) as _sort_row from

Toy

)as query WHERE query._sort_row > @firstResult
ORDER BY query._sort_row
البته من همین query را هم در داخل یک Named SQL Query قرار داده‌ام ولی به جای فراخوانی توابع SetFirstResult و SetMaxResults از پارامترهایی که خودم به همین اسامی در query تعبیه کرده‌ام استفاده می‌کنم.

‫استفاده مستقیم از SQL در NHibernate

NHibernate یک ORM است که شما را ترغیب می‌کند برای دسترسی به دیتابیس از APIهای خاص خودش یعنی HQL، ICriteria و Linq-to-NHibernate استفاده کنید. اما راه را برای آنها که به هر دلیل ترجیح می‌دهند یا مجبورند از SQL استفاده کنند نبسته است. NHibernate دو راه برای انجام این کار دارد: استفاده از ISession.CreateSQLQuery و استفاده از Named SQL queries.

استفاده از راه حل اول خیلی سر راست است ولی چون برنامه‌نویس را تشویق می‌کند کوئری SQL را در متن کدهای C#‎ نگهداری کند توصیه نمی‌شود. در عوض راه حل دوم یعنی Named SQL queries تمیزتر و قابل انعظاف‌تر عمل می‌کند. در این روش شما Queryهای مورد نظر را درست مشابه mappingها در فایل‌هایی با پسوند ‎.hbm.xml نگهداری می‌کنید. به این ترتیب هم کدهای C#‎ و اسکریپت‌های SQL با هم قاطی نمی‌شوند و هم ممکن است بتوانید از یک سری بهینه‌سازی‌ها و cachingهای SQL Server بهره‌مند شوید.

به عنوان یک نمونه از Named SQL queries به مورد زیر توجه فرمایید. منتها توجه داشته باشید که فایل query حتماً باید به صورت Embedded Resource کامپایل شده و dll آن به کمک AddAssembly به فهرست Assemblyها اضافه شده باشد.














برای کسب اطلاعات بیشتر به Documentation مربوط به NHibernate در nhforge مراجعه کنید.

‫داستان بی‌سوادی ما – ۵: SQL Server Profiler‫

یکی از ابزارهایی که هم برای خطایابی خیلی استفاده می‌شود و هم برای پیدا کردن مشکلات سرعت Queryهای دیتابیسی، ابزار SQL Server Profiler است. من هم از این ابزار خیلی استفاده کرده و می‌کنم. اما مشکل من با این ابزار حجم زیاد log تولیدی آن است. گاهی اوقات برای دیدن یک Query خاص باید چند صد رکورد را بالا و پایین کنم تا به رکورد مورد نظرم برسم. هر چند که این مشکل خیلی آزار دهنده بود ولی چون چاره‌ی دیگری نداشتم مجبور بودم همچنان از Profiler استفاده کنم.

اما همین چند وقت از طریق مطلبی در وبلاگ وحید و نیز توضیحات یکی از دوستان فهمیدم که می‌شد با یک تنظیم بسیار کوچک، حجم log مربوطه را به شدت کاهش داده و خیلی سریع‌تر به اطلاعات مورد نظرم دسترسی پیدا کنم.

نمی‌دانم این بی‌سوادی ناقابل تا حالا چند صد ساعت وقتم را تلف کرده است. اما به هر حال نحوه‌ی فیلتر کردن اطلاعات بسیار ساده بوده و کافی بود تا از طریق برگه‌ی Event Selection، اطلاعات را بر حسب «نام کاربری» و… فیلتر کنم. برای کسب اطلاعات بیشتر به اصل مطلب در وبلاگ وحید مراجعه کنید.

این مطلب جزیی از سری نوشته‌های «داستان بی‌سوادی ما» است.

Paging problem with Named SQL queries

Named SQL query in NHibernate is an easy way to apply paging through native SQL. SetFirstResult and SetMaxResults helps a lot here. But the problem is when query is get a bit complicated or for some other reasons that I don’t know, paging does not works on pages after first page. In this case a wrong sql query is generated.

From given answers I didn’t realized what’s the root cause and if there is any solutions or not. But because I was in emergency I used my work-around for it.

My work-around is adding paging elements directly in the query and pass “first result” and “max results” to the query as parameters instead of using SetFirstResult and SetMaxResults. Consider following example:





default-access="field.camelcase"
namespace="nhtest"
assembly="nhtest">



SELECT TOP (:maxResults) * FROM (

select

*

,ROW_NUMBER() OVER(ORDER BY (:orderBy)) as _sort_row from

Toy

)as query WHERE query._sort_row > :firstResult
ORDER BY query._sort_row

]]>





This code is inspired from script that NHibernate itself produces while correct paging. Parametrization “top” and “order” sections was not very easy. I got helped from here and here. Notice parametrized “order” is not necessary for paging. It was my own requirement.

‫تاثیر Index گذاری بر سرعت جداول MS SQL

si2 چند روزی را در حال بررسی سرعت Query بر روی جداول حجیم بودم. Query مورد نظر من یک sum ساده بر روی جدولی به اسم amort بود. این Query به صورت یک function پیاده سازی شده بود. البته آن Query نهایی که من زمان آن را بررسی کرده و نتایجش را در نظر داشتم این function را همراه با یک select ساده صدا می زد. خود این select آخر روی یک جدول با ۷۰۰۰ رکورد اجرا می‌شد. در نتیجه اگر فرض کنیم جدول amort دارای ۳۰۰ رکورد است پس ما عملا با انجام عملیات روی ۳۰۰ ضرب در ۷۰۰۰ یعنی ۲۱۰۰۰۰۰ رکورد طرف هستیم. تنها عاملی که آن را در آزمایشات مختلف تغییر می‌دادم تعداد رکوردهای جدول amort (که function مورد نظر من روی آن اجرا می‌شد) و ایندکس داشتن و نداشتن همان جدول amort بود. تمام آزمایشات بر روی یک کامپیوتر Pentium 4, 2.26 Ghz, 1GB RAM, Win Xp SP2 و Microsoft Sql Server 2008 انجام شده است.

نتایج بدون ایندکس واقعا فاجعه بار بودند. برای ۷۲۰۰ رکورد ۲۱۳ ثانیه طول کشید! این در حالی بود که هر سال همین تعداد رکورد به جدول مورد نظر من یعنی amort اضافه می‌شد. به عبارتی دیگر اجرای Query مورد نظر برای سال هشتم حدود نیم ساعت کشنده طول می‌کشد! با این که روی همه جداول دیتابیس بر روی ستون id ایندکس وجود داشت به علت کندی بیش از حد مجبور شدم باز هم به ایندکس‌ها، انواع آن و تاثیرشان بر کارایی فکر کنم. در MS SQL چهار نوع ایندکس وجود دارد که من فقط دو تای پر استفاده‌تر آن را مطالعه و بررسی کردم: Clustered Index و Non-Clustered Index. هر دوی آنها اطلاعات را به صورت sort نگهداری می‌کنند. بر اساس توضیحات اینجا، نوع Clustered اطلاعات را به صورت فیزیکی به حالت sort نگه می‌دارد و در نتیجه در هر جدولی فقط یک ایندکس از این نوع مجاز است در حالی که نوع Non-Clustered از یک ساختار اضافه برای نگهداری اطلاعات sort جدول استفاده می‌کند و می‌توان حدود اقلا ۲۵۰ ایندکس از این نوع در یک جدول داشت. در بعضی منابع خوانده بودم که performance ایجاد شده در هر دو نوع تقریبا یکسان است در حالی که در آزمایشات خودم فهمیدم سرعت Clustered حدود ۱۰ برابر Non-Clustered است. دقت کنید تعریف یک یا چند ستون به عنوان Primary Key باعث می‌شود آن ستون یا ستون‌ها خود به خود به یک Clustered Index تعریف شوند. در همه انواع ایندکس‌ها دو مفهوم ReOrganize و ReBuild وجود دارد که مفهوم آنها را خیلی نفهمیدم ولی متوجه شدم که برای دستیابی به حداکثر کارایی هر از چندگاهی باید این دو عملیات را بر روی جدول مورد نظر اجرا کرد.

نتایج آزمایشم را با هر دو نوع ایندکس تکرار کردم. پرس و جو آن قدر سریع اجرا می‌شد که خیلی سریع تعداد رکوردها را از ۷۲۰۰ به ۷۲۰ هزار، یک و نیم میلیون رکورد و نهایتا ۳ میلیون رکورد رساندم. نتایج کار اگر من مرتکب اشتباهی نشده باشم واقعا شگفت آور بود: زمان مورد نیاز برای اجرا در حالت استفاده از Clustered Index کمتر از یک ثانیه و در حالت استفاده از None Clustered Index حدود ۱۰ ثانیه بود! دقت کنید که ۳ میلیون رکورد در جدول amort و ۷ هزار رکورد در select استفاده کننده function من یعنی ۲۱ میلیارد حالت!!

 

پ. ن.: برای تولید ۳ میلیون رکورد از مقادیر اتفاقی int و تاریخ در یک حلقه while استفاده کردم. نحوه تولید «تاریخ» به صورت random در اینجا و اینجا بحث شده است.