سریعترین روش جستجو برای متون فارسی در Sql Server با رکورد های زیاد(چند میلیون رکورد)

[saeid1389]

با سلام به همه دوستان

دو راه زیر به نظرم میرسه که مشکلاتی به همراه دارند:

1- استفاده از دستور Like : برای چند میلیون رکورد چند دقیقه طول می کشه که مناسب نیست.
2- استفاده از Full Text Searching : که نیازه متون به طور صحیح وارد دیتابیس بشه، یعنی بین کلمات Space گذاشته بشه، تا کلمات به صورت صحیح شناسایی و ایندکس بشن.

یه دیتا بیس طراحی کردم که قراره متون تعداد زیادی کتاب داخل اون ذخیره بشه - این کار با Copy&Paste در فرم ورود اطلاعات انجام می شه - از اونجایی که متون داخل فایل ورد و از پیش تایپ شده اند دارای اشکال ذکر شده بالا هستند
به عنوان مثال دو عبارت ذیل رو در نظر بگیرید:
1- سریعترین روش جستجوبرای متون فارسی در Sql Server با رکورد های زیاد (چندمیلیون رکورد)
2- سریعترین روش جستجو برای متون فارسی در Sql Server با رکورد های زیاد (چند میلیون رکورد)

در عبارت شماره 1 : Sql Server کلمات " جستجوبرای " رو به عنوان یک کلمه در نظر می گیره و اونو ایندکس می کنه که اشتباه است.

از دوستان که کسی روشی برای جستجو سریع می دونه، لطفا راهنمایی کنه.:قلب:

[یوسف زالی]

سلام.
اگر مقادیر رو به صورت سورت شده وارد کنید و نوع رکورد رو به جای nvarchar نوع varchar بگیرید فکر کنم جستجو سریع تر میشه. (جستجو روی varchar از nvarchar سریعتره)
راهی که به ذهنم می رسه اینه که وقتی داریم متن رو وارد می کنیم به جای سورت کل جدول ، یک فیلد بگیریم به اسم اولویت که محل قرار گرفتن ردیف جاری رو در حالت سورت متن داره.
یک فیلد دیگه هم بگیریم که 20 تا کاراکتر اول رو تو خودش اینسرت کنه.
روش ایندکس میذاریم.
برای جستجو هم با این 20 تا جستجو می کنیم. (ممکنه سورت بودن یا نبودن این فرقی به حال سرعتش نداشته باشه - متاسفانه تست نکردم- این همه دیتا نداشتم)
نتیجه حاصله ممکنه مثلا 100 ردیف باشه که توش این 20 تا مشترکه.
حالا تو اون 100 تا کل متن رو جستجو می کنیم.

برای اون مشکل هم می تونید از replace(matn, ' ', '') -- hazfe space ha استفاده کنید.


حتی میشه اون 20 کاراکتر اول رو در یک جدول جداگانه بریزید که فقط همین ردیف رو داره با ID ردیف جدول اصلی.
این کار به شدت روی سرچ شما تاثیر مثبت داره.
لااقل برای من که داشت!

لطفا اگر به جواب رسیدید در همین جا مطرح کنید تا ما هم بهترین راه رو بدونیم.

[Reza_Yarahmadi]

راهی که به ذهنم می رسه اینه که وقتی داریم متن رو وارد می کنیم به جای سورت کل جدول ، یک فیلد بگیریم به اسم اولویت که محل قرار گرفتن ردیف جاری رو در حالت سورت متن داره.نمیدونم ایندکس گذاشتن روی فیلد اولویت چه تاثیری روی جستجوی متن یک فیلد دیگه داره ، مخصوصا در حالتی که باید تمام رکوردها اسکن بشن.

یک فیلد دیگه هم بگیریم که 20 تا کاراکتر اول رو تو خودش اینسرت کنه.
روش ایندکس میذاریم.
برای جستجو هم با این 20 تا جستجو می کنیم. (ممکنه سورت بودن یا نبودن این فرقی به حال سرعتش نداشته باشه - متاسفانه تست نکردم- این همه دیتا نداشتم)
نتیجه حاصله ممکنه مثلا 100 ردیف باشه که توش این 20 تا مشترکه.
حالا تو اون 100 تا کل متن رو جستجو می کنیم.این روش هم برای حالتی است که فقط بر اساس ابتدای متن بخواید جستجو انجام بدید ، که با توجه صورت مسئله جستجو در کل متن انجام میشه.

1- استفاده از دستور Like : برای چند میلیون رکورد چند دقیقه طول می کشه که مناسب نیست.
2- استفاده از Full Text Searching : که نیازه متون به طور صحیح وارد دیتابیس بشه، یعنی بین کلمات Space گذاشته بشه، تا کلمات به صورت صحیح شناسایی و ایندکس بشن.به هر حال هر روش محاسن و معایب خودش رو داره!
با توجه به اینکه متن ذخیره شده متن یک کتاب است یا باید از Full Text استفاده کنید یا عملیات ایندکس گذاری روی کلمات رو خودتون هندل کنید به این صورت که زمان ذخیره کردن یک کتاب تمام کلمات رو توی یک جدول دیگه ایندکس کنید ، زمان جستجو هم روی جدول دوم جستجو انجام بدید و با توجه به شماره کتابهای استخراج شده اونها رو نمایش بدید(کاری که موتورهای جستجو انجام میدن)

ولی در هر دو صورت مثالی که مطرح کردید بصورت جستجوبرای ذخیره میشه (در صورتی که سرعت رو بخواید باید کاربر اطلاعات رو درست وارد کنه)

[saeid1389]

به نظرم استفاده ار varchar به جای nvarchar تاثیری در تکنیک جستجوی sql نداشته باشه، فقط چون nvarchar دو برابر varchar نیاز به حافظه داره، ممکنه به علت حجم بیشتر روی سرعت تاثیر داشته باشه.
همونطوری که آقا رضا گفت : جستجو بایستی روی تمام متن انجام بشه، بنابراین بخشی از متن ، نتیجه درستی نداره ضمنا سورت کردن هم بی فایده است چون براساس اولین کلمه متن سورت می شه - باقی متن رو چیکار کنیم .

چون اطلاعات از قبل آماده شده و حجم زیادی هم دارند، زمان زیادی برای اصلاح نیاز هست، که مقدور نیست.

یه روش اینه که کلمات کلیدی متن استخراج بشه، در فیلد های جداگانه ریخته بشه بعدش هم ایندکس بشه، که البته برای چند میلیون رکورد زمانبره

از توجهی که کردید ممنونم.

[حمیدرضاصادقیان]

سلام.
ساختار جداول شما به چه شکلی است؟

[saeid1389]

ساختار جدول اصلی دیتا بیس:
قسمت های اصلی یک کتاب شامل مشخصات کتاب(نام کتاب، مولف و ...)، بخش، زیربخش و متن است.
از اونجایی که تکرار عنوان بخش، در رکوردها زیاد می شه، برای اون یه جدول جدا در نظر می گیریم و شماره رکورد اون رو در جدول اصلی ذخیره می کنیم.
بنابراین برای ساختار جدول اصلی داریم:
شناسه جدول اصلی(کلید اصلی، از نوع int )،
شناسه مشخصات کتاب(کلید خارجی،از نوع داده int) دارای ایندکس غیرخوشه غیر یکتا،
شماره عنوان بخش(کلید خارجی،از نوع داده int)دارای ایندکس غیرخوشه غیر یکتا،
عنوان زیر بخش(varchar) و
متن ( (varchar(max ) .

[حمیدرضاصادقیان]

خوب حالا اینجا یک سوال مطرح میشه. منظور از بخش های کتاب همون ایندکس اول کتاب هست؟
یعنی برهمون منوال ، اطلاعات داخل جداول قرار میگیرند؟

بعد جستجویی که شما انجام میدید روی کل جدول هست؟ یعنی روی همه کتابها جستجو انجام میشه یا فقط روی یک کتاب خاص این جستجو صورت میگیره؟

[saeid1389]

بله، اگه به فهرست کتاب نگاه کنید، اطلاعات به قسمت ها یی (بخش ها )تقسیم شده و هر قسمت هم قسمت های خودشو داره(زیربخش ها یا فصل ها ) و ... ، که ما فقط تا بخش ها و زیر بخش ها(فصل ها) جلو میریم بقیه میشه متن.
البته کتب با توجه به موضوعاتی که دارن به هفت موضوع تقسیم میشن.
عمل جستجو روی
1- یک موضوع،
2- ممکنه چند موضوع
3- و یا تمام موضوعات با هم(کل جدول)
انجام بشه.
در مورد شماره های 1 و 2 با استفاده از دستور WHERE دامنه جستجو محدود می شه، اما جستجو روی کل جدول اشکالاتی که در بالا ذکر شد رو داره، چون تعداد رکورد های کل جدول به حدود ده میلیون می رسه(قراره حدود بیست تا سی هزار جلد کتاب وارد دیتا بیس بشه)

[حمیدرضاصادقیان]

یکی از راهها میتونه استفاده از Table Partitioning باشه. که مثلا جدول شما روی دوتا سرور قرارداشته باشه و عمل جستجو به صورت Parallelism انجام بشه تا سرعت شما افزایش پیدا کنه.
میتونید نمونه Select و Execution Plan اونو اینجا قرار بدید تا دقیقتر بشه بررسی کرد؟

[saeid1389]

آقا حمید ممنون
موضوعاتی رو که مطرح کردید، پیگیری می کنم.

غیر از پارتیشن کردن جدول دیگه روشی وجود نداره؟

[حمیدرضاصادقیان]

این باید یک نمونه از دستورات شما باشه و Plan که ساخته میشه تا اونجا دقیق وضعیت ایندکسها بررسی بشه.

[saeid1389]

پروژه ام رو دوباره بررسی کردم، یه خرده طراحی رو تغییر دادم که نیاز به جستجو در کل دیتابیس نباشه - با محدود کردن جستجو به موضوعات و ... - و مشکل این پروژه حل شد.
اما مسئله همچنان باقی است.

یک ایده به ذهنم رسیده که تو تاپیک بعدی مطرحش می کنم. البته ایده خامه و نیاز به بررسی بیشتر داره.

[saeid1389]

در این باره هر چی فکر می کنم، آخرش به این نتیجه می رسم که Full Text Searching یه چیز دیگه است. وقتی برای جستجو تو چند میلیون رکورد با دستور Like چند دقیقه و با FTS چند ثانیه زمان صرف میشه، واقعا FTS یه چیز دیگه است.

به نظرم استفاده از همین امکان Sql Server گزینه خوبیه. برای رفع اشکال ذکر شده میشه بعد از Paste کردن اطلاعات در TextBox فرم ورود اطلاعات، با پردازش متن، HighLight کلمات به هم چسبیده و اصلاح آن کلمات توسط کاربر، قبل ورود اطلاعات به دیتا بیس مشکل رو حل کرد.
ضمنا تعداد این کلمات به هم چسبیده معمولا کم ، در هر صفحه ممکنه حدود 5 الی 7 مورد باشه( حالا یه خرده کمتر یا بیشتر ).

توضیح :
تمام کلمات بر اساس حرف آخرشان به دو گروه اصلی تقسیم می شن:
1- کلماتی که حرف آخر آنها حروفی است که اگه Space نزنیم به کلمه بعدی می چسیند. مثل:" آب باران" ، که بدون Space می شه " آبباران"، بنابراین حتما تایپیست بعد ازاین حروف از Space استفاده کرده
2- حروفی که نیاز به Space بعد از آنها نیست. مثل: "جستجوکردن" که صحیح آن "جستجو کردن" است(تعدادشون کمه: الف، دال، ذال، ر، ز، ژ، و )
پس ما با گروه دوم از کلمات کار داریم.

الگوریتم اون می تونه به شکل زیر باشه:
1- کاراکتر به کارکتر متن رابخوان و در آرایه بریز تا زمانیکه به Space برسی
2- اگه حروف گروه دو، در میان کلمه نبود ، اگر به انتهای متن رسیدیم خاتمه اسکن در غیر این صورت به مرحله 1 برو
3- اگه حروف گروه دو ، در میان کلمه بود (از ایندکس شماره یک آرایه به بعد-چون اگه حروف اول کلمه باشند مشکلی رو ایجاد نمی کنند) آن کلمه را در دیتابیس کلمات فارسی جستجو کن.
(دیتابیس کلمات فارسی: یه دیتا بیس درست می کنیم که کلمات فارسی رو درون اون جهت اعتبار سنجی کلمات حین اسکن، می ریزیم-می تونیم از فرهنگ لغت جهت یافتن کلمات استفاده کنیم- ضمنا می شه به مرور حین اسکن هم اونو تکمیل کرد)
4- اگر کلمه را در دیتابیس یافتی ، بنابراین کلمه معتبر است(از دو کلمه به هم چسبیده تشکیل نشده) به مرحله 1 برو، در غیر این صورت آن کلمه رو HighLight کن.

پس از پایان این اگوریتم یه سری کلمات HighLight شده داریم که کاربر به سادگی با نگاه به کلمات و در صورت نیاز قرار دادن Space بین آنها اصلاح رو انجام می ده.

البته خطا هم ممکنه داشته باشه، اما درصد اون پایینه و قابل چشم پوشی.