Performance این Query

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

سلام دوستان.من query زیر رو نوشتم.


select a.cod_k,a.cod_v,a.prc_a,a.prc_p,a.prc_s,a.tdad1,a. tdad2,a.tdad3 , a.prc_s2 , a.prc_s3
,(SELECT Name FROM p_ganbar where rdfanbar=rdf ) as AnbarName
,(select disk1 from p_vahed where A.cod_v=cod ) As VahedName
,(select grp_c from P_Gkala where k.grp=grp ) As GropName
,a.tmpt,a.tmax,a.tmin,a.kharid,a.forush,a.fo_dar
,k.disk1,k.grp,k.mark
,prc_p*tdad2 arkh
,prc_s*tdad2 arfo,rdfAnbar ,old_year
from p_anbar a,p_kala k

where (cod_k=k.cod)


عکس سمت راست مربوط به plan این query می باشد. و روی 100000 رکورد تقریبا 12 ثانیه طول میکشه.
این Query رو به شکل زیر بازنویسی کردم ولی تقریبا 25 ثانیه طول کشید دیتا رو بخونه.






select anbar.cod_k,anbar.cod_v,anbar.prc_a,anbar.prc_p,an bar.prc_s,anbar.tdad1,anbar.tdad2,anbar.tdad3,
anbar.prc_s2,anbar.prc_s3 ,pganbar.name as AnbarName,pvahed.Disk1 as VahedName,
pgkala.grp_c as GropName,anbar.tmpt,anbar.tmax,anbar.tmin,anbar.kh arid,anbar.forush,anbar.fo_dar,
pkala.disk1,pkala.grp,pkala.mark,anbar.prc_p*anbar .tdad2 arkh,
anbar.prc_s*anbar.tdad2 arfo,anbar.rdfAnbar ,anbar.old_year from P_anbar as anbar
join(select disk1,grp,mark,cod from p_kala) as pkala
On anbar.cod_k=pkala.cod
Join(Select grp_c,grp from p_gkala) as pgkala
On pkala.grp=pgkala.grp
Join(Select Name,rdf from p_ganbar) as pganbar
On anbar.rdfanbar=pganbar.rdf
join(select disk1,cod from p_vahed) as pvahed
on anbar.cod_v=pvahed.cod

عکس سمت چپ هم مربوط به plan این query می باشه.میخواستم ببینم میشه جور دیگه ایی نوشت که سرعتش افزایش پیدا کنه.

[AminSobati]

قبل از اجرای هر Query دستور زیر رو اجرا کنین و خروجی رو بصورت text بفرستین:
set statistics profile on

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

این مربوط به query اول می باشد.


100719 1 select a.cod_k,a.cod_v,a.prc_a,a.prc_p,a.prc_s,a.tdad1,a. tdad2,a.tdad3 , a.prc_s2 , a.prc_s3 ,(SELECT Name FROM p_ganbar where rdfanbar=rdf ) as AnbarName ,(select disk1 from p_vahed where A.cod_v=cod ) As VahedName ,(select grp_c from P_Gkala wh 3 1 0 NULL NULL NULL NULL 77841.617 NULL NULL NULL 11.941926 NULL NULL SELECT 0 NULL
100719 1 |--Compute Scalar(DEFINE:([p_ganbar].[Name]=[p_ganbar].[Name], [p_vahed].[disk1]=[p_vahed].[disk1], [P_Gkala].[grp_c]=[P_Gkala].[grp_c], [Expr1014]=[a].[Prc_p]*[a].[Tdad2], [Expr1015]=[a].[Prc_s]*[a].[Tdad2])) 3 2 1 Compute Scalar Compute Scalar DEFINE:([p_ganbar].[Name]=[p_ganbar].[Name], [p_vahed].[disk1]=[p_vahed].[disk1], [P_Gkala].[grp_c]=[P_Gkala].[grp_c], [Expr1014]=[a].[Prc_p]*[a].[Tdad2], [Expr1015]=[a].[Prc_s]*[a].[Tdad2]) [p_ganbar].[Name]=[p_ganbar].[Name], [p_vahed].[disk1]=[p_vahed].[disk1], [P_Gkala].[grp_c]=[P_Gkala].[grp_c], [Expr1014]=[a].[Prc_p]*[a].[Tdad2], [Expr1015]=[a].[Prc_s]*[a].[Tdad2] 77841.617 0.0 7.7841617E-3 303 11.941926 [a].[Cod_k], [a].[Prc_a], [a].[Tdad1], [a].[Tdad3], [a].[prc_s2], [a].[prc_s3], [a].[Cod_v], [a].[Tmpt], [a].[Tmax], [a].[Tmin], [a].[Kharid], [a].[Forush], [a].[Fo_dar], [a].[Prc_p], [a].[Tdad2], [a].[Prc_s], [a].[rdfAnbar], [a].[OLD_YEAR], [k].[Disk1], [ NULL PLAN_ROW 0 1.0
100719 1 |--Hash Match(Right Outer Join, HASH:([P_Gkala].[grp])=([k].[grp])) 3 3 2 Hash Match Right Outer Join HASH:([P_Gkala].[grp])=([k].[grp]) NULL 77841.617 0.0 0.43407199 271 11.934141 [a].[Cod_k], [a].[Prc_a], [a].[Tdad1], [a].[Tdad3], [a].[prc_s2], [a].[prc_s3], [a].[Cod_v], [a].[Tmpt], [a].[Tmax], [a].[Tmin], [a].[Kharid], [a].[Forush], [a].[Fo_dar], [a].[Prc_p], [a].[Tdad2], [a].[Prc_s], [a].[rdfAnbar], [a].[OLD_YEAR], [k].[Disk1], [ NULL PLAN_ROW 0 1.0
33 1 |--Clustered Index Scan(OBJECT:([honar86].[dbo].[P_Gkala].[PK_P_Gkala])) 3 4 3 Clustered Index Scan Clustered Index Scan OBJECT:([honar86].[dbo].[P_Gkala].[PK_P_Gkala]) [P_Gkala].[grp], [P_Gkala].[grp_c] 33.0 3.7578501E-2 0.0001148 43 3.7693299E-2 [P_Gkala].[grp], [P_Gkala].[grp_c] NULL PLAN_ROW 0 1.0
100719 1 |--Hash Match(Right Outer Join, HASH:([p_vahed].[Cod])=([a].[Cod_v])) 3 5 3 Hash Match Right Outer Join HASH:([p_vahed].[Cod])=([a].[Cod_v]) NULL 77841.617 0.0 0.54185331 253 11.462374 [a].[Cod_k], [a].[Prc_a], [a].[Tdad1], [a].[Tdad3], [a].[prc_s2], [a].[prc_s3], [a].[Cod_v], [a].[Tmpt], [a].[Tmax], [a].[Tmin], [a].[Kharid], [a].[Forush], [a].[Fo_dar], [a].[Prc_p], [a].[Tdad2], [a].[Prc_s], [a].[rdfAnbar], [a].[OLD_YEAR], [k].[Disk1], [ NULL PLAN_ROW 0 1.0
8 1 |--Clustered Index Scan(OBJECT:([honar86].[dbo].[p_vahed].[PK_p_vahed])) 3 6 5 Clustered Index Scan Clustered Index Scan OBJECT:([honar86].[dbo].[p_vahed].[PK_p_vahed]) [p_vahed].[Cod], [p_vahed].[disk1] 8.0 3.7578501E-2 8.7300003E-5 38 3.7665799E-2 [p_vahed].[Cod], [p_vahed].[disk1] NULL PLAN_ROW 0 1.0
100719 1 |--Nested Loops(Left Outer Join, WHERE:([a].[rdfAnbar]=[p_ganbar].[rdf])) 3 7 5 Nested Loops Left Outer Join WHERE:([a].[rdfAnbar]=[p_ganbar].[rdf]) NULL 77841.617 0.0 0.32537797 295 10.882852 [a].[Cod_k], [a].[Prc_a], [a].[Tdad1], [a].[Tdad3], [a].[prc_s2], [a].[prc_s3], [a].[Cod_v], [a].[Tmpt], [a].[Tmax], [a].[Tmin], [a].[Kharid], [a].[Forush], [a].[Fo_dar], [a].[Prc_p], [a].[Tdad2], [a].[Prc_s], [a].[rdfAnbar], [a].[OLD_YEAR], [k].[Disk1], [ NULL PLAN_ROW 0 1.0
100719 1 |--Merge Join(Inner Join, MERGE:([k].[Cod])=([a].[Cod_k]), RESIDUAL:([k].[Cod]=[a].[Cod_k])) 3 8 7 Merge Join Inner Join MERGE:([k].[Cod])=([a].[Cod_k]), RESIDUAL:([k].[Cod]=[a].[Cod_k]) NULL 77841.617 0.0 0.45473632 263 10.465694 [a].[Cod_k], [a].[Prc_a], [a].[Tdad1], [a].[Tdad3], [a].[prc_s2], [a].[prc_s3], [a].[Cod_v], [a].[Tmpt], [a].[Tmax], [a].[Tmin], [a].[Kharid], [a].[Forush], [a].[Fo_dar], [a].[Prc_p], [a].[Tdad2], [a].[Prc_s], [a].[rdfAnbar], [a].[OLD_YEAR], [k].[Disk1], [ NULL PLAN_ROW 0 1.0
100736 1 | |--Clustered Index Scan(OBJECT:([honar86].[dbo].[P_kala].[PK_P_kala] AS [k]), ORDERED FORWARD) 3 9 8 Clustered Index Scan Clustered Index Scan OBJECT:([honar86].[dbo].[P_kala].[PK_P_kala] AS [k]), ORDERED FORWARD [k].[Cod], [k].[Disk1], [k].[grp], [k].[Mark] 100737.0 0.42720813 0.1108892 98 0.53809732 [k].[Cod], [k].[Disk1], [k].[grp], [k].[Mark] NULL PLAN_ROW 0 1.0
100719 1 | |--Sort(ORDER BY:([a].[Cod_k] ASC)) 3 10 8 Sort Sort ORDER BY:([a].[Cod_k] ASC) NULL 100719.0 1.1261261E-2 7.6835189 173 9.4728575 [a].[Cod_k], [a].[Prc_a], [a].[Tdad1], [a].[Tdad3], [a].[prc_s2], [a].[prc_s3], [a].[Cod_v], [a].[Tmpt], [a].[Tmax], [a].[Tmin], [a].[Kharid], [a].[Forush], [a].[Fo_dar], [a].[Prc_p], [a].[Tdad2], [a].[Prc_s], [a].[rdfAnbar], [a].[OLD_YEAR] NULL PLAN_ROW 0 1.0
100719 1 | |--Clustered Index Scan(OBJECT:([honar86].[dbo].[P_Anbar].[PK_P_Anbar] AS [a])) 3 11 10 Clustered Index Scan Clustered Index Scan OBJECT:([honar86].[dbo].[P_Anbar].[PK_P_Anbar] AS [a]) [a].[Cod_k], [a].[Prc_a], [a].[Tdad1], [a].[Tdad3], [a].[prc_s2], [a].[prc_s3], [a].[Cod_v], [a].[Tmpt], [a].[Tmax], [a].[Tmin], [a].[Kharid], [a].[Forush], [a].[Fo_dar], [a].[Prc_p], [a].[Tdad2], [a].[Prc_s], [a].[rdfAnbar], [a].[OLD_YEAR] 100719.0 0.83360404 0.0554347 196 1.7780775 [a].[Cod_k], [a].[Prc_a], [a].[Tdad1], [a].[Tdad3], [a].[prc_s2], [a].[prc_s3], [a].[Cod_v], [a].[Tmpt], [a].[Tmax], [a].[Tmin], [a].[Kharid], [a].[Forush], [a].[Fo_dar], [a].[Prc_p], [a].[Tdad2], [a].[Prc_s], [a].[rdfAnbar], [a].[OLD_YEAR] NULL PLAN_ROW 0 1.0
100719 100719 |--Table Spool 3 15 7 Table Spool Lazy Spool NULL NULL 1.0 1.6756756E-2 3.7999999E-7 39 6.8426549E-2 [p_ganbar].[rdf], [p_ganbar].[Name] NULL PLAN_ROW 0 77841.617
1 1 |--Clustered Index Scan(OBJECT:([honar86].[dbo].[p_ganbar].[PK_p_ganbar])) 3 16 15 Clustered Index Scan Clustered Index Scan OBJECT:([honar86].[dbo].[p_ganbar].[PK_p_ganbar]) [p_ganbar].[rdf], [p_ganbar].[Name] 1.0 3.7578501E-2 7.9600002E-5 56 3.7658099E-2 [p_ganbar].[rdf], [p_ganbar].[Name] NULL PLAN_ROW 0 1.0



اینم مربوط به query دوم.


100719 1 select anbar.cod_k,anbar.cod_v,anbar.prc_a,anbar.prc_p,an bar.prc_s,anbar.tdad1,anbar.tdad2,anbar.tdad3, anbar.prc_s2,anbar.prc_s3 ,pganbar.name as AnbarName,pvahed.Disk1 as VahedName, pgkala.grp_c as GropName,anbar.tmpt,anbar.tmax,anbar.tmi 2 1 0 NULL NULL NULL NULL 51.994286 NULL NULL NULL 3.5340128 NULL NULL SELECT 0 NULL
100719 1 |--Compute Scalar(DEFINE:([Expr1009]=[anbar].[Prc_p]*[anbar].[Tdad2], [Expr1010]=[anbar].[Prc_s]*[anbar].[Tdad2])) 2 2 1 Compute Scalar Compute Scalar DEFINE:([Expr1009]=[anbar].[Prc_p]*[anbar].[Tdad2], [Expr1010]=[anbar].[Prc_s]*[anbar].[Tdad2]) [Expr1009]=[anbar].[Prc_p]*[anbar].[Tdad2], [Expr1010]=[anbar].[Prc_s]*[anbar].[Tdad2] 51.994286 0.0 5.1994289E-6 303 3.5340128 [anbar].[Cod_k], [anbar].[Cod_v], [anbar].[Prc_a], [anbar].[Tdad1], [anbar].[Tdad3], [anbar].[prc_s2], [anbar].[prc_s3], [anbar].[Tmpt], [anbar].[Tmax], [anbar].[Tmin], [anbar].[Kharid], [anbar].[Forush], [anbar].[Fo_dar], [anbar].[Prc_p], [anbar].[Tdad2], NULL PLAN_ROW 0 1.0
100719 1 |--Nested Loops(Inner Join, OUTER REFERENCES:([anbar].[Cod_v])) 2 3 2 Nested Loops Inner Join OUTER REFERENCES:([anbar].[Cod_v]) NULL 51.994286 0.0 2.1733611E-4 317 3.5340075 [anbar].[Cod_k], [anbar].[Cod_v], [anbar].[Prc_a], [anbar].[Tdad1], [anbar].[Tdad3], [anbar].[prc_s2], [anbar].[prc_s3], [anbar].[Tmpt], [anbar].[Tmax], [anbar].[Tmin], [anbar].[Kharid], [anbar].[Forush], [anbar].[Fo_dar], [anbar].[Prc_p], [anbar].[Tdad2], NULL PLAN_ROW 0 1.0
100719 1 |--Nested Loops(Inner Join, OUTER REFERENCES:([anbar].[rdfAnbar])) 2 4 3 Nested Loops Inner Join OUTER REFERENCES:([anbar].[rdfAnbar]) NULL 51.994286 0.0 2.1735771E-4 288 3.5232444 [anbar].[Cod_k], [anbar].[Cod_v], [anbar].[Prc_a], [anbar].[Tdad1], [anbar].[Tdad3], [anbar].[prc_s2], [anbar].[prc_s3], [anbar].[Tmpt], [anbar].[Tmax], [anbar].[Tmin], [anbar].[Kharid], [anbar].[Forush], [anbar].[Fo_dar], [anbar].[Prc_p], [anbar].[Tdad2], NULL PLAN_ROW 0 1.0
100719 1 | |--Hash Match(Inner Join, HASH:([P_kala].[Cod])=([anbar].[Cod_k]), RESIDUAL:([anbar].[Cod_k]=[P_kala].[Cod])) 2 5 4 Hash Match Inner Join HASH:([P_kala].[Cod])=([anbar].[Cod_k]), RESIDUAL:([anbar].[Cod_k]=[P_kala].[Cod]) NULL 51.999454 0.0 0.67936432 240 3.5124817 [anbar].[Cod_k], [anbar].[Cod_v], [anbar].[Prc_a], [anbar].[Tdad1], [anbar].[Tdad3], [anbar].[prc_s2], [anbar].[prc_s3], [anbar].[Tmpt], [anbar].[Tmax], [anbar].[Tmin], [anbar].[Kharid], [anbar].[Forush], [anbar].[Fo_dar], [anbar].[Prc_p], [anbar].[Tdad2], NULL PLAN_ROW 0 1.0
100737 1 | | |--Hash Match(Inner Join, HASH:([P_Gkala].[grp])=([P_kala].[grp])) 2 6 5 Hash Match Inner Join HASH:([P_Gkala].[grp])=([P_kala].[grp]) NULL 67.293938 0.0 0.47924322 78 1.0550369 [P_kala].[Cod], [P_kala].[Disk1], [P_kala].[grp], [P_kala].[Mark], [P_Gkala].[grp_c] NULL PLAN_ROW 0 1.0
33 1 | | | |--Clustered Index Scan(OBJECT:([honar86].[dbo].[P_Gkala].[PK_P_Gkala])) 2 7 6 Clustered Index Scan Clustered Index Scan OBJECT:([honar86].[dbo].[P_Gkala].[PK_P_Gkala]) [P_Gkala].[grp], [P_Gkala].[grp_c] 33.0 3.7578501E-2 0.0001148 43 3.7693299E-2 [P_Gkala].[grp], [P_Gkala].[grp_c] NULL PLAN_ROW 0 1.0
100737 1 | | | |--Clustered Index Scan(OBJECT:([honar86].[dbo].[P_kala].[PK_P_kala])) 2 8 6 Clustered Index Scan Clustered Index Scan OBJECT:([honar86].[dbo].[P_kala].[PK_P_kala]) [P_kala].[Cod], [P_kala].[Disk1], [P_kala].[grp], [P_kala].[Mark] 100737.0 0.42720813 0.1108892 98 0.53809732 [P_kala].[Cod], [P_kala].[Disk1], [P_kala].[grp], [P_kala].[Mark] NULL PLAN_ROW 0 1.0
100719 1 | | |--Clustered Index Scan(OBJECT:([honar86].[dbo].[P_Anbar].[PK_P_Anbar] AS [anbar])) 2 10 5 Clustered Index Scan Clustered Index Scan OBJECT:([honar86].[dbo].[P_Anbar].[PK_P_Anbar] AS [anbar]) [anbar].[Cod_k], [anbar].[Cod_v], [anbar].[Prc_a], [anbar].[Tdad1], [anbar].[Tdad3], [anbar].[prc_s2], [anbar].[prc_s3], [anbar].[Tmpt], [anbar].[Tmax], [anbar].[Tmin], [anbar].[Kharid], [anbar].[Forush], [anbar].[Fo_dar], [anbar].[Prc_p], [anbar].[Tdad2], 100719.0 0.83360404 0.0554347 196 1.7780775 [anbar].[Cod_k], [anbar].[Cod_v], [anbar].[Prc_a], [anbar].[Tdad1], [anbar].[Tdad3], [anbar].[prc_s2], [anbar].[prc_s3], [anbar].[Tmpt], [anbar].[Tmax], [anbar].[Tmin], [anbar].[Kharid], [anbar].[Forush], [anbar].[Fo_dar], [anbar].[Prc_p], [anbar].[Tdad2], NULL PLAN_ROW 0 1.0
100719 100719 | |--Clustered Index Seek(OBJECT:([honar86].[dbo].[p_ganbar].[PK_p_ganbar]), SEEK:([p_ganbar].[rdf]=[anbar].[rdfAnbar]) ORDERED FORWARD) 2 12 4 Clustered Index Seek Clustered Index Seek OBJECT:([honar86].[dbo].[p_ganbar].[PK_p_ganbar]), SEEK:([p_ganbar].[rdf]=[anbar].[rdfAnbar]) ORDERED FORWARD [p_ganbar].[Name] 1.0 6.3284999E-3 7.9603E-5 56 1.0545187E-2 [p_ganbar].[Name] NULL PLAN_ROW 0 51.999454
100719 100719 |--Clustered Index Seek(OBJECT:([honar86].[dbo].[p_vahed].[PK_p_vahed]), SEEK:([p_vahed].[Cod]=[anbar].[Cod_v]) ORDERED FORWARD) 2 13 3 Clustered Index Seek Clustered Index Seek OBJECT:([honar86].[dbo].[p_vahed].[PK_p_vahed]), SEEK:([p_vahed].[Cod]=[anbar].[Cod_v]) ORDERED FORWARD [p_vahed].[disk1] 1.0 6.3284999E-3 7.9603E-5 38 0.0105459 [p_vahed].[disk1] NULL PLAN_ROW 0 51.994286

[AminSobati]

به نظر میرسه این ایندکس بتونه کمک کنه:


create index ix1 on P_Anbar(Cod_k)

اگر تغییری حاصل شد، هم Plan و هم خروجی Profile رو برای هر دو Query مجددا بفرستین تا بشه قدمهای بعدی رو برداشت

[AminSobati]

در ضمن اگر میتونیستین با Management Studio وصل بشین به 2000 و Query رو انجام بدین، امکانش بودن که Graphical Plan رو هم بفرستین. اگر Management Studio دارین این کار رو بکنین

[پویا]

Graphical Plan را چطوری میشه دید
من قسمتی به این اسم پیدا نکردم

[AminSobati]

میتونین CTRL+L رو بزنین و بعد روی Plan راست-کلیک و Save انجام بدین

[hosseinab]

استاد میشه بگید چطوری از نتیجه set statistics profile on استفاده میکنید

[AminSobati]

حسین جان Profile یک دنیا حرف داره که توضیح همه اون از یک پست ساده در Forum ساخته نیست. ولی تیتروار میشه به چند نکته خیلی مهم اشاره کرد:
1) از چه الگوریتمهایی در Join استفاده شده
2) Join و سایر قسمتهای Query در دسترسی به جدول به دنبال چه رکوردهایی بوده اند
3) هر Operator چند بار execute شده
4) مقدارهای تخمین زده شده در قبل از اجرای Query چقدر به مقدارهای واقعی که بعد از Query محاسبه میشن نزدیک بوده. اختلاف زیاد بین این اعداد میتونه احتمال اشتباه Query Optimizer در انتخاب Plan بهینه رو زیاد کنه
5) Operatorهایی که به جدول دسترسی داشته اند، چه فیلدهایی رو نیاز داشتن. این در تشخیص ایندکس مناسب بسیار مهمه