کمک در مورد اندیس گذاری بهینه

[M * M * A]

کمک در مورد ایندکس کردن بهینه

با سلام خدمت دوستان عزیز
من یک جدول دارم که کلیه گزارشات را از این میگیرم.
فیلد هایی که قرمز کردم بصورت 100درصدی در گزارشات استفاده میشوند.
از تمام این فیلد ها در گزارشات استفاده میشود .
لازم به ذکر است که داخل این جدول بیش از 20 میلیون رکورد وجود دارد .
من برای افزایش سرعت جستجو ، چه نوع ایندکس گذاری یا پارتیشن بندی انجام بدم .
بیشتر گزارشات در بازه ماهیانه انجام میشه .
132013

[reza_ali202000]

دوست عزیز بزرگترین و زمانبر ترین کوئری که از این جدول میگیری رو انالیز کن خود اس کیو ال بهترین پیشنهادات رو بهت میده. سرعت به شدت افزایش پیدا میکنه.
موفق باشی

[M * M * A]

میشه بیشتر راهنمایی کنی ؟ اگه لینکی داری بفرس

[tooraj_azizi_1035]

سلام
یک نمونه کوئری با زمان اجرای زیاد قرار بدین بشه بحث کرد.

[M * M * A]

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

select
,Fn_GetShiftName (ShiftId) ShiftName
,Fn_GetAreaName (AreaId) AreaName
,Fn_GetUserName(UserId) UserName
,Sum(Debit) SDebit
,Sum(Tax)STax
,Sum(Deposite)SDeposite
from Transaction
where cityID=1
and FromDateTime between cast('2014-01-01 08:00' as DateTime) and cast('2014-02-01 23:59'as DateTime)
and AreaId in (100,101,102,103,202,205,300,350,401,402)
and ShiftId=1
and UserId in (1010,1011,1012,1015,1090,2020,1214,2321,6546,8440 ,3102,3609,3154,2148)

[pezhvakco]

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

میشه در برنامه SSMS و یا با Sql Profile کوئری های نوشته شده را ازمایش کرد.

میتونین درباره ساختار ایندکس و پارتیشن بندی اگاهی بدست بیاریند

ایندکس ها دو ساختار اصلی Clustered و Non-clustered دارند:
ایندکس Clustered اطلاعات را به صورت فیزیکی مرتب می کند.
ایندکس Non-clustered از همان ساختار مرتب سازی B-tree استفاده می کند , با 2 فرق.اول اینکه در Leaf این B-tree اطلاعات مربوط به رکوردها را ندارد . بلکه یک آدرس دارد که به Node مورد نظر در B-tree ایندکس Clustered اشاره می کند. و دوم اینکه اطلاعات بر مبنای کلید ایندکس Non-clustered شما مرتب و ذخیره نمی شوند.
=>http://barnamenevis.org/showthread.php?37664-Index-%D9%87%D8%A7

پارتیشن بندی :
بکارگیری این قابلیت که با بخش کردن محتوای یک جدول و قرار دادن آنها در چندین فایل برای جداول حجیم، به ویژه جداولی که داده‌های آن حاوی مقادیر تاریخچه‌ای است بکار میاد.
هر بانک اطلاعاتی در حالت پیش فرض دارای یک فایل داده‌ای (MDF.) و یک فایل ثبت تراکنشی (LDF.) می‌باشد.برای ذخیره سطر‌های داده‌ای از فایل‌های بیشتری به نام فایل‌های ثانویه (NDF.) کار می کشند . به همان طریق که در فایل سیستم، فایل‌ها به پوشه‌ها تخصیص داده می‌شوند، می‌توان Data File را به Filegroup تخصیص داد. چنانچه چندین Data File به یک Filegroup تخصیص داده شوند، داده‌ها در تمامی Data File‌ها به طریق Round-Robin توزیع می‌شوند.

و ....

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

[M * M * A]

ممنونم از پاسختون .

اگه شما جای بنده بودین کدوم فیلد ها و چه ایندکس گذاری انجام میدادین .

[Davidd]

سلام. در هر جدول فقط یک ایندکس clustered میشه تعریف کرد چون در این نوع ایندکس رکورد ها به صورت فیزیکی بر اساس ستون مورد نظر مرتب میشن. معمولا روی کلید اصلی این نوع ایندکس تعریف میشه و خود sql به صورت پیش فرض روی کلید اصلی ایندکس تعریف میکنه. پیشنهاد من اینه که روی کلید اصلی ایندکس clustered تعریف کنید و روی اون سه فیلد دیگه ایندکس nonclustered تعریف کنید. البته باید توجه داشت که ایندکس گذاری اگرچه سرعت جستجو (select) بالا میبره اما سرعت Insert و Update کم میکنه. بنابراین با توجه به شرایط مسئله، فراوانی کوئری ها ، نوع کوئری ها و اینکه سرعت در کدام نوع کوئری مهمه باید تصمیم بگیرید که ایندکس استفاده بشه یا نه.

[tooraj_azizi_1035]

پارتیشن بندی بر اساس FromDateTime به صورت سالانه انجام دهید و سپس ایندکسی به فرم زیر بسازید:

create nonculstered index ix__test on Transaction (FromDateTime, ToDateTime)
include (ShiftId, AreaId, UserId, Debit, Tax, Deposite)

اگر CityId=1 و ShiftId=1 بخش ثابتی از شرط کوئری شماست میتونید به ایندکس بالا این خط رو اضافه کنید تا تبدیل به Filtered Index شود:

WHERE CityId=1 AND ShiftId=1

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

اگر امکانش هست پلن اجرای کوئری بالا رو به همراه آمار I/O هم قرار بدید.

[M * M * A]

ممنونم از پاسخ زیباتون
من هنوز در مرحله طراحی هستم و گفتم از شما خوبان یک مشورت بگیرم.
در توابع نیز یک دستور select معمولی است .فیلد جدولاشون Id,Title هست .
--------------------
در رابطه با این کد


and UserId in (1010,1011,1012,1015,1090,2020,1214,2321,6546,8440 ,3102,3609,3154,2148)

آیا دستور دیگری وجود دارد که باعت کاهش سرعت نشه ؟
اپراتور ممکن هست تا 300 کد کاربری را انتخاب کنه و حتی 1 نفر .

[tooraj_azizi_1035]

ID های انتخابی کاربر رو در یک متغیر TableValued بریزید و به SP پاس بدید. کوئری به این شکل در میاد:


select
,Fn_GetShiftName (ShiftId) ShiftName
,Fn_GetAreaName (AreaId) AreaName
,Fn_GetUserName(UserId) UserName
,Sum(Debit) SDebit
,Sum(Tax)STax
,Sum(Deposite)SDeposite
from Transaction
join @TBV_Users t on t.UserId= Transaction.UserId
where cityID=1
and FromDateTime between cast('2014-01-01 08:00' as DateTime) and cast('2014-02-01 23:59'as DateTime)
and AreaId in (100,101,102,103,202,205,300,350,401,402)
and ShiftId=1


برای AreaId هم می تونید این کار رو بکنید. Performance رو قبل و بعد از اینکار اندازه بگیرید.

[reza_ali202000]

تقریبا برا تمام کوئری هاتون همین کارا رو انجام بدین و بعد خودش پیشنهاداتی رو برای ایندکس سازی میده اونا رو توی یه کوئری کپی کنین و اجرا کنین. خواهید دید که سرعتتون به شدت بالا میره! خودم به شخصه کوری که 1 دقیقه طول میکشید اجراش با پیشنهادات خود اس کی ال به 2 ثانیه رسید!

[M * M * A]

ID های انتخابی کاربر رو در یک متغیر TableValued بریزید و به SP پاس بدید. کوئری به این شکل در میاد:


select
,Fn_GetShiftName (ShiftId) ShiftName
,Fn_GetAreaName (AreaId) AreaName
,Fn_GetUserName(UserId) UserName
,Sum(Debit) SDebit
,Sum(Tax)STax
,Sum(Deposite)SDeposite
from Transaction
join @TBV_Users t on t.UserId= Transaction.UserId
where cityID=1
and FromDateTime between cast('2014-01-01 08:00' as DateTime) and cast('2014-02-01 23:59'as DateTime)
and AreaId in (100,101,102,103,202,205,300,350,401,402)
and ShiftId=1


برای AreaId هم می تونید این کار رو بکنید. Performance رو قبل و بعد از اینکار اندازه بگیرید.

سلام
من اگه با TableValued این کارو بکنم ، فکرکنم دچار مشکل بشم.چون چندین اپراتور وجود داره که درحال گزارش گیری هستن .چون ممکن هست هر اپراتور گزارش ازکد های خاصی بگیره .

[tst835]

سلام.
پارتیشن بندی رو براساس حجم داده ها انجام بدید. ممکنه حجم داده های ماهیانه شما خیلی زیاد باشه در اینصورت به جای پارتیشن بندی سالیانه بهتره از پارتیشن بندی ماهیانه استفاده کنید که حجم رکوردهای هر پارت کمتر و در نتیجه افزایش سرعت بیشتر بشه.
کلاستر ایندکس رو بر روی مهمترین فیلد در گزارشات بذارید. برای سایر شروط از Non Cluster Index استفاده کنید.
روی فیلدهای برگشتی از select یک Cover Index قرار بدید (این کار مانع از ایجاد LookUp و افزایش سرعت کوئری ها میشه).
مبحثی داریم به نام Data Compression که جهت فشرده سازی و کاهش حجم داده ها استفاده میشه که در نتیجه حجم داده ها کمتر و باعث افزایش سرعت کوئری ها میشه.
در sql 2012 مبحث Column Store Index مطرح شد که به شدت باعث افزایش سرعت کوئری ها میشه (البته تا جایی که بنده اطلاع دارم محدودیت هایی هم داره که باید برای گزارشاتتون بررسی کنید).
نحوه صحیح و بهینه کوئری نویسی
استفاده از stored procedure به جای نوشتن مستقیم کوئری درون Application (بیشتر در ADO.Net) یا استفاده از Dynamic SQL (بصورت پیش فرض دوستانی که سی شارپ کار میکنند و از Entity Framework استفاده میکنند این قابلیت رو دارند).
و البته سخت افزار مناسب مخصوصا رم و cpu رو هم نباید در افزایش سرعت فراموش کرد (شاید نصف سرعت رو بشه به سخت افزار منوط کرد هر چند خیلی ها میگن 30 تا 40 درصد سرعت به سخت افزار بستگی داره).
در کل اگر طراحی دیتابیس رو صحیح انجام بدید و مواردی رو که دوستان اعلام کردن و این چند موردی رو که بنده عرض کردم دربارشون تحقیق و در نهایت اعمال کنید شک نکنید که به وضوح افزایش سرعت رو لمس می کنید.
موفق باشید.

[tooraj_azizi_1035]

من اگه با TableValued این کارو بکنم ، فکرکنم دچار مشکل بشم.چون چندین اپراتور وجود داره که درحال گزارش گیری هستن .چون ممکن هست هر اپراتور گزارش ازکد های خاصی بگیره .

مقادیر این متغیر به ازاء هر کاربر فرق می کنه. شما در برنامه یک DataTable دارید که ID ها رو در اون پر می کنید و به این SP پاس میدید. هر اپراتور یک Session یونیک از دید SQL Server به حساب میاد.