Full Text Search و اعداد یک رقمی
جستجوی تمام متنی (Full Text Search) یکی از قابلیت های اکثر پایگاه های داده میباشد که استفاده از اون در SQL Server بسیار ساده هست.
در این پست فرض میکنیم که Full Text Search رو میدونیم چیه و اون رو برای یک فیلد از جدول دیتابیس خودمون فعال کردیم. اگر با این موضو آشنایی ندارید یه سر به
این پست در وبلاگ حامد بنایی بزنید و با این قابلیت جستجو آشنا بشید.
در یک سناریو، دیتابیس ما حاوی اطلاعاتی در مورد فیلمهای سینمایی و نقد و بررسی اونها هست. نظرات کارشناسان و کاربران در مورد نقد فیلمها در یک فیلد به اسم comments قرار میگیره. این فیلد به علت حجم زیاد مطالبی که در خودش داره، برای سرعت بیشتر پرس و جو، قابلیت Full Text Search رو براش فعال کردیم. حالا میخوایم تو این فیلد جستجو کنیم که در مورد مثلا فیلم Matrix 2 چه صحبتهایی انجام شده. مشخص هست که از این عبارت استفاده میکنیم:
Select MovieName from Movies where Contains(comments,'"Matrix 2"')
به نتایج برگشتی از دیتابیس نگاه میکنیم و با کمی توجه میبینیم که فیلمهای Matrix 1 و Matrix 3 و اصلا تمام نظراتی که توشون از کلمه Matrix استفاده شده، در این جستجوی ما وجود دارند. این در صورتیکه که ما فقط به دنبال شماره 2 این فیلم بودیم. علت چیست؟
در Full Text Search برای اینکه حجم داده های ایندکس شده در دیتابیس پایین بیاد و سرعت پرس و جو بیشتر بشه، یک سری از کلمات توسط دیتابیس در نظر گرفته نمیشوند مثل: and ،or ،am ،is ،with ،not و... در SQL Server 2005 و ماقبل اون این کلمات رو بهش میگیم Noise Words که در داخل یک فایل متنی در آدرسی که دیتابیس اونجا نصب شده، وجود دارند. اما از ورژن 2008، نام این لیست به Stop Words تغییر کرده و دیگه تو یه فایل متنی نگه داری نمیشند و به یک جدول در داخل خود دیتابیس منتقل شدهاند.
نکته قابل توجه اینه که اعداد تک رقمی صفر تا نه [0-9] هم در این لیست وجود دارند. این یعنی دیتابیس هنگام جستجوی عباراتی که توشون عدد یک رقمی هست، اون عدد رو حذف میکنه و دنبالش نمیگرده اصلا! و دلیل اینکه تمامی فیلمهای Matrix در جستجوی ما پیدا شد، همینه و دیتابیس طوری رفتار میکنه که انگار اصلا شماره دویی وجود نداره تو عبارت جستجوی ما.
راه حل چیست؟
یک:
میشه فایل متنی noise words رو باز کرد و این ارقام تک رقمی رو از توش پاک کرد و اون رو دوباره ذخیره کرد. اما دسترسی به این فایل فقط برای کسی مقدوره که دسترسی به فایلهای سرور دیتابیس رو داشته باشه، این دسترسی به ما هیچ گاه داده نمیشه مگه اینکه صاحب اون سرور باشیم و یا اینکه اون سرور رو اجاره کرده باشیم، اما در حالت معمول که ما تنها یک اکانت از یک دیتابیس رو اجاره میکنیم، سطح دسترسی به فایل سیستم اون سرور به ما داده نمیشه. پس این راه حل اکثرا به درد ما نمیخوره.
البته در sql server2008 این مشکل حل شده و همونطور که گفتم این کلمات اضافه در داخل یک جدول وجود دارند که با داشتن سطح دسترسی مناسب به اون جدول میتونیم از update، select و delete به سادگی استفاده کنیم.
نکته: بعد از تغییر این لیست، حتما باید کاتالوگ مربوطه در full text search دوباره سازی (Rebuild) بشه.
دو:
میتونیم صبر کنیم تا شماره 10 این فیلم بیاد، اونوقت با خیال راحت جستجو میکنیم!!
سه:
این راه به ذهن خودم رسید و بعد از چند آزمایش دیدم به کار من میخوره و از اون استفاده کردم؛ راه های دیگه ای اگه شما میدونین لطفاً اینجانب رو بی نصیب نگذارید :دی
عبارت زیر رو در نظر بگیرید:
Select MovieName from Movies where Contains(comments,'"Matrix 2"')
AND comments like '%Matrix 2%'
مشکل جستجو با این عبارت حل میشه، یعنی استفاده از قابلیت جستجوی متنی contains و بعد از اون استفاده از جستجوی کاراکتری با استفاده از like و علامت درصد. اما استفاده از like امکان داره زمان جستجوی ما رو بالا ببره و کارایی رو پایین؟ اگه قراره استفاده از این عبارت پرس جو، کارایی رو پایین بیاره دیگه اصلا چه نیازی به Full Text Search بود؟
یک آزمایش بر روی یک یتابیس با مجموع 5000 رکورد انجام دادم و زمانهای اونها به ترتیب زیر در اومد:
پرس و جو فقط از طریق like
زمان: 3 ثانیه
پرس و جو از طریق contains ( که در این حالت عدد تک رقمی جستجو نمیشود)
زمان: لحظه ای، بلافاصله بعد از اجرای دستور
پرس و جو از طریق contains و بعد از آن استفاده از like ( که در این حالت عدد تک رقمی نیز جستجو میشود)
زمان: لحظه ای، بلافاصله بعد از اجرای دستور
بنابر این تست، رفتار دیتابیس وقتی که ابتدا از contains استفاده میکنیم و سپس like رو در مورد همون فیلد به کار میبریم، متفاوت از حالتیه که فقط از like استفاده میکنیم. من هم در برنامه ای که داشتم از همین روش استفاده کردم و مشکلی هم در سرعت و کارایی نداشتم.
برای این کار من فقط نیاز داشتم کلماتی که تو اونها یک عدد تک رقمی وجود داره رو از طریق دستور جدید برای پرس و جو به دیتابیس بفرستم و در بقیه حالات از همون contains به تنهایی مثل قبل استفاده کنم.
برای تشخیص کلمه ای که در اون یک عدد تک رقمی وجود داره، از RegEx استفاده کردم.
^.*\s+\d{1}($|\s+.+)$
در نهایت به صورت زیر کد رو میشه نوشت در ساده ترین حالت:
Dim Pattern As String = "^.*\s+\d{1}($|\s+.+)$"
If RegularExpressions.Regex.Match(keyword_to_search, Pattern).Success Then
' query with Contains AND Like
Else
' query with Contains
End If
*این مطالب از خودم میباشد! اگر در اون مورد اشتباهی میبینید، ممنون میشم که اعلام کنید و استفاده از این مطلب آزاد هست و اگر دوست دارید با انصاف باشید، منبع رو هم ذکر کنید.