PDA

View Full Version : نمایش index در execution plan



mit_89
چهارشنبه 06 بهمن 1389, 18:05 عصر
سلام دوستان
من یک جدول دارم به نام prc که کالاها در آن نگهداری می شه .در برنامه که تحت ویندوز با c# است یک گرید از این کالاها دارم که کاربر با زدن حروف روی گرید می تونه جستجو کنه.ولی این جستجو و لود شدن اولیه خود گرید هم خبلی کند است .
جدول 2 تا index دارد یکی همون pk خودش و یک ایندکس که خودم درست کردم روی فیلد unam که در where ه کار می رود ولی وقتی همراه با اجرای storeprocedure دکمه ctrl + M را میزنم در execution plan آن فقط ایندکس
pk ا نمایش میدهد.می شه بگید باید چه کاری انجام بدم تا ایندکس که خودم هم ساختم در execution plan ببینم.
نمی دونم این به این معنی هست که index که روی فیلد unam ساختم رو نمی شناسه؟؟؟

کد store procedure



ALTER PROCEDURE [dbo].[prcq] (

@idanbar int,

@ns int,

@sstr nvarchar(300)
)

AS

if (@ns=1 and @idanbar=1 )

begin



SELECT id, nam, unit, unit2, box, price, pric, stut, unam, price1, price2, mainid, idanbar, prictmp, numbtmp, wi, tp, barcod, err_numb, minnumb, vi_numb,
ptk, lowprice, hightprice, groupid, vpercent, f_j, per_ma, per_av ,ltrim(rtrim(dbo.numbtext2(numb,box))) as numb FROM dbo.fn_prc() fn_prc WHERE (idanbar = @idanbar) and (stut=0) AND (unam LIKE @sstr) order by unam

End

Else

begin

SELECT id, nam, unit, unit2, box, price, pric, stut, unam, price1, price2, mainid, idanbar, prictmp, numbtmp, wi, tp, barcod, err_numb, minnumb, vi_numb,
ptk, lowprice, hightprice, groupid, vpercent, f_j, per_ma, per_av,ltrim(rtrim(dbo.numbtext2(numb,box))) as numb FROM dbo.fn_prc() fn_prc WHERE (numb>0) and (stut=0) and (idanbar = @idanbar) AND (unam LIKE @sstr) order by unam
End





ین هم عکس plan
65601

file

حمیدرضاصادقیان
چهارشنبه 06 بهمن 1389, 19:24 عصر
سلام.
الزاما این نیست که SQL Server حتما از ایندکسهای شما استفاده کنه. چون خود Omptimizer ترجیح میده که بدون ایندکس سرعت شما سریعتر خواهد شد به همین خاطر اونو در Plan شرکت نداده است.
شما تمامی فیلدهای موجود در عبارت where رو در یک ایندکس قرار دهید دوباره دستور رو اجرا کنید و plan رو قرار بدید.

mit_89
پنج شنبه 07 بهمن 1389, 10:07 صبح
سلام اول خیلی ازتون ممنونم که به سوالم جواب دادید.راستش خیلی به خاطر سرعت جستجو گیر افتادم دلم می خواد بدونم چرا Omptimizer این فیلد رو در نظر نمی گیره چون جستجوی اصلی روی این فیلد هست.به خصوصیت دیگری از این فیلد ربط پیدا می کنه؟؟

دستور ساخت index



CREATE NONCLUSTERED INDEX [IX_prc3] ON [dbo].[prc2]
(
[stut] ASC,
[unam] ASC,
[idanbar] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]



عکس plan
65625

حمیدرضاصادقیان
پنج شنبه 07 بهمن 1389, 20:12 عصر
سلام.
لطفا فایل plan رو ذخیره کرده و اینجا قرار دهید.

m_omrani
پنج شنبه 07 بهمن 1389, 22:45 عصر
می دونید. مشکل فقط داشتن ایندکس مناسب نیست. بعضی وقت ها به قول آقای صادقیان optimizer اصلاً سراغ ایندکس شما نمی ره یا نمی تونه بره. نحوه پرس و جوی شما جوریه که به نظرم اجراش هزینه بره. چون از خروجی تابع (fn_prc) دارید دیتا رو select می زنید. به نظرم سعی کنید پرس و جو تون رو کلاً تغییر بدید و به شکل دیگه ای بنویسید. مثلاً پرس و جوی داخل تابع رو بیارید بیرون و مستقیماً داخل Stored Procedure بنویسید. همچنین یه تابع به نام dbo.numbtext() رو هم در قسمت select استفاده کردید. این هم می تونه سربار داشته باشه. چون عمل محاسبه سطر به سطر یا رکورد به رکورد رو ایجاد می کنه.

mit_89
جمعه 08 بهمن 1389, 10:57 صبح
سلام دوستان بازم ازتوجه تون خیلی ممنونم.
فایل plan رو در خود sql فقط تونستم با پسوند خود sql ذخیره کنم. اگه این هم درست نبود لطفا راهنمایی کنید که چطور plan رو ذخیره کنم؟؟
ساختار دیتابیس ما به این صورت است که یک دیتابیس main داریم که همه جداول اصلی در آن هستند و به تعداد کاربران دیتابیس user دارم که در آن از جداول اصلی view یا function درست شده و storeprocedure ها هم در user است.این جدول که دارم روش کوئری می گیرم به نام prc2 در main وجود دارد و به صوزت زیر از آن یک function درست شده و در پروسیجر از آن function به نام fn_prc() استفاده می کنم.

ALTER FUNCTION [dbo].[fn_prc]() RETURNS Table AS RETURN (SELECT * from [main1].dbo.prc2)و plan

m_omrani
جمعه 08 بهمن 1389, 12:16 عصر
و به تعداد کاربران دیتابیس user دارم که در آن از جداول اصلی view یا function درست شده و storeprocedure ها هم در user است.

من خيلي متوجه نشدم. لطفاً کمي بيشتر توضيح بديد.

mit_89
جمعه 08 بهمن 1389, 13:15 عصر
دیتابیس اصلی که main است فقط جداول اصلی برنامه رادارد و هیچ پروسیجر یا view در آن نیست. فرض کنید در محلی که مثلا 4 نفر با 4 تا کامپیوتر می خوان با این برنامه کار کنند ما 4 تا دیتابیس به نام user1,user2,user3,user4 درست میکنیم که هر کاربر به یکی از این user ها وصل می شود. در داخل این user ها view ها و function هایی که از جدول های موجود در main ساخته شده وجود دارد و همچنین تمام store procedure ها که در خود user ساخته شده و از طریق این view و function ها به main دسترسی دارند.
راستش نمی ونم چرا با وجود اینکه طبق گفته آقای صادقیان 3 تا شرط موجود در where رو هم که index کردم باز هم همان طور که در plan می بینید این ایندکس در نظر گرفته نشده است:ناراحت:

حمیدرضاصادقیان
جمعه 08 بهمن 1389, 23:43 عصر
سلام.

ما 4 تا دیتابیس به نام user1,user2,user3,user4 درست میکنیم که هر کاربر به یکی از این user ها وصل می شود.

دوست عزیز شما چرا لقمه رو دور سرتون میچرخونید؟!!!
یعنی اگر 100 نفر بخوان همزمان کار کنند 100 تا دیتابیس ایجاد میکنید؟ میدونید چه ترافیک اضافی دارید به شبکه تحمیل می کنید؟
بعد در مورد ایندکس ها همون طور که جناب m_omrani گفتند چون شما داری در کوئری از تابع ها هم استفاده میکنی باید سورس اونا هم مورد بررسی قرار بگیره.وگرنه اگر یک Select ساده از جدول بگیرید و همون فیلدها رو در شرط where قرار بدید احتمال استفاده از ایندکس افزایش پیدا میکنه.

mit_89
جمعه 08 بهمن 1389, 23:57 عصر
می شه منو راهنمایی کنید که ایندکس مناسب چی می تونه باشه؟؟
ببخشید یک سوال دیگه covering index ها هم از راه include روی ایندکس ها درست می شوند و هم می تونیم فیلدهای که در select هست رو جزء فیلدهای index قرار داد؟؟

mit_89
شنبه 09 بهمن 1389, 00:18 صبح
حالا شما ببخشید چرا می زنید؟؟؟
راستش از تابع numtxt2 مجبورم استفاده کنم برای نشام دادن تعداد کالا بر اساس واحد آن به صورت 20+3 و راستش الان این کوئری رو با دادن یک رشته متفاوت به فیلد unam روی خود جدول و همین حالت لقمه پیچیده تست کردم plan به صورت زیر شد.فقط من خیلی از این plan ها سر در نمیارم اگه می شه شما لطف کنید یک نگاهی بهش بندازید ببینید این cost ها باید در چه وضعی باشند؟

plan5 مربوط به زمانی است که دقیقا همان select داخل store procedure را در پنجره new query با دادن مقادیر زیر اجرا می کنم:


SELECT id, nam, unit, unit2, box, price, pric, stut, unam, price1, price2, mainid, idanbar, prictmp, numbtmp, wi, tp, barcod, err_numb, minnumb, vi_numb,
ptk, lowprice, hightprice, groupid, vpercent, f_j, per_ma, per_av ,ltrim(rtrim(dbo.numbtext2(numb,box))) as numb FROM dbo.fn_prc() fn_prc WHERE (idanbar = 1) and (stut=0) AND (unam LIKE '%مپ%') order by unam و plan6 هنگامی که روی خود store procedure که در اولین پست گذاشتم اتفاق می افتد به شکل زیر:


EXEC @return_value = [dbo].[prcq]
@idanbar = 1,
@ns = 1,
@sstr = N'%مپ%'

SELECT 'Return Value' = @return_value

GO

حمیدرضاصادقیان
شنبه 09 بهمن 1389, 22:12 عصر
سلام.
این Plan ها تقریبا در شرایط خوبی هستند.زیرا از ایندکس ها و PK ها استفاده کرده و نوع Join های استفاده شده نیز Inner Join می باشد.
الان مدت زمان اجرای دستور چقدر می باشد.؟

mit_89
یک شنبه 10 بهمن 1389, 15:23 عصر
سلام آقای صادقیان ، خیلی ازتون ممنونم که وقت گذاشتید و تا اینجا منو راهنمایی کردید.

راستش با عرض معذرت نمی دونم این زمان اجرای دستور را چه طوری باید محاسبه کنم؟:خجالت: به نظر شما اگر فیلدهایی که در select این کوئری شرکت کردند 7تا فیلد باشد و این کوئری در برنامه اهمیت زیادی داشته باشد از نظر سرعت اجرا ، به صرفه هست که این فیلدها رو در ایندکس ساخته شده include کنم ؟؟

و ببخشید یک سوال دیگه؟؟ برای یک کوئری یه شکل زیر این ایندکس با این plan مناسبه؟؟


SELECT id,numfact,idsanad,sharh,stut,idtafseal,idsaz,yad,
CONVERT(BIGINT,case when stut=102 then bed-takh else bed end) as be,
CONVERT(BIGINT,case when stut=101 then bas-takh else bas end) as ba,
dbo.pdate(dat) as dat,curusernam,takh,tsh,convert(bigint,mand) as mand,CONVERT(BIGINT,(bed-bas)) as mandkol,id,dbo.pdate(nowdate) as nowdate,CONVERT(varchar(50), nowdate, 108) as timesanad ,radifid
FROM naghd WHERE (idtafseal = 581) ORDER BY dbo.fn_naghd.dat,id



index:


CREATE NONCLUSTERED INDEX [IX_naghd1] ON [dbo].[naghd]
(
[dat] ASC,
[idtafseal] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, FILLFACTOR = 80) ON [PRIMARY]


planدر صورت اینکه idtafseal عددی باشد که تعداد رکوردهای کمتی برگرداند plann1 اگر مثلا تعداد رکوردهای نزدیک به 442 و بیشتر plann2 را برمی گرداند.

باز هم به خاطر توجه تون ممنون

حمیدرضاصادقیان
یک شنبه 10 بهمن 1389, 23:10 عصر
سلام.
من یک توضیحی درمورد ایندکس گذاری بدم.
ببینید این طور نیست که هر فیلدی که دیدیم در لیست Selectهست یا در شرط where هست رو داخل ایندکس قرار بدیم.
چون ایندکس ها مخصوصا ایندکس های کلاستر روی سرعت updateو insert شما تاثیر خواهند گذاشت. پس باید به شدت محتاطانه عمل کنید.
برای ایندکس گذاری باید شرایط کل جدول مورد بررسی قرار بگیره.کوئری مورد نظر قبل از ایندکس گذاری plan اون مورد بررسی قرار بگیره.میزان update,insert اطلاعات اون جدول مورد بررسی قرار بگیره.
درنهایت اقدام به ایندکس گذاری کنیم.