گذاشتن index روی view

[niloofar norouzi]

سلام



SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

ALTER view RPT_HOKM_LAST_Peymani as
SELECT level1.DES AS shahr1, level2.DES AS shahr2, level3.des shahr3 , level4.des shahr4,
/* level1.levelnumber AS level1, level2.levelnumber AS level2, level3.levelnumber AS level3, level4.levelnumber AS level4,*/
OstaneTavallod.DES AS OstaneTavallod, ShahreSodor.DES AS
ShahreSodor, OstaneSodor.DES as OstaneSodor , person.idseri ,
PSDEGREE.DES AS BalatarinMadrak, PSFIELD.DES AS ReshteTahsili, PSFIELD.UKFIELD, PSDEGREE.UKDEGREE,
TBPOSITION.GUIDPOSITION, TBPOSTdes.DES AS OnvanePost, TBPOSITION.positionid AS ShomareMosavabNo,PSEMPTYPE.EMPTYPECODE AS
EMPTYPECODE, PSEMPTYPE.EMPTYPEDESC AS EMPTYPEDESC,
PSRASTEH.UKRASTEH, PSRASTEH.DES AS Rasteh, PSRESHTEH.DES AS ReshteKari, PSHOKM.TABAGE,tbposition.noepost noepost ,tbposition.positionid postno,
PSGROUP.SZGROUP AS Goorooh, PSHOKM.YEARWORKED, PSHOKM.MONTHWORKED, TBHIERARCHY.DES AS VahedSazmani,
TBHIERARCHY.GUIDHIERARCHY, PSGROUP.UKGROUP, PSRESHTEH.UKRESHTEH, PSMARRSTAT.MARRSTATDESC,pswdaraje.wdaraje ,
PSMARRSTAT.MARRSTATCODE, PSHOKM.CHILDNO, PSHOKM.DARSADSAL,PSHOKM.zaribbasij, PSMARRSTAT.UKMARRSTATUS,
PSHOKMTYPE.UKHOKMTYPE, PSHOKMTYPE.HOKMTYPE, PSHOKMTYPE.HOKMTYPEDESC, PSHOKM.HOKMDESC AS SharheHokm,pswdaraje.wdarajecode
,
PSHOKM.EJRADATE, PSHOKM.SodorDATE as startdate, PSHOKM.HOKMNO, PSHOKM.UKMASOOL, Person.UKPERSNO, Person.PERSNO,
Person.persNO as empno, Person.PNAME, Person.PFAMILY, Person.PFATHER, Person.PIDNO, Person.BIRTHDATE, --Masoul.PNAME AS MasoulName,
/*Masoul.PFAMILY AS MasoulFamili, HIERARCHYMasoul.DES AS Masoulhier, POStdesmasoul.DES AS MasoulPost, PSHOKM.UKHOKM,*/ dbo.GetMazayaByCode( PSHOKM.UKHOKM,
101) AS C101, dbo.GetMazayaByCode( PSHOKM.UKHOKM, 102) AS C102, dbo.GetMazayaByCode( PSHOKM.UKHOKM, 103) AS C103,
dbo.GetMazayaByCode( PSHOKM.UKHOKM, 104) AS C104, dbo.GetMazayaByCode( PSHOKM.UKHOKM, 105) AS C105, dbo.GetMazayaByCode( PSHOKM.UKHOKM, 111) AS C111,
dbo.GetMazayaByCode( PSHOKM.UKHOKM, 113) AS C113, dbo.GetMazayaByCode( PSHOKM.UKHOKM, 128) AS C128, dbo.GetMazayaByCode( PSHOKM.UKHOKM, 132) AS C132,
dbo.GetMazayaByCode( PSHOKM.UKHOKM, 127) AS C127, dbo.GetMazayaByCode( PSHOKM.UKHOKM, 215) AS C215, isnull(dbo.GetMazayaByCode( PSHOKM.UKHOKM, 106) ,0) AS C106,
dbo.GetMazayaByCode( PSHOKM.UKHOKM, 116) AS C116, dbo.GetMazayaByCode( PSHOKM.UKHOKM, 114) AS C114, isnull(dbo.GetMazayaByCode( PSHOKM.UKHOKM, 171) ,0) AS C171,
dbo.GetMazayaByCode( PSHOKM.UKHOKM, 115) AS C115, dbo.GetMazayaByCode( PSHOKM.UKHOKM, 124) AS C124, isnull(dbo.GetMazayaByCode( PSHOKM.UKHOKM, 122) ,0) AS C122,
dbo.GetMazayaByCode( PSHOKM.UKHOKM, 123) AS C123, dbo.GetMazayaByCode( PSHOKM.UKHOKM, 144) AS C144, isnull(dbo.GetMazayaByCode( PSHOKM.UKHOKM, 170) ,0) AS C170,
dbo.GetMazayaByCode( PSHOKM.UKHOKM, 118) AS C118, dbo.GetMazayaByCode( PSHOKM.UKHOKM, 121) AS C121,isnull(dbo.GetMazayaByCode( PSHOKM.UKHOKM, 110) ,0) AS C110,
isnull(dbo.GetMazayaByCode( PSHOKM.UKHOKM, 147) ,0) AS C147, isnull(dbo.GetMazayaByCode( PSHOKM.UKHOKM, 112) ,0) AS C112, isnull(dbo.GetMazayaByCode( PSHOKM.UKHOKM, 400) ,0) AS C400, isnull(dbo.GetMazayaByCode( PSHOKM.UKHOKM, 146) ,0) AS C146,
dbo.GetMazayaByCode( PSHOKM.UKHOKM, 122) AS codeEzafi, dbo.GetMablagheEzafeMazaya( PSHOKM.UKHOKM,
dbo.GetCodeEzafeMazaya( PSHOKM.UKHOKM)) AS mablagheEzafi, dbo.GetMazayaByCode( PSHOKM.UKHOKM,
dbo.GetCodeEzafeMazaya( PSHOKM.UKHOKM)) AS SharheEzafiEzafi , pshokm.gorohfogh as gr ,


/* (isnull(dbo.GetMazayaByCode( PSHOKM.UKHOKM,101),0) + isnull(dbo.GetMazayaByCode( PSHOKM.UKHOKM, 102),0)+isnull( dbo.GetMazayaByCode( PSHOKM.UKHOKM, 103),0)+
-- dbo.GetMazayaByCode( PSHOKM.UKHOKM, 105) +
isnull(dbo.GetMazayaByCode( PSHOKM.UKHOKM, 104),0)+ isnull(dbo.GetMazayaByCode( PSHOKM.UKHOKM, 111) ,0)+ isnull(dbo.GetMazayaByCode( PSHOKM.UKHOKM, 110) ,0)+
isnull( dbo.GetMazayaByCode( PSHOKM.UKHOKM, 113) ,0)+ isnull(dbo.GetMazayaByCode( PSHOKM.UKHOKM, 128),0) +-- isnull(dbo.GetMazayaByCode( PSHOKM.UKHOKM, 132),0) +
isnull(dbo.GetMazayaByCode( PSHOKM.UKHOKM, 127),0) + isnull(dbo.GetMazayaByCode( PSHOKM.UKHOKM, 215),0)+
isnull(dbo.GetMazayaByCode( PSHOKM.UKHOKM, 116),0) + isnull(dbo.GetMazayaByCode( PSHOKM.UKHOKM, 114),0) +
isnull(dbo.GetMazayaByCode( PSHOKM.UKHOKM, 115),0) + isnull(dbo.GetMazayaByCode( PSHOKM.UKHOKM, 124) ,0)+ isnull(dbo.GetMazayaByCode( PSHOKM.UKHOKM, 145) ,0)+
isnull(dbo.GetMazayaByCode( PSHOKM.UKHOKM, 123) ,0)+isnull( dbo.GetMazayaByCode( PSHOKM.UKHOKM, 144),0) +
isnull(dbo.GetMazayaByCode( PSHOKM.UKHOKM, 118),0) +isnull( dbo.GetMazayaByCode( PSHOKM.UKHOKM, 121),0) + isnull(dbo.GetMazayaByCode( PSHOKM.UKHOKM, 171) ,0)--+

-- isnull(dbo.GetMazayaByCode( PSHOKM.UKHOKM, 122) ,0) --+isnull(dbo.GetMablagheEzafeMazaya( PSHOKM.UKHOKM,
--dbo.GetCodeEzafeMazaya( PSHOKM.UKHOKM)) ,0)+ isnull(dbo.GetMazayaByCode( PSHOKM.UKHOKM,
-- dbo.GetCodeEzafeMazaya( PSHOKM.UKHOKM)) ,0)
) AS mabkolmazahokm,*/( tbhierarchy.sectionno + ' ' + tbhierarchy.des) sectionno, pssandogh.des ,
psisartype.isardesc as isardesc /*, organ.organdesc organdesc , beorgan.organdesc beorgan*/,pssemat.sematcode as sematcode,pssemat.sematdesc as sematdesc
FROM -- PSPERSONEL Masoul inner JOIN
PSHOKM inner JOIN --HOKMSEQ = GETHOKMSEQ(masoul.UKPERSNO) inner JOIN
PSPERSONEL Person ON PSHOKM.UKPERSNO = Person.UKPERSNO left outer JOIN
PSDEGREE ON PSHOKM.UKLASTDEGREE = PSDEGREE.UKDEGREE left outer JOIN
PSFIELD ON PSHOKM.UKFIELD = PSFIELD.UKFIELD left outer JOIN
TBPOSITION ON PSHOKM.UKPOSITION = TBPOSITION.GUIDPOSITION left outer JOIN
TBPOSTdes ON tbposition.guidPOSTdes = TBPOSTdes.GUIDPOSTdes left outer JOIN
pssemat ON tbposition.guidsemat= pssemat.uksemat left outer JOIN
PSRESHTEH ON tbposition.guidshoghl = PSRESHTEH.UKRESHTEH left outer JOIN
PSRASTEH ON PSHOKM.UKRASTEH = PSRASTEH.UKRASTEH left outer JOIN
PSGROUP ON PSHOKM.UKGROUP = PSGROUP.UKGROUP LEFT OUTER JOIN
TBHIERARCHY ON tbposition.guidHIERARCHY = TBHIERARCHY.GUIDHIERARCHY left outer JOIN
PSMARRSTAT ON PSHOKM.UKMARRSTAT = PSMARRSTAT.UKMARRSTATUS left outer JOIN
PSwdaraje ON person.UKwdaraje = PSwdaraje.UKwdaraje left outer JOIN
PSHOKMTYPE ON PSHOKM.UKHOKMTYPE = PSHOKMTYPE.UKHOKMTYPE left outer jOiN
PSsandogh ON person.uksandogh = PSsandogh.UKsandogh left outer jOiN
-- Masoul.UKPERSNO = PSHOKM.UKMASOOL LEFT OUTER JOIN
-- TBposition POSmasoul ON Masoul.UKposition = POSmasoul.GUIDposition LEFT OUTER JOIN
-- TBHIERARCHY HIERARCHYMasoul ON posmasoul.guidHIERARCHY = HIERARCHYMasoul.GUIDHIERARCHY LEFT OUTER JOIN

-- TBpostdes POStdesmasoul ON POSmasoul.guidpostdes = POStdesmasoul.GUIDpostdes LEFT OUTER JOIN
TBGEOGRAPHICAL ShahreSodor ON Person.UKCITY = ShahreSodor.GUIDGEOGRAPHICAL LEFT OUTER JOIN
TBGEOGRAPHICAL OstaneSodor ON Person.UKSTATE = OstaneSodor.GUIDGEOGRAPHICAL LEFT OUTER JOIN
TBGEOGRAPHICAL OstaneTavallod ON Person.UKBIRTHcity = OstaneTavallod.GUIDGEOGRAPHICAL LEFT OUTER JOIN
TBGEOGRAPHICAL level1 ON tbposition.guidgeographical = level1.GUIDGEOGRAPHICAL left outer join
TBGEOGRAPHICAL level2 ON level1.GUIDparent = level2.guidgeographical left outer join
tbgeographical level3 on level2.guidparent = level3.guidgeographical left outer join
tbgeographical level4 on level3.guidparent = level4.guidgeographical --left outer join
-- psorgan organ on person.ukorgan = organ.ukorgan left outer join
-- psorgan beorgan on person.ukbeorgan = beorgan.ukorgan
left outer join psemptype on psemptype.ukemptype=PSHOKM.ukemptype
left join PSISARTYPE ON person.UKISARGARI = PSISARTYPE.UKISAR
where (Person.baztype=1) and (pshokm.hokmseq = dbo.GETHOKMSEQ(person.UKPERSNO)) -- with READ ONLY







GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO



چه index روی این view بزارم که بهینه شود
با تشکر

[AminSobati]

سلام،
با توجه به اینکه OUTER JOIN استفاده شده، روی View نمیتونین ایندکس بگذارین. ولی مطمئن هستین که این تنها راهه؟ آیا با ایندکس گذاری روی جداول نمیتونین وضعیت رو بهینه کنین؟

[niloofar norouzi]

سلام
با ایندکس گذاری روی جداول زمان اجرا از 2 دقیقه به یک دقیقه رسید
لطف می کنید راهنمایی کنید

[AminSobati]

سلام،
متاسفانه ایندکس گذاری مثل نسخه پیچیدن نیست! ساعتها وقت به همراه Execution Plan کامل نیاز داره

[niloofar norouzi]

ممنونم که راهنمایی می فرمایید
من چطور می تونم Execution Plan را برای شما بفرستم ؟
مگه index روی فیلدی که در where و group by قرار داره ساخته نمی شه؟

[AminSobati]

در نسخه 2005 کلید CTRL+M رو بزنید و بعد Query رو اجرا کنین. در خاتمه Query یک Tab جدید کنار کنار Results و Messages ظاهر میشه. اون رو باز کنین. وقتی روی صحفه Right-Click کنین گزینه Save دارین.

[niloofar norouzi]

سلام
اینم از Execution Plan
ممنون از اینکه راهنمایی می فرمایید

[AminSobati]

ساختار Joinها نشون میده این Query مستعد بهینه شدن هست ولی همونطور که اشاره کردم زمان میبره و از طرفی اطلاعات خود جداول نیازه تا تست صورت بگیره. بعضی از Hash Joinها احتمالا به Merge Join یا Nested Loop قابل تبدیل هستند ولی نمیشه تخمین زد چند درصد بهتر میشه. ترفندهای دیگه هم به ذهن میرسه مثلا اینکه Inner Join بین سه جدول PSPERSONEL و PSEMPTYPE و PSHOKM تبدیل به View بشه و ایندکس بخوره و بعد این View با بقیه Query بصورت Join پیوند بخوره.