نمایش index در execution plan

[mit_89]

سلام دوستان
من یک جدول دارم به نام prc که کالاها در آن نگهداری می شه .در برنامه که تحت ویندوز با C#‎ است یک گرید از این کالاها دارم که کاربر با زدن حروف روی گرید می تونه جستجو کنه.ولی این جستجو و لود شدن اولیه خود گرید هم خبلی کند است .
جدول 2 تا index دارد یکی همون pk خودش و یک ایندکس که خودم درست کردم روی فیلد unam که در where ه کار می رود ولی وقتی همراه با اجرای storeprocedure دکمه ctrl + M را میزنم در execution plan آن فقط ایندکس
pk ا نمایش میدهد.می شه بگید باید چه کاری انجام بدم تا ایندکس که خودم هم ساختم در execution plan ببینم.
نمی دونم این به این معنی هست که index که روی فیلد unam ساختم رو نمی شناسه؟؟؟

کد store procedure

ALTERPROCEDURE [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)orderby 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)orderby unam 


End

 

ین هم عکس plan
plan.JPG

file

[حمیدرضاصادقیان]

سلام.
الزاما این نیست که SQL Server حتما از ایندکسهای شما استفاده کنه. چون خود Omptimizer ترجیح میده که بدون ایندکس سرعت شما سریعتر خواهد شد به همین خاطر اونو در Plan شرکت نداده است.
شما تمامی فیلدهای موجود در عبارت where رو در یک ایندکس قرار دهید دوباره دستور رو اجرا کنید و plan رو قرار بدید.

[mit_89]

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

دستور ساخت index

CREATENONCLUSTEREDINDEX [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
plan2.JPG

[حمیدرضاصادقیان]

سلام.
لطفا فایل plan رو ذخیره کرده و اینجا قرار دهید.

[m_omrani]

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

[mit_89]

سلام دوستان بازم ازتوجه تون خیلی ممنونم.
فایل 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]

و به تعداد کاربران دیتابیس user دارم که در آن از جداول اصلی view یا function درست شده و storeprocedure ها هم در user است.

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

[mit_89]

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

[حمیدرضاصادقیان]

سلام.

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

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

[mit_89]

می شه منو راهنمایی کنید که ایندکس مناسب چی می تونه باشه؟؟
ببخشید یک سوال دیگه covering index ها هم از راه include روی ایندکس ها درست می شوند و هم می تونیم فیلدهای که در select هست رو جزء فیلدهای index قرار داد؟؟

[mit_89]

حالا شما ببخشید چرا می زنید؟؟؟
راستش از تابع 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

[حمیدرضاصادقیان]

سلام.
این Plan ها تقریبا در شرایط خوبی هستند.زیرا از ایندکس ها و PK ها استفاده کرده و نوع Join های استفاده شده نیز Inner Join می باشد.
الان مدت زمان اجرای دستور چقدر می باشد.؟

[mit_89]

سلام آقای صادقیان ، خیلی ازتون ممنونم که وقت گذاشتید و تا اینجا منو راهنمایی کردید.

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

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

SELECT id,numfact,idsanad,sharh,stut,idtafseal,idsaz,yad,

CONVERT(BIGINT,casewhen stut=102 then bed-takh else bed end)as be,

CONVERT(BIGINT,casewhen 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)ORDERBY dbo.fn_naghd.dat,id

index:

CREATENONCLUSTEREDINDEX [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 را برمی گرداند.

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

[حمیدرضاصادقیان]

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