حمیدرضاصادقیان
یک شنبه 25 شهریور 1386, 14:09 عصر
سلام دوستان.من 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 می باشه.میخواستم ببینم میشه جور دیگه ایی نوشت که سرعتش افزایش پیدا کنه.
حمیدرضاصادقیان
دوشنبه 26 شهریور 1386, 12:29 عصر
این مربوط به 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
vBulletin® v4.2.5, Copyright ©2000-1403, Jelsoft Enterprises Ltd.