بررسی performance این query

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

سلام.
من کوئری زیر رو برای بدست آوردن لیست کالاهایی که در یک بازه زمانی مخصوص گردش ندارند نوشتم.هیچ مشکلی هم نداره.فقط میخواستم ببینم از لحاظ performance مشکلی نداره.


Select Anb.Cod_k,Anb.cod_v,Anb.Rdfanbar,Kl.disk1 As KalaName,Ganb.Name As NameAnbar
,Vhd.Disk1 As VahedName,0 as tedad
From p_anbar Anb
Inner join P_Kala Kl
On Anb.cod_k=kl.cod
Inner join P_ganbar Ganb
On Anb.rdfanbar=Ganb.rdf
Inner join p_Vahed Vhd
On Anb.cod_v=Vhd.Cod
Where (Cast(anb.cod_k as Varchar(13))+Cast(Anb.cod_v as varchar(5))+Cast(Anb.Rdfanbar as Varchar(6)))
Not in (select (Cast(code_k as Varchar(13))+Cast(code_v as Varchar(5))+Cast(Rdfanbar as varchar(6))) as Cod from p_oriz1
inner join p_otpt on p_oriz1.code=p_otpt.code
where p_otpt.date1>='87/01/01' and p_otpt.date1<='87/01/31')
And (Cast(anb.cod_k as Varchar(13))+Cast(Anb.cod_v as varchar(5))+Cast(Anb.Rdfanbar as Varchar(6)))
Not in (select (Cast(code_k as Varchar(13))+Cast(code_v as Varchar(5))+Cast(Rdfanbar as varchar(6))) as Cod from p_iriz1
Inner join p_inpt On p_iriz1.code=p_inpt.code
Where p_inpt.date1>='87/01/01' and p_inpt.date1<='87/01/31')
And (Cast(anb.cod_k as Varchar(13))+Cast(Anb.cod_v as varchar(5))+Cast(Anb.Rdfanbar as Varchar(6)))
Not in (select (Cast(code_k as Varchar(13))+Cast(code_v as Varchar(5))+Cast(Rdfanbar as varchar(6))) as Cod from p_Sriz1
Inner join p_sput On p_sput.code=p_sriz1.code
Where p_sput.date1>='87/01/01' and p_sput.date1<='87/01/31')
And (Cast(anb.cod_k as Varchar(13))+Cast(Anb.cod_v as varchar(5))+Cast(Anb.Rdfanbar as Varchar(6)))
Not in (select (Cast(code_k as Varchar(13))+Cast(code_v as Varchar(5))+Cast(Rdfanbar as varchar(6))) as Cod from p_Priz1
Inner join p_pput On p_pput.code=p_priz1.code
where p_pput.date1>='87/01/01' and p_pput.date1<='87/01/31')
And (Cast(anb.cod_k as Varchar(13))+Cast(Anb.cod_v as varchar(5))+Cast(Anb.Rdfanbar as Varchar(6)))
Not in (select (Cast(code_k as Varchar(13))+Cast(code_v as Varchar(5))+Cast(Rdfanbar as varchar(6))) as Cod from p_griz
Inner join P_garb on p_griz.code=P_garb.code
Where p_garb.date1>='87/01/01' and p_garb.date1<='87/01/31')
And (Cast(anb.cod_k as Varchar(13))+Cast(Anb.cod_v as varchar(5))+Cast(Anb.Rdfanbar as Varchar(6)))
Not in (select (Cast(code_k1 as Varchar(13))+Cast(code_v1 as Varchar(5))+Cast(Rdfanbar1 as varchar(6))) as Cod from p_criz
inner join p_cnvt on p_cnvt.code=p_criz.code
Where (p_criz.code_k1 is not null) and (p_cnvt.date1>='87/01/01' and p_cnvt.date1<='87/01/31'))
And (Cast(anb.cod_k as Varchar(13))+Cast(Anb.cod_v as varchar(5))+Cast(Anb.Rdfanbar as Varchar(6)))
Not in (select (Cast(code_k2 as Varchar(13))+Cast(code_v2 as Varchar(5))+Cast(Rdfanbar2 as varchar(6))) as Cod from p_criz
inner join p_cnvt on p_cnvt.code=p_criz.code
Where (p_criz.code_k2 is not null) and (p_cnvt.date1>='87/01/01' and p_cnvt.date1<='87/01/31'))



اینم plan این کوئری

[ASKaffash]

سلام
حتما داره چرا ؟
تا به حال نام تکنولوژی RushMore را شنیده اید ؟
یک Query مناسب به عواملی نظیر ایندکس های مناسب و روش نوشتن به خصوص شرط ها و... بستگی دارد واز هم مهمتر تا حدامکان نباید از توابع به خصوص UDF در دستورات شرطی استفاده کرد چون در اینصورت براساس تکنولوژی ذکرشده به شدت میتواند در انتخاب ایندکس مناسب (اگر هم خوب ایجادشده باشد) اثرگذارباشد در دستورات شما حتی اگر فرض کنیم رابطها یعنی PK و FK ها درست باشند وایندکس های مناسب هم داشته باشید خیلی از تابع Cast استفاده کرده اید در ضمن اتصال از نوع Inner هم جای بحث دارد

[AminSobati]

حمید جان Plan وضعیت خیلی خوبی داره. این ایندکس رو بساز و مجددا Plan بفرست. امیدوارم یکی دو تا از Scanها بهینه بشن:


create index _a_1 on P_cnvt(Date1,Code)

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

امین جان من اینکارو کردم.plan جدید رو هم فرستادم. باتشکر.

[AminSobati]

الان با ایندکس کمی بهتر از قبل شد

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

ممنون امین جان.دیگه بیشتر از این میشه روش کاری انجام داد یا خیر؟
یا الان در وضعیت مناسبی به سر میبره؟

[AminSobati]

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

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

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

[AminSobati]

در بعضی شرایط، وفتی چند فیلد با هم ترکیب میشن و حاصلش با یک Value مقایسه میشه، میتونیم یک فیلد Computed در جدول داشته باشیم که همیشه حاصل اون ترکیب رو ذخیره کنه (برای هر رکورد). روی این فیلد ایندکس میزنیم و ازش در where استفاده میکنیم. مثلا:

select * from mytable where col1+col2>10

جدول به این شکل تغییر میکنه:

alter table mytable
add c3 as c1+c2

بعد از ساخت ایندکس روی c3 کوئری به این شکل تغییر میکنه:

select * from mytable where c3>10

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

با سلام خدمت اساتید.
من کوئری بالا رو تغییر دادم و با استفاده از Not exists نوشتم.اخه جایی خونده بودم که سرعتش از not in خیلی بیشتره ولی وقتی روی یک دیتابیس هر دو روش رو تست کردم روش اول که با not in بود با حدود 11000 رکورد 27 ثانیه طول میکشه ولی در روش دوم 47 ثانیه طول میکشه.
من plan و کد روش دوم رو قرار میدم تا اساتید نظر بدن.
با تشکر



Select Anb.Cod_k,Anb.cod_v,Anb.Rdfanbar ,Kl.disk1 As KalaName,Ganb.Name As NameAnbar
,Vhd.Disk1 As VahedName,0 as tedad
From p_anbar Anb
Inner join P_Kala Kl
On Anb.cod_k=kl.cod
Inner join P_ganbar Ganb
On Anb.rdfanbar=Ganb.rdf
Inner join p_Vahed Vhd
On Anb.cod_v=Vhd.Cod
Where Not Exists (select * from p_oriz1
inner join p_otpt on p_oriz1.code=p_otpt.code
where (p_otpt.date1>='85/01/01' and p_otpt.date1<='87/01/01')
And (Anb.cod_k=p_oriz1.code_k and anb.cod_v=P_oriz1.code_v and anb.rdfanbar=p_oriz1.rdfanbar))
And Not Exists (select code_k,code_v,Rdfanbar from p_iriz1
Inner join p_inpt On p_iriz1.code=p_inpt.code
Where (p_inpt.date1>='85/01/01' and p_inpt.date1<='87/01/01')
And (Anb.cod_k=p_iriz1.code_k and anb.cod_v=P_iriz1.code_v and anb.rdfanbar=p_iriz1.rdfanbar))
And Not Exists (select code_k,code_v,Rdfanbar from p_Sriz1
Inner join p_sput On p_sput.code=p_sriz1.code
Where (p_sput.date1>='85/01/01' and p_sput.date1<='87/01/01')
And (Anb.cod_k=p_Sriz1.code_k and anb.cod_v=P_Sriz1.code_v and anb.rdfanbar=p_sriz1.rdfanbar))
And Not Exists (select code_k,code_v,Rdfanbar from p_Priz1
Inner join p_pput On p_pput.code=p_priz1.code
where (p_pput.date1>='85/01/01' and p_pput.date1<='87/01/01')
And (Anb.cod_k=p_Priz1.code_k and anb.cod_v=P_Priz1.code_v and anb.rdfanbar=p_Priz1.rdfanbar))
And Not Exists (select code_k,code_v,Rdfanbar from p_griz
Inner join P_garb on p_griz.code=P_garb.code
Where (p_garb.date1>='85/01/01' and p_garb.date1<='87/01/01')
And (Anb.cod_k=p_griz.code_k and anb.cod_v=P_griz.code_v and anb.rdfanbar=p_griz.rdfanbar))
And Not Exists (select code_k1,code_v1,Rdfanbar1 from p_criz
inner join p_cnvt on p_cnvt.code=p_criz.code
Where (p_criz.code_k1 is not null) and (p_cnvt.date1>='85/01/01' and p_cnvt.date1<='87/01/01')
And (Anb.cod_k=p_criz.code_k1 and anb.cod_v=P_criz.code_v1 and anb.rdfanbar=p_criz.rdfanbar1))
And Not Exists (select code_k2,code_v2,Rdfanbar2 from p_criz
inner join p_cnvt on p_cnvt.code=p_criz.code
Where (p_criz.code_k2 is not null) and (p_cnvt.date1>='85/01/01' and p_cnvt.date1<='87/01/01')
And (Anb.cod_k=p_criz.code_k2 and anb.cod_v=P_criz.code_v2 and anb.rdfanbar=p_criz.rdfanbar2))

[AminSobati]

Plan در ظاهر مسئله حادی نشون نمیده. بهتره قبل از اجرای Query دستور set statistics io on رو اجرا کنین و در قسمت Messages چک کنین ببینین چقدر هر کدوم از روشها IO داشتن

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

سلام استاد.من این رو بررسی کردم. حالا بررسی رو اینجا قرار میدم.البته الان که تست کردم با استفاده از Not exists حدود 59 ثانیه طول کشید ولی با استفاده از روش اول حدود 3 دقیقه طول کشید.


Table 'P_kala' . Scan count 9677 , logical reads 19480 , physical reads 145 , read-ahead reads 0.
Table 'P_kala' . Scan count 9677 , logical reads 19480 , physical reads 0 , read-ahead reads 0.

Table 'p_ganbar' . Scan count 9677 , logical reads 19354 , physical reads 2 , read-ahead reads 0.
Table 'p_ganbar' . Scan count 9677 , logical reads 19354 , physical reads 0 , read-ahead reads 0.

Table 'P_criz' . Scan count 0 , logical reads 0 , physical reads 0 , read-ahead reads 0.
Table 'P_criz' . Scan count 0 , logical reads 0 , physical reads 0 , read-ahead reads 0.

Table 'P_cnvt' . Scan count 19354 , logical reads 0 , physical reads 0 , read-ahead reads 0.
Table 'P_cnvt' . Scan count 19354 , logical reads 0 , physical reads 0 , read-ahead reads 0.

Table 'P_garb' . Scan count 0 , logical reads 0 , physical reads 0 , read-ahead reads 0.
Table 'P_garb' . Scan count 0 , logical reads 0 , physical reads 0 , read-ahead reads 0.

Table 'P_griz' . Scan count 9677 , logical reads 19354 , physical reads 2 , read-ahead reads 0.
Table 'P_griz' . Scan count 9677 , logical reads 19354 , physical reads 0 , read-ahead reads 0.

Table 'P_pput' . Scan count 0 , logical reads 0 , physical reads 0 , read-ahead reads 0.
Table 'P_pput' . Scan count 0 , logical reads 0 , physical reads 0 , read-ahead reads 0.

Table 'P_priz1' . Scan count 9677 , logical reads 19354 , physical reads 2 , read-ahead reads 0.
Table 'P_priz1' . Scan count 9677 , logical reads 19354 , physical reads 0 , read-ahead reads 0.

Table 'P_sput' . Scan count 0 , logical reads 0 , physical reads 0 , read-ahead reads 0.
Table 'P_sput' . Scan count 0 , logical reads 0 , physical reads 0 , read-ahead reads 0.

Table 'P_sriz1' . Scan count 9677 , logical reads 19354 , physical reads 2 , read-ahead reads 0.
Table 'P_sriz1' . Scan count 9677 , logical reads 19354 , physical reads 0 , read-ahead reads 0.

Table 'P_inpt' . Scan count 62 , logical reads 124 , physical reads 6 , read-ahead reads 0.
Table 'P_inpt' . Scan count 9739 , logical reads 339607 , physical reads 0 , read-ahead reads 33.

Table 'P_iriz1' . Scan count 9739 , logical reads 184366 , physical reads 1 , read-ahead reads 18.
Table 'P_iriz1' . Scan count 9397705 , logical reads 19644776 , physical reads 0 , read-ahead reads 0.

Table 'P_otpt' . Scan count 1350 , logical reads 2700 , physical reads 46 , read-ahead reads 0.
Table 'P_otpt' . Scan count 1350 , logical reads 2925 , physical reads 0 , read-ahead reads 0.

Table 'P_oriz1' . Scan count 11089 , logical reads 627119 , physical reads 1 , read-ahead reads 59.
Table 'P_oriz1' . Scan count 11089 , logical reads 665081 , physical reads 0 , read-ahead reads 0.

Table 'P_Anbar' . Scan count 1 , logical reads 397 , physical reads 2 , read-ahead reads 396.
Table 'P_Anbar' . Scan count 1 , logical reads 397 , physical reads 0 , read-ahead reads 0.

Table 'p_vahed' . Scan count 1 , logical reads 2 , physical reads 2 , read-ahead reads 0.
Table 'p_vahed' . Scan count 1 , logical reads 2 , physical reads 0 , read-ahead reads 0.



ردیف اول مربوط به دستور Not exists و ردیف دوم مربوط به Not in هست. ولی تعداد Scan ها در not in خیلی وحشتناک زیاده.مثلا در جدول p_inpt تعداد scan ها در روش Not exists 62 مرحله است ولی در روش دوم 9700 تعداد هست.
دلیل اینهمه اختلاف برای چیه؟
ممنون میشم در مورد این نتیجه ای که داده توضیح بدین.
با تشکر.

[AminSobati]

لطفا خروجی این دو دستور رو پست کنین:


exec sp_spaceused 'P_iriz1'
go
dbcc showcontig('P_iriz1')

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

سلام استاد. اینم خروجی دو دستور



Name Rows reserved data index_size unused
------ ----- ------------- ----- -------- ------
P_iriz1 1036 264 KB 88 KB 16 KB 160 KB

------------------------------------------------------------------
DBCC SHOWCONTIG scanning 'P_iriz1' table...
Table: 'P_iriz1' (165575628); index ID: 1, database ID: 18
TABLE level scan performed.
- Pages Scanned................................: 11
- Extents Scanned..............................: 8
- Extent Switches..............................: 8
- Avg. Pages per Extent........................: 1.4
- Scan Density [Best Count:Actual Count].......: 22.22% [2:9]
- Logical Scan Fragmentation ..................: 45.45%
- Extent Scan Fragmentation ...................: 75.00%
- Avg. Bytes Free per Page.....................: 2911.2
- Avg. Page Density (full).....................: 64.03%

[AminSobati]

Clustered Index جدول 'P_iriz1' رو با Fillfactor=93% یکبار Rebuild کنین ببینین آیا مقدار IO کاهش پیدا میکنه یا نه