راهنمایی جهت بهینه سازی

[ealireza]

یک فایل SVC مربوط به سایت Ip2Location دارم Db6,DB7,Db10 .
حجم فایل SVC حدود 500 مگابایت و حاوی 5341652 رکورد هست.

این فایل رو با توجه با توجه به ساختار داده شده در سایت به SQL منتقل کردم


CREATE TABLE [dbo].[IPCITYLATLONGISP] (
[ipFROM] [float] NOT NULL ,
[ipTO] [float] NOT NULL ,
[countrySHORT] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[countryLONG] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ipREGION] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ipCITY] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ipLATITUDE] [float] ,
[ipLONGITUDE] [float] ,
[ipISP] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

GO

یک کوئری از دیتابیس گرفتم

select * from IPCITYLATLONGISP where ([ipFROM] <= 1075383810) AND ([ipTO] >= 1075383810)
حدود 30 ثانیه طول کشید

من دو نوع Index بر روی ipFROM و ipTO تعریف کردم و تست کردم
هم Clustered و هم None clustered تغییر محسوسی نکرد 30 ثانیه به 15 کاهش یافت

راه حل چیست
چه راهی رو پیشنهاد میکنید
خود سرور ip2location پاسخ کوئری ها رو تو کمتر از 1 ثانیه میده. (وب سرویس هست)

پیشنهاد شما چیست

[Microsoft.net]

1 - بجای float از bigint و بجای nvarchar از varchar استفاده کن چون index رو اعداد صحیح خیلی کاراتر از اعشاریه و nvarchar دقیقا دوبرابر Varchar فضا اشغال میکنه
2 - یه index ترکیبی رو ipfrom و ipto بزار اگه حاصیت کلید دارن یه کلید ترکیبی cluster بزار
3- بجای select * from فقط فیلدهایی که نیازه رو بیار
دوباره کویری بگیر و زمانشو اینجا بنویس

[AminSobati]

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

این ایندکس میتونه بیشترین کمک رو به Query شما بکنه (با فرض اینکه اصل جدول میخوایم دست نخورده باقی بمونه)


create index ix1 on IPCITYLATLONGISP (
ipFROM,
ipTO,
countrySHORT,
countryLONG,
ipREGION,
ipCITY,
ipLATITUDE,
ipLONGITUDE,
ipISP
)


این ایندکس تقریبا یک کپی کامل از جدول شما میشه، پس ساخته شدنش زمان میبره

[RezaKia]

سلام،
من هم از این SVC استفاده میکنم ولی:
1. فقط کشور کاربر برام مهمه نه شهر و یا مشخصات ISP
2. جدولو به 2 جدول زیر تقسیم کردم:
الف. جدولی شامل یک Identity،IpFrom ،IpTo ،CountryId
ب. جدول دوم شامل یک فیلد Identity به نام CountryId و تمام مشخصات مورد نیاز دیگر مثل نام کامل و مختصر کشور

بعد CountryId از جدول اول و دوم رو به هم مرتبط کردم. این کار رو حتماً بکن چون سرعت رو زیاد میکنه.

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

دقت کن اینکار رو تو دو دستور جدا انجام بده و اصلاً از Join استفاده نکن.

مثل کد زیر:



Create Function GetCountryByIpAddress
(
@IpFrom bigint,
@IpTo bigint
)
Returns @tbl_tmp_Country Table
(
CountryId int,
CountyFullName varchar(50),
CountryAbb varchar(4)
)
As
Begin
Declare @intCountryId int
Select @intCountryId = CountryId
From tbl_IpToCountry
Where ((IpFrom <= @IpFrom) And (IpTo >= @IpTo))
If (@intCountryId Is Null)
Begin
Set @intCountryId = 0
End
Else
Begin
Insert Into @tbl_tmp_Country(CountryId, CountyFullName, CountryAbb)
Select CountryId, CountyFullName, CountryAbb
From tbl_IpCountries
Where (CountryId = @intCountryId)
End
Return
End
Go



دیگه من بعد فقط با فراخوانی این تابع مشخصات مورد نظر برمیگرده.

موفق باشید.

[ealireza]

ویرایش شد

[AminSobati]

سلام،
من هم از این SVC استفاده میکنم ولی:
1. فقط کشور کاربر برام مهمه نه شهر و یا مشخصات ISP
2. جدولو به 2 جدول زیر تقسیم کردم:
الف. جدولی شامل یک Identity،IpFrom ،IpTo ،CountryId
ب. جدول دوم شامل یک فیلد Identity به نام CountryId و تمام مشخصات مورد نیاز دیگر مثل نام کامل و مختصر کشور

بعد CountryId از جدول اول و دوم رو به هم مرتبط کردم. این کار رو حتماً بکن چون سرعت رو زیاد میکنه.

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

دقت کن اینکار رو تو دو دستور جدا انجام بده و اصلاً از Join استفاده نکن.

مثل کد زیر...

رضا جان نیازی به تفکیک جدول نیست. با این کار شما یک جستجوی اضافی، و مقادیر قابل توجهی کد نویسی به پروژه خودت اضافه میکنی!
زمانی که ایندکس روی فیلدهای مورد نظر میسازین، درست مثل اینه که یک جدول با اون فیلدها دارین بوجود میارین. یعنی برای شما مزیته داشتن یک جدول کم حجم رو به همراه داره اما در عوض Query Optimizer خودش به طور هوشمند در موقع مناسب از اون استفاده میکنه و شما Search یا Join اضافی هم تحمیل نمیکنید. همچنین استفاده از Table Valued Function و به میان کشیدن بحث Insert هم طبعا Overhead خودش رو داره.

[RezaKia]

رضا جان نیازی به تفکیک جدول نیست.


سلام،
چرا هست. شدیداً هم نیاز به این کار هست. :متعجب:

این بار برای اولین باره دارم یک جواب SQL میدم و تمام چیزی که نوشتم مال خود خود Microsoft بوده بدون هیچ تغییری. این یک تابع استاندارد و البته پولی است که همراه با SQL 2000 توسط Microsoft به بعضی از شرکتها داده شد.

من هم چون تو جای قبلی که کار میکردم وظیفه نوشتن بعضی کدهای نمایندگی Orange Country از شرکت Future Technology نماینده رسمی Microsoft در بخش طراحی رابط کاربر و لایه Data Access در زمینه نرم افزارهای تحت وب سفارشی برای مشتریان رده دوم تا پنجم Microsoft رو داشتیم. این کد رو در اختیار دارم. :قلب:

البته کد اصلی دارای توابع بسیار زیادی هست که داده های دیگری رو هم از جمله نوع ISP، Browser، و ... را هم بدست می آورد که من اونا رو ور داشتم.

اما در مورد شاخص،
بله کارش دقیقاً همونه که گفتید. ولی دیگه شما خوب میدونید اگر طول شاخص از 900 بایت بیشتر بشه؛ باید جدول رو چند تکه کرد.

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

چراش هم معلومه، این تابع با در نظر گرفتن عنصر اول از نتیحه تابع برگشت از Optimizer سطح اول شاخص داده ها رو پیدا میکنه در حالی که دستور Select رو جدول تفکیک نشده باید بعد از برگشت کامل از Catalog File خروجی سطح دوم IO داده ها رو بدست بیاره.

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

اگه جایی رو اشتباه کردم، بگید درستش چیه تا منم اشتباهمو تصحیح کنم.


نکته: داشت یادم میرفت؛ هر دو تا جدول باید حداقل این شاخص ها رو داشته باشند:
جدول اصلی رو ستونهای داده ای CountryId و Identity
جدول دوم روی ستون CountryId
در ضمن اگر Relation بین دو تا جدول رو برداری در حالت همزمانی بیش از 100 اجرا تمام بهینه سازی ها ندید گرفته میشه :افسرده:



موفق باشید.

[ealireza]

در بخش طراحی رابط کاربر و لایه Data Access در زمینه نرم افزارهای تحت وب سفارشی برای مشتریان رده دوم تا پنجم Microsoft رو داشتیم. این کد رو در اختیار دارم.
والا این کد شما چیزه خاصی نداره .. فقط OverHead درست میکنه .


ز جمله نوع ISP، Browser، و ... را هم بدست
دوست عزیز Browser ربطی به Ip2Location نداره !!!


در آخر، تو کدی که نمایندگی به ما داد ذکر شده بود برای استفاده از این تابع حتما از دستور مستقیم استفاده کنیم
این نشون میده در چه سطحی SQL بلد بودن


در هر صورت با یک سری ایندکس که خود شرکت برام ایمیل کرد زمان کوئری به 0.05 ثانیه رسید

با تشکر از دوستان

[AminSobati]

من هم چون تو جای قبلی که کار میکردم وظیفه نوشتن بعضی کدهای نمایندگی Orange Country از شرکت Future Technology نماینده رسمی Microsoft در بخش طراحی رابط کاربر و لایه Data Access در زمینه نرم افزارهای تحت وب سفارشی برای مشتریان رده دوم تا پنجم Microsoft رو داشتیم. این کد رو در اختیار دارم. :قلب:

تفکیک جدول گاها به خاطر انطباق اون با مسائل طراحی Object Oriented صورت میگیره، نه Performance. به احتمال 99 درصد کدی که در اختیار شماست به همین منظور جدول رو Split کرده. این کاملا واضح و بدیهیه که وقتی جدول دو قسمت شد، یک Join به Query ما اضافه میشه. شما در کدی که نوشتین، در ظاهر Join رو حذف کردین در حالیکه منطق شیوه شما همون Join هستش و عملا الگوریتم Join رو استفاده کردین.



اما در مورد شاخص،
بله کارش دقیقاً همونه که گفتید. ولی دیگه شما خوب میدونید اگر طول شاخص از 900 بایت بیشتر بشه؛ باید جدول رو چند تکه کرد.

نه! حتی در این مورد هم نیاز نیست جدول Split بشه. چون میتونین Indexed View بسازین تا بدون دردسر زیاد کردن جدول، Query Optimizer خودش در صورت لزوم از اون Indexed View استفاده کنه. حسنش اینه که شما هنوز با یک جدول طرف هستین! همچنین مزیت بهتر شدن Performance رو هم در اختیار دارین.





در هر صورت با یک سری ایندکس که خود شرکت برام ایمیل کرد زمان کوئری به 0.05 ثانیه رسید


علیرضا جان برای Query که شما پست کردین، ایندکس من Perfect ترین حالت ممکن بود. ممکنه ایندکسی که خود شرکت پیشنهاد کرد رو بفرستین؟