کار با جداول خیلی بزرگ

[Mohammadi_F]

سلام
من در حال نوشتن برنامه ای هستم که یک از جداول اون حدود سه میلیون رکورد داره .
سوالی که از دوستان داشتم اینه که بهترین روش برای کار با جداولی با این حجم چیه و از چه روشهایی استفاده کنم تا بهترین عملکرد رو داشته باشم. از یکی از دوستان شنیدم که SQL امکانی رو داره که با اون میشه یک جدول رو به چند پارتیشن مجزا تقسیم کرد و به عنوان یک جدول استفاده کرد. آیا چنین چیزی وجود داره ؟
ضمنا جدول من 9 فیلد داره.
متشکرم

[AminSobati]

Partitioning و Indexing! امیدوارم از SQL Server 2005 استفاده کنید تا در هر دو مورد مزایای زیادی بدست بیارین

[Mohammadi_F]

ممنون از جوابتون ولی خیلی مختصر بود.
ممکنه کمی بیشتر در این مورد بخصوص در مورد Partitioning توضیح بدید چون من تازه از اکسس به SQL اومدم و اطلاعات چندانی ندارم.
ممنون از توجهتون.

[AminSobati]

Partitioning این امکان رو به شما میده که رکوردها رو بر اساس یک معیار، دسته بندی کنید و روی فایلهای مجزا قرار بدین. زمانی که این فایلهای مجزا واقعا روی دیسکهای مجزا هم قرار داشته باشند، Requestها به تناسب اینکه با کدوم دسته از رکوردها کار دارند، با دیسکهای مختلف درگیر خواهند شد و همه فشار روی یک دیسک قرار نمیگیره. بعنوان مثال اگر جدول Orders رو بر اساس فیلد OrderDate به 4 قسمت (4 فصل یک سال) تقسیم کنید، میتونین اطلاعات هر فصل رو روی یک دیسک قرار بدین (در حالیکه کاربر یک جدول رو میبینه) و باعث میشه Queryهایی که با Range یا فصل خاصی سر و کار دارند، با دیسک مربوطه کار کنند.

[Behrouz_Rad]

جناب ثباتی!
زمان دستیابی در این حالت با توجه به وجود دیسک های مختلف چقدر هزینه بر هست؟
در صورتی که نیاز باشه یکی از Query ها جهت استخراج اطلاعات خودش به یک دیسک دیگه دسترسی پیدا کنه، آیا باز هم Partitioning رو پیشنهاد می کنید؟

[DonetKarvb]

جناب ثباتی!
زمان دستیابی در این حالت با توجه به وجود دیسک های مختلف چقدر هزینه بر هست؟
در صورتی که نیاز باشه یکی از Query ها جهت استخراج اطلاعات خودش به یک دیسک دیگه دسترسی پیدا کنه، آیا باز هم Partitioning رو پیشنهاد می کنید؟
اگر شما به ازای هر هارد دیسک یک CPu هم داشته باشید آنگاه شما قادر خواهید بود به صورت موازی اطلاعات را از روی دیسک های مختلف بخوانید و سرعت بیشتر از وجود یک هارد دیسک خواهد بود.

[Behrouz_Rad]

اگر شما به ازای هر هارد دیسک یک CPu هم داشته باشید آنگاه شما قادر خواهید بود به صورت موازی اطلاعات را از روی دیسک های مختلف بخوانید و سرعت بیشتر از وجود یک هارد دیسک خواهد بود.
این که یک امر بدیهیه!!!

[hmm]

اصلاً هم امری بدیهی نیست!
یعنی چی که به ازای هر هارد یک cpu داشته باشم؟
partition فقط از لحاظ فیزیکی داده ها رو به چند محل تقسم میکنه ، از لحاظ پردازشی کار همون پردازنده ایست که Sqlserver رو Run میکنه
و به لحاظ فنی نمیشه یه سروری که مثلا دوتا Cpu و دوتا هارد داره رو جوری تنظیم که هر کدوم از Cpu ها فقط به requestهای یک هارد جواب بده
در ضمن cpu نمیتونه اطلاعات موجود در هارد رو پردازش کنه
شاید هم امکان جدیدی است و من بیخبرم!

[Behrouz_Rad]

اصلاً هم امری بدیهی نیست!
یعنی چی که به ازای هر هارد یک cpu داشته باشم؟
partition فقط از لحاظ فیزیکی داده ها رو به چند محل تقسم میکنه ، از لحاظ پردازشی کار همون پردازنده ایست که Sqlserver رو Run میکنه
و به لحاظ فنی نمیشه یه سروری که مثلا دوتا Cpu و دوتا هارد داره رو جوری تنظیم که هر کدوم از Cpu ها فقط به requestهای یک هارد جواب بده
در ضمن cpu نمیتونه اطلاعات موجود در هارد رو پردازش کنه
شاید هم امکان جدیدی است و من بیخبرم!
دوست من!
آرامش خودت رو حفظ کن و با خوندن دوباره مطالب و اندکی صبر، لحظات شیرینی رو خلق کن!

متشکرم.:لبخندساده:

[hmm]

سلام بهروز خان
گفتن یه جمله انتقادی نیاز به عصبانی شدن نداره
به فرمایش شما کل مطلب رو یه بار دیگه هم خوندم و راهنمایی که میتونم انجام بدم همونیه که تو پست بالاست
ببین هدف از partition کردن اطلاعات نوعی دسته بندی فیزیکیه که کمک میکنه query ها سریعتر به محل فیریکی دیتا دست پیدا کنن یعنی DBA با این کار عملا به هوش مصنوعی دیتابیس کمک میکنه .
دومین اینکه Cpuها هنوز اینقدر هوشمند نشده اند که مستقیما محتویات هارد رو پردازش کنن پس "اگر شما به ازای هر هارد دیسک یک CPu هم داشته باشید آنگاه شما قادر خواهید بود به صورت موازی اطلاعات را از روی دیسک های مختلف بخوانید و سرعت بیشتر از وجود یک هارد دیسک خواهد بود." منتفیه حتی اگه برای هر هارد بشه 3 تا Cpu گذاشت (هنوز مطمئن نیستم اینکار یعنی چه و از لحاظ فنی میشه یا نه)
سوما اینجا محیطیه که همه دانششون رو در اختیار دیگران میگذارن احتیاجی به ناآرامی هم نداره ولی اگه هرکسی بخواهد همینطوری تصوراتش رو بدون پایه علمی بنویسه و مدیری هم همینطوری تایید کنه بدون اینکه روی اون مطلب فکری کرده باشه یا مستندی جهش ارائه داده باشه زیاد مطلوب کسانی که تازه میخوان شروع کنن نیست و ممکنه ... بماند.
بهرحال من هنوز هم عصبانی نیستم و اینجا وقتی لحظات خوشی برام فراهم میشه که چیزی یاد بگیرم که واقعا مستند و علمی باشه نه تصورات و اوهام .
و از دوستمون هم که این جمله رو گفته میخوام مستنداتش رو ارائه کنه . تا شبهات برطرف بشه و یه لحظه خوشی برام فراهم شه.

[Behrouz_Rad]

بنده نه چیزی رو تایید کردم و نه رد! همچنان که در مورد صحبت های تو نظری نمیدم.
شاید لازم می بود که بیشتر توضیح میدادم.
منظور اینه که: اگر فرض دوستمون صحیح باشه، ایشون به یک امر بدیهی اشاره کرده!

متشکرم.

[odiseh]

Partitioning این امکان رو به شما میده که رکوردها رو بر اساس یک معیار، دسته بندی کنید و روی فایلهای مجزا قرار بدین. زمانی که این فایلهای مجزا واقعا روی دیسکهای مجزا هم قرار داشته باشند، Requestها به تناسب اینکه با کدوم دسته از رکوردها کار دارند، با دیسکهای مختلف درگیر خواهند شد و همه فشار روی یک دیسک قرار نمیگیره. بعنوان مثال اگر جدول Orders رو بر اساس فیلد OrderDate به 4 قسمت (4 فصل یک سال) تقسیم کنید، میتونین اطلاعات هر فصل رو روی یک دیسک قرار بدین (در حالیکه کاربر یک جدول رو میبینه) و باعث میشه Queryهایی که با Range یا فصل خاصی سر و کار دارند، با دیسک مربوطه کار کنند.

سلام آقای ثباتی
ممکنه بگین که آیا SQL2000 هم امکان Partitioning رو داره یا نه و اگر داره کجا باید بریم سراغش؟

[AminSobati]

ظاهرا بحث خیلی داغ شده! ببینید دوستان:
زمانی که شما مثلا بر اساس تاریخ، اطلاعات رو دسته بندی یا همون Partitioning میکنین، Query Processor بر اساس شرطی که در Query دارید، این رو درک میکنه که الان کدوم Partition باید با این Query درگیر بشه. مثالی در خصوص دسته بندی سفارشها در ابتدا مطرح کردم، اگر من Query به این شکل بزنم:


SELECT * FROM Orders
WHERE OrderDate BETWEEN '2006-1-15' AND '2006-2-15'

از این شرط Query Processor متوجه میشه شما رکوردهایی از ربع اول سال رو نیاز دارید، لذا سایر دیسکهایی که ربعهای دیگه سال رو دربر دارند، اصلا با این Query درگیر نمیشن. اما اگر من به این شکل Query بگیرم:


SELECT * FROM Orders
WHERE OrderDate BETWEEN '2006-1-15' AND '2006-5-15'

اینجا مشخص میشه که Range مورد نظر، با دو پارتیشن کار داره. چون هر پارتیشن روی یک Spindle (دیسک) جدا قرار داره و حداقل دو CPU داریم (فرضا)، دو دیسک بصورت موازی شروع به آماده سازی اطلاعات میکنن. انگار که دو Query از دو پارتیشن انجام میشه و در نهایت Union میشن. اما با این تفاوت که این دو عمل کاملا همزمان صورت میگیرند. اساسا اینکه Query با کدوم پارتیشن ها درگیر میشه جزو مراحلی هست که Query Processor طی میکنه (برای هر Query) و حتی این مرحله در Execution Plan با آیکن Constant Scan قابل رویت هست. اصطلاحا Partition Elimination گفته میشه بهش، یعنی: کنار گذاشتن پارتیشن هایی که نیاز نیست!
حالا فرض کنیم به این شکل Query انجام بدیم:


SELECT * FROM Orders

این یعنی اطلاعات همه فصلها رو نیاز داریم، پس 4 دیسک همزمان و موازی نتیجه Query رو فراهم میکنند.
اینکه عرض کردم داشتن بیش از یک CPU برای موازی شدن کار ضروریه به این علت هست که خود SQL Server تعیین میکنه که کدوم CPU در هر لحظه کدوم پارتیشن رو پردازش کنه.
در پاسخ اینکه آیا CPU اطلاعات روی دیسک رو پردازش میکنه یا خیر، باید بگم دقیقا همینطوره. مثلا زمانی که Join انجام میدین، Pageهای اطلاعات Load میشن داخل حافظه و CPU اطلاعات رو پردازش و Join میکنه. اساسا SQL Server چیزی جز اونچه که روی دیسک هست نداره، پس CPU همیشه داره اطلاعات دیسک رو پردازش میکنه.
حالت پیشرفته تری در Partitioning هست که اصطلاحا Collocation نامیده میشه و به این معنی هست که ما جداول رو بر مبنای یک معیار مشترک، پارتیشن کنیم، ولی اون جدال باید قابل Join باشند تا Collocation تحقق پیدا کنه. مثلا جدول OrderDetails رو میتونیم دقیقا مشابه خود Orders پارتیشن کنیم. حالا موقع Join باید فیلدی که ملاک پارتیشن کردن بوده هم در شرط Join شرکت کنه:


SELECT o.OrderDate, od.UnitPrice FROM Orders o
JOIN [Order Details] od
ON o.OrderID=od.OrderID AND o.OrderDate=od.OrderDate
WHERE OrderDate BETWEEN '2006-1-15' AND '2006-5-15'

در این حالت چون دو جدول Orders و OrderDetails بر اساس فیلد OrderDate پارتیشن شدن و با هم Join هم میشن میگیم: این دو جدول Collocate هستند.
در این حالت ما سفارشها و جزئیات هر فاکتور (Order) رو برای هر فصل روی یک دیسک قرار دادیم.
مطلب آخر اینکه: Partitioning در 2000 به این شکل وجود نداره، متفاوته

[SeniorDevs]

دوست عزیز، من پیشنهاد نمی کنم که شما از SQL SERVER 2005 استفاده کنید چون از سال 2000 تا کنون 4 تا ServicePack برای SQL SERVER 2000 عرضه شده که ایرادات بحرانی آنرا از بین برده است اما هنوز معلوم نیست که ویژگی جدید و بسط یافته Partitioning تا چه اندازه دارای اطمینان باشد بنابراین من جواب شما را در SQL SERVER 2000 میدهم.

شما فکر می کنید که مثلا 3 میلیون رکورد، در پایگاه های داده خیلی زیاد است جواب منفی است بلکه بسیار ناچیز است. یک مثال برای شما میزنم شرکت EBAY که دارای شرکتی به نام PayPal است حداقل 90 میلیون کاربر دارد حال شما تصور کنید که شخصی بخواهد عمل Log-in را انجام دهد شاید فکر می کنید حتما چند دقیقه طول می کشد تا جواب بدهد اما اینطور نیست فقط 7 ثانیه.

هر پایگاه داده ای مانند SQL SERVER 2000، Oracle، Btrieve، Paradox و غیره دارای سیستم Catching بسیار پیشرفته ای برای انجام QUERY ها هستند به این صورت که مثلا 20MB به 20MB از فایل اصلی Database گرفته می شود و با استفاده از الگوریتم BINARY TREE در آن QUERY انجام می شود و نتایج در Buffer ی ذخیره می شود و سپس 20MB بعدی و الی آخر. و در نهایت نتایج با هم تلفیق می شوند و به صورت یک Table در اختیار شما قرار می گیرد.

بنابراین هرچه Database حجیم تر شود عمل QUERY باید کند تر گردد. اما از انجایی که SQL SERVER 2000 از تکنولوژی Multi-Threading استفاده می کند و حداکثر 255 Thread به CPU متصل می کند بنابراین می تواند حداکثر 255 QUERY یا 255 کار را در هر 255 میلی ثانیه بصورت موازی انجام دهد بنابراین در حین انجام یک QUERY روی 3 میلیون Record دیتابیس قفل نمی شود و کاربران دیگر می توانند براحتی اطلاعات خود را در حین اجرای QUERY شما بگیرند.

من خودم قبلا یک تست روی 30 میلیون رکورد انجام دادم که فقط 5 ثانیه طول کشید بنابراین شما نباید نگران کاهش سرعت باشید چون شما تنها در جهان با مشکل حجیم بودن دیتابیس مواجه نیستید و شرکت هایی که دیتابیس ها را می سازند خود این مسئله را می دانند و انرا حل کرده اند.

[AminSobati]

دوست عزیزم،
هیچ نرم افزاری بدون باگ به دست مشتری نمیرسه، چه برسه به SQL Server که بالای 400 نفر در مایکروسافت فقط روی این محصول زحمت کشیده اند.
- ارائه سرویس پک زیاد، نشانه عیب کار نیست، نشانه تیم منسجمی هست که بلافاصله Feedbackها رو جمع آوری و عیبها رو برطرف
کرده. 4 سرویس پک در 4 سال زیاد نیست. ولی بد نیست بدونین زمانی که حتی نسخه نهایی SQL Server 2005 بدست من و شما نرسیده بود (چه برسه به سرویس پک 1) شرکت Dell از همون استفاده میکرد و از لحاظ Performance در لیست 10 سرور برتر جهان بود (مرجع www.tpc.org).
- اگر 90 میلیون کاربر همه باهم لاگین کنند، سیستم Down میشه، ولی چرا نشد؟ چون هیچ وقت 90 میلیون نفر با هم لاگین نکردند!
- در SQL Server و Oracle هیچ وقت نتیجه Query در حافظه Cache نمیشه، این یک اشتباه رایجه، بلکه فقط Pageهایی از اطلاعات که مورد نیازه در حافظه Cache میشه و محاسبات واقعا روی Pageها انجام میشه. مثلا اگر شما دو جدول رو 1000 بار با هم Join کنین، واقعا 1000 با Query اجرا میشه، اما فقط دفعه اول اطلاعات از دیسک خوانده میشه، و 999 بار دیگه از Pageهایی که Cache شدن خوانده میشه و Join اتفاق میافته.
- عدد 255 به این معنیه که تا 255 عدد Connection وقتی برقرار میشه، SQL Server باید یک Thread به هر Connection اختصاص بده، و برای Connectionهای بیش از اون، از Threadهایی که سرشون خلوته استفاده میکنه، یعنی به نوعی Share میکنه Threadها رو. پس بیش از 255 عدد Connection به یک CPU میتونیم داشته باشیم. اما اینطور نیست که این کارها موازی انجام بشه، بلکه Time Slicing اتفاق میافته و CPU وانمود میکنه که کار داره پارالل انجام میشه (شاید هم ما به نظرمون میاد!)