طولانی شدن اجرا select

[razaghi]

من جدولی شامل سه فیلد NameID1,NameID2, Score دارم. تمام فیلدها از نوع int می باشد و ترکیب NameID1,NameID2 کلید می باشد. جدول من شامل 60میلیون رکورد می باشد. من می خواهم تمام Score که NameID1=1 or NameID2=1 را Select کنم. به صورت زیر :
select * from tblName where NameID1=1 or NameID2=1
به خاطر زیاد بودن رکوردها اجرای این کوئری طولانی می شود. در برنامه C#،Timeout می دهد. ممنون میشم اگر راهنمایی کنید.

[reza_rad]

روی فیلدهای NameID1 و NameID2 ایندکس بگذارید.
Timeout کامند تون رو در C# ببرید بالا.

[razaghi]

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

[reza_rad]

Create INDEX (http://msdn2.microsoft.com/en-us/library/ms188783.aspx)

[razaghi]

با تشکر
ایندکس گذاشتم ولی اثری ندارد.

[reza_rad]

ایندکس گذاشتم ولی اثری ندارد.
Connection Timeout رو هم بالا بردید؟

[AminSobati]

دوست عزیزم،
1) در کدوم نسخه از SQL Server کار میکنید؟
2) دستور CREATE INDEX که انجام دادین رو پست بفرمایید

[ali_kolahdoozan]

select * رو هم بردار و نام فیلدها رو بنویس . گاهی * مانع از اثر گزاری index میشود . اگر ایندکس رو درست گذاشته باشی

[Elham_gh]

select * رو هم بردار و نام فیلدها رو بنویس . گاهی * مانع از اثر گزاری index میشود . اگر ایندکس رو درست گذاشته باشی

من بعید می دونم. و تا حالا مطلبی در این مورد نخوندم. منبع شما برای این حرف کجاست.
البته این درست است که می گن سعی کنید از* در Select استفغاده نکنید اما دلیلش استفاده نشدن از ایندکس نیست

[fkohantorabi]

من بعید می دونم. و تا حالا مطلبی در این مورد نخوندم. منبع شما برای این حرف کجاست.
البته این درست است که می گن سعی کنید از* در Select استفغاده نکنید اما دلیلش استفاده نشدن از ایندکس نیست

query execution plan رو نگاه کن و میبینی که از کدوم ایندکس استفاده می شود. اگه یکی از این دو هم clustered index بکنی بد نیست. اگه اشتباه نکنم کلید ارلیه بطور خودکار cluster index می شود. در ضمن در دستور select مربوطه with (nolock) رو اضافه کن و سعی کن از read uncommited isolation level استفاده کنی.


فرزاد-

[AminSobati]

من بعید می دونم. و تا حالا مطلبی در این مورد نخوندم. منبع شما برای این حرف کجاست.
البته این درست است که می گن سعی کنید از* در Select استفغاده نکنید اما دلیلش استفاده نشدن از ایندکس نیست
استفاده از * میتونه باعث بشه Query Optimizer از استفاده ی یک ایندکس صرف نظر کنه. دلیل این هست که وقتی Query تمام فیلدها رو درخواست کرده، احتمال وجود تمام فیلدها در ایندکس کمه لذا در صورتی که حتی روی WHERE Clause ایندکس هم داشته باشیم، بعد از پیدا شدن رکوردهای مورد نظر در ایندکس، باید Bookmark Lookup انجام بشه تا بقیه فیلدها هم بدست بیاد (Bookmark Lookup یعنی حرکت کردن Head هارددیسک از ایندکس به اصل Table برای استخراج بقیه فیلدها، و چون یک عمل مکانیکی هست، به شدت باعث کاهش Performance میشه). در صورتیکه رکوردهای یافت شده در ایندکس تعدادش کم باشه، Bookmark Lookup میتونه مقرون به صرفه باشه.
SQL Server به کمک Statistics درصد فراوانی یک Value در یک فیلد رو میدونه. مثلا مقادیر در فیلد جنسیت (Gender) تنوع زیادی نداره. وقتی Query شما WHERE Gender=Male رو درخواست میکنه، Optimizer میدونه که تقریبا 50 درصد کل رکوردها بدست میاد. لذا حتی با وجود ایندکس روی Gender (در صورت استفاده از * در SELECT) ممکنه Optimizer تصمیم بگیره اصل جدول رو Scan کنه به جای اینکه از ایندکس مرتبا Bookmark Lookup بزنه. اما اگر WHERE OrderDate=2008/3/26 در Query داریم، و Statistics به Optimizer بگه تقریبا 20 رکورد با این شرط وجود داره، طبعا داشتن ایندکس روی OrderDate کمک خواهد کرد. چون هزینه 20 بار Bookmark Lookup زدن به مراتب کمتر از Scan کردن جدول سفارشات با 10 هزار سفارش هست.
وقتی ما در اصل 5 فیلد رو لازم داریم و از * استفاده نمیکنیم، پس میتونیم ایندکسی بسازیم که هر 5 فیلد رو Cover کرده باشه.
حالا برای مثال این دو Query رو در نظر بگیریم (با کلید ctrl+L نتیجه رو ببینید):



USE Northwind
GO

SELECT * FROM Orders
WHERE CustomerID='ANATR'

SELECT * FROM Orders
WHERE CustomerID='SAVEA'


Query اول از ایندکس استفاده میکنه و Lookup میزنه چون تعداد رکوردها کمه اما Query دوم ایندکس رو کنار میگذاره و Scan انجام میده.
حالا Query دوم اگر تعداد فیلد کمی بخواد، میتونیم براش ایندکس بسازیم:



CREATE INDEX ix1 ON Orders(CustomerID) INCLUDE (OrderDate, ShipCountry, ShipCity)
GO
SELECT OrderDate, ShipCountry, ShipCity FROM Orders
WHERE CustomerID='SAVEA'


اگر در Query مجددا * قرار بدیم، هیچ ایندکسی استفاده نمیشه و Scan انجام خواهد شد.
اما مشکل اینجاست: همیشه نمیتونیم از * صرف نظر کنیم. گاهی واقعا تمام فیلدها مد نظر هستند و قرار دادن همه اونها در ایندکس میتونه باعث افزایش قابل توجه حجم دیتابیس بشه. در این حالتها باید از راهکارهای دیگه ای مثل Partitioning (در صورت امکان) استفاده کرد

[AminSobati]

query execution plan رو نگاه کن و میبینی که از کدوم ایندکس استفاده می شود. اگه یکی از این دو هم clustered index بکنی بد نیست. اگه اشتباه نکنم کلید ارلیه بطور خودکار cluster index می شود. در ضمن در دستور select مربوطه with (nolock) رو اضافه کن و سعی کن از read uncommited isolation level استفاده کنی.
فرزاد-

ملاک Clustered کردن یک ایندکس این نیست. Clustered شدن ایندکس تاثیرات بعدی داره که باید ازشون آگاه بود و به جا این تصمیم رو گرفت. استفاده از nolock یا read uncommited تنها زمانی کمک میکنه که شما مشکل همزمانی کاربرها از نظر Lock شدن دارین. با این کار فقط معطل ویرایش سایر کاربران نخواهید شد، و نه بیشتر! گو اینکه وقتی شما nolock یا read uncommited رو انتخاب میکنید، احتمال Dirty Read (دریافت اطلاعات نا معتبر) وجود داره.

[fkohantorabi]

ملاک Clustered کردن یک ایندکس این نیست. Clustered شدن ایندکس تاثیرات بعدی داره که باید ازشون آگاه بود و به جا این تصمیم رو گرفت. استفاده از nolock یا read uncommited تنها زمانی کمک میکنه که شما مشکل همزمانی کاربرها از نظر Lock شدن دارین. با این کار فقط معطل ویرایش سایر کاربران نخواهید شد، و نه بیشتر! گو اینکه وقتی شما nolock یا read uncommited رو انتخاب میکنید، احتمال Dirty Read (دریافت اطلاعات نا معتبر) وجود داره.



select توی SQL Server در خواست LOCK می کند که می تواند به Table Lock تبدیل شود.

ویرایش: نکته شما رو قبول دارم و این بعد از اعمال نکات شما معنی پیدا می کند.

فرزاد-

[razaghi]

من در Table Design از طریق Index/Keys ، index ساختم. و Query زیر در Sql2005 اجرا کردم. باز سرعت به نسبت تغییر نکرده.

[ali_kolahdoozan]

طراحی db رو با query میشه بزاری یکجای کارت لنگه در ضمن از sp استفاده میکنی که ؟

[AminSobati]

من در Table Design از طریق Index/Keys ، index ساختم. و Query زیر در Sql2005 اجرا کردم. باز سرعت به نسبت تغییر نکرده.
لطفا Execution Plan رو ذخیره و ارسال کنید

[AminSobati]

select توی SQL Server در خواست LOCK می کند که می تواند به Table Lock تبدیل شود.

فرزاد جان زمانی که از Isolation Level پیش فرض استفاده میکنید، Lock قرار داده نمیشه. در Isolation Levelهای بالاتر از Repeatable read دستور Select باعث Shared Lock میشه که سایر کاربران هنوز قادر به Select کردن هستند ولی ویرایشات Block خواهد شد

[fkohantorabi]

فرزاد جان زمانی که از Isolation Level پیش فرض استفاده میکنید، Lock قرار داده نمیشه. در Isolation Levelهای بالاتر از Repeatable read دستور Select باعث Shared Lock میشه که سایر کاربران هنوز قادر به Select کردن هستند ولی ویرایشات Block خواهد شد

ببین شاید من درست توضیح نمی دهم ولی اگه میشه برو به توضیح این دستور نگاه کن و بیا بهم بگو که این چه معنی می دهد:




select * from Table1 with (nolock)



فرزاد-

[AminSobati]

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


No shared locks are issued to prevent other transactions from modifying data read by the current transaction

اگر منظورتون تنها در مدت زمان انجام Query (نه کل زمان Transaction) باشه درست میفرمایید.
Lockی که توسط Select قرار میگیره در طول زمان انجام Query، کوتاه مدته و همونطور که عرض کردم چون Shared Lock هست، جلوی خواندن سایر کاربرها گرفته نمیشه. یعنی مشکل همزمانی در گزارش گیری پدید نمیاد. به همین دلیل راه حل موضوع این تاپیک در ایندکس نهفته است.

[fkohantorabi]

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


No shared locks are issued to prevent other transactions from modifying data read by the current transaction
اگر منظورتون تنها در مدت زمان انجام Query (نه کل زمان Transaction) باشه درست میفرمایید.
Lockی که توسط Select قرار میگیره در طول زمان انجام Query، کوتاه مدته و همونطور که عرض کردم چون Shared Lock هست، جلوی خواندن سایر کاربرها گرفته نمیشه. یعنی مشکل همزمانی در گزارش گیری پدید نمیاد. به همین دلیل راه حل موضوع این تاپیک در ایندکس نهفته است.

قبول دارم. این قفلی که در زمان select گرفته می شود در برنامه های با hit بالا خودش رو خوب نشون می دهد. بابته کل مطالبی که تا اینجا فرستادید به هر حال خیلی ممنون.

فرزاد-