{"id":380,"date":"2008-01-08T10:25:28","date_gmt":"2008-01-08T09:25:28","guid":{"rendered":"http:\/\/soci.hu\/blog\/index.php\/2008\/01\/08\/sql-server-2008-ujdonsagok-10-hierarchyid-adattipus-2\/"},"modified":"2008-01-25T10:23:59","modified_gmt":"2008-01-25T09:23:59","slug":"sql-server-2008-ujdonsagok-10-hierarchyid-adattipus-2","status":"publish","type":"post","link":"https:\/\/soci.hu\/blog\/index.php\/2008\/01\/08\/sql-server-2008-ujdonsagok-10-hierarchyid-adattipus-2\/","title":{"rendered":"SQL Server 2008 \u00fajdons\u00e1gok 11. – HierarchyId adatt\u00edpus 2."},"content":{"rendered":"

Mire j\u00f3 a HierarchyID? Vannak m\u0171veletek, amelyeket gyorsabban lehet v\u00e9grehajtani a seg\u00edts\u00e9g\u00e9vel, mivel a node-ok el\u00e9r\u00e9si \u00fatja van enk\u00f3dolva az idben, \u00edgy a felindexelt id alapj\u00e1n egyes lek\u00e9rdez\u00e9sek hat\u00e9konyak lehetnek.<\/p>\n

N\u00e9zz\u00fck meg pl. hogyan keresn\u00e9nk meg egy adott ember \u00f6sszes direkt vagy indirekt beosztottj\u00e1t? Azaz, az adott node alatti r\u00e9szf\u00e1t szeretn\u00e9nk kiv\u00e1lasztani (az el\u0151z\u0151 r\u00e9sz<\/a> adataira alapozok).<\/p>\n

\r\ndeclare @manager hierarchyid = (select OrgNode \r\nfrom HumanResources.NewOrg\r\nwhere LoginID = 'adventure-works\\terri0')\r\n\r\n--select @manager.ToString()\r\n\r\nselect \r\ncast(OrgNode as varchar(50)) as OrdPath, \r\nEmployeeID, LoginID, ManagerID, Title\r\nfrom HumanResources.NewOrg\r\nwhere @manager.IsDescendant(OrgNode) = 1\r\norder by OrdPath\r\n<\/pre>\n
Kikeress\u00fck terri0 HierarchyID-j\u00e9t, majd az IsDescendant met\u00f3dus seg\u00edts\u00e9g\u00e9vel lesz\u0171rj\u00fck az ut\u00f3dait. Gyerekek, unok\u00e1k, stb. (kicsit bizarr, hogy pont Terri nev\u0171 emberr\u0151l sz\u00f3l a p\u00e9ld\u00e1nk… :). A f\u00fcggv\u00e9ny mag\u00e1t a kiindul\u00f3 node-ot is visszaadja, azaz a DescendantOrSelf n\u00e9v prec\u00edzebb n\u00e9v lenne (persze ez egy CTP verzi\u00f3, ki tudja, hogy lesz m\u00e9g a v\u00e9glegesben).
\nA kimenet:<\/p>\n
\r\nOrdPath                        EmployeeID  LoginID                        ManagerID   Title\r\n------------------------------ ----------- ------------------------------ ----------- ------------------------------\r\n\/2\/                            12          adventure-works\\terri0         109         Vice President of Engineering\r\n\/2\/1\/                          3           adventure-works\\roberto0       12          Engineering Manager\r\n\/2\/1\/1\/                        4           adventure-works\\rob0           3           Senior Tool Designer\r\n\/2\/1\/2\/                        9           adventure-works\\gail0          3           Design Engineer\r\n\/2\/1\/3\/                        11          adventure-works\\jossef0        3           Design Engineer\r\n\/2\/1\/4\/                        158         adventure-works\\dylan0         3           Research and Development Manag\r\n\/2\/1\/4\/1\/                      79          adventure-works\\diane1         158         Research and Development Engin\r\n\/2\/1\/4\/2\/                      114         adventure-works\\gigi0          158         Research and Development Engin\r\n\/2\/1\/4\/3\/                      217         adventure-works\\michael6       158         Research and Development Manag\r\n\/2\/1\/5\/                        263         adventure-works\\ovidiu0        3           Senior Tool Designer\r\n\/2\/1\/5\/1\/                      5           adventure-works\\thierry0       263         Tool Designer\r\n\/2\/1\/5\/2\/                      265         adventure-works\\janice0        263         Tool Designer\r\n\/2\/1\/6\/                        267         adventure-works\\michael8       3           Senior Design Engineer\r\n\/2\/1\/7\/                        270         adventure-works\\sharon0        3           Design Engineer\r\n<\/pre>\n
Az OrdPathb\u00f3l j\u00f3l l\u00e1that\u00f3, de az EmployeeID-k \u00e9s ManagerID-k alapj\u00e1n szemre is kikereshet\u0151, hogy az \u00f6sszes ut\u00f3d szerepel a list\u00e1ban.<\/p>\n
A lek\u00e9rdez\u00e9s k\u00f6lts\u00e9g\u00e9nek elemz\u00e9s\u00e9hez kiveszem az OrdPath oszlopot, \u00e9s a order by-t is, hogy tiszt\u00e1n a sz\u0171r\u00e9s k\u00f6lts\u00e9g\u00e9t l\u00e1ssuk. Illetve rakok egy indexet a LoginID oszlopra, hogy az els\u0151 sor sz\u0171r\u00e9se gyors legyen:<\/p>\n
\r\ncreate nonclustered index IDX_LoginID \r\non HumanResources.NewOrg(LoginID)\r\n<\/pre>\n
A csupasz lek\u00e9rdez\u00e9s:<\/p>\n
\r\ndeclare @manager hierarchyid = (select OrgNode \r\nfrom HumanResources.NewOrg\r\nwhere LoginID = 'adventure-works\\terri0')\r\n\r\nselect \r\nEmployeeID, LoginID, ManagerID, Title\r\nfrom HumanResources.NewOrg\r\nwhere @manager.IsDescendant(OrgNode) = 1\r\n<\/pre>\n
A v\u00e9grehajt\u00e1si terv:<\/p>\n
Sz\u0171r\u00e9s v\u00e9grehajt\u00e1si terve HierarchyID alapj\u00e1n<\/a><\/p>\n
Mint l\u00e1that\u00f3 (kattintani kell a k\u00e9pre) az IsDescedantra fel van k\u00e9sz\u00edtve az optimizer (!), \u00e9s a lek\u00e9rdez\u00e9st a leghat\u00e9konyabb m\u00f3don, index seek-kel hajtja v\u00e9gre (alul, jobb oldalt). N\u00e9zz\u00fck meg a sz\u0171r\u0151felt\u00e9telt is az index oper\u00e1torhoz:<\/p>\n
Seek Keys[1]:
\nStart: [AdventureWorks].[HumanResources].[NewOrg].OrgNode >= Scalar Operator([@manager]),
\nEnd: [AdventureWorks].[HumanResources].[NewOrg].OrgNode <= Scalar Operator([@manager].DescendantLimit())\n[\/source]\n\nL\u00e1that\u00f3, hogy kihaszn\u00e1lja azt, hogy az \u00f6sszes ut\u00f3d egy bag\u00e1zsban van, mivel az OrdPath \u00edgy van rendezve (eml\u00e9kezz\u00fcnk az el\u0151z\u0151 r\u00e9szb\u0151l, a HieararchyID oszlopunkon volt egy clustered index: constraint PK_NewOrg_OrgNode primary key clustered (OrgNode)). A DescendantLimit egy bels\u0151 f\u00fcggv\u00e9ny, ez jelzi, hol v\u00e1ltanak szintet, meddig kell keresni. \u00dcgyes. A lek\u00e9rdez\u00e9s k\u00f6lts\u00e9ge minim\u00e1lis, az els\u0151 \u00e9s a m\u00e1sodik r\u00e9sz \u00f6sszege 6.6 ms (k\u00e9t index seek). Persze, az igaz\u00e1n izgalmas, mekkora ennek a k\u00f6lts\u00e9ge a rekurz\u00edv CTE megold\u00e1shoz k\u00e9pest, ami \u00edgy n\u00e9zne ki:\n\n[source='sql']\nwith Descendants(EmployeeID, LoginID, ManagerID, Title) \nas \n(\nselect EmployeeID, LoginID, ManagerID, Title\nfrom HumanResources.NewOrg\nwhere LoginID = 'adventure-works\\terri0'\n\nunion all\n\nselect\ne.EmployeeID,\ne.LoginID,\ne.ManagerID,\ne.Title\nfrom HumanResources.NewOrg as e\njoin Descendants as d\non e.ManagerID = d.EmployeeID\n)\n\nselect * from Descendants\n[\/source]\n\nLegy\u00fcnk igazs\u00e1gosak, a HieararchyID oszlopon volt egy clustered index, az persze megbik\u00e1zta a lek\u00e9rdez\u00e9st mint \u00e1llat. Szeg\u00e9ny CTE-s megold\u00e1sunk meg join-ol kem\u00e9nyen, kapjon h\u00e1t hozz\u00e1 olyan indexeket, amelyek rendesen megt\u00e1mogatj\u00e1k. Mivel a ManagerID nem t\u00fal szelekt\u00edv (egy adott ManagerID-re a t\u00e1bla jelent\u0151s r\u00e9sze visszaj\u00f6het), ez\u00e9rt annak included column-ot is tartalmaz\u00f3 nc indexet adok, hogy cover query-t tudjon csin\u00e1lni a szerver.\n\n[source='sql']\ncreate unique nonclustered index IDX_EmployeeID\non HumanResources.NewOrg(EmployeeID)\ngo\n\ncreate nonclustered index IDX_ManagerID \non HumanResources.NewOrg(ManagerID) include (EmployeeID, LoginID, Title)\n[\/source]\n\n(Finoms\u00e1g: az OrgNode nincs benne az indexben, mivel az a clustered index kulcsa, \u00edgy mindenk\u00e9ppen benne van minden nc indexben is, \u00edgy k\u00e1r lenne k\u00e9tszer belerakni.)\n\nEzen megold\u00e1sban a kimenet sorrendje m\u00e1s, de az adatok benne ugyanazok, \u00e9s mivel most is csak a sz\u0171z lek\u00e9rdez\u00e9s k\u00f6lts\u00e9g\u00e9re koncentr\u00e1lunk, az order by-t itt is kihagytuk.\n\nA rekurz\u00edv CTE v\u00e9grehajt\u00e1si terve<\/a><\/p>\n
A terv k\u00f6lts\u00e9ge 21 ms. Ezekkel az adatokkal a HierarchyID-s megold\u00e1s 3x gyorsabb. Minimum, ugyanis m\u00edg az els\u0151 megold\u00e1s 5 lapolvas\u00e1st ig\u00e9nyelt, a CTE-s 118-at, \u00e9s m\u00e9g egy temp t\u00e1bl\u00e1t is haszn\u00e1lt! A gyakorlatban ez azt jelenti, hogy terhelt szervern\u00e9l sokkal lassabb lesz, mint a 3-as szorz\u00f3.<\/p>\n
\u00d6sszegezve, ha egy node ut\u00f3djait reprezent\u00e1l\u00f3 r\u00e9szf\u00e1t akarunk lev\u00e1logatni, akkor egy rendesen karbantartott \u00e9s felindexelt HierarchyID oszlop hat\u00e9kony lek\u00e9rdez\u00e9seket tesz lehet\u0151v\u00e9, hat\u00e9konyabbat, mint a hagyom\u00e1nyos rekurz\u00edv megold\u00e1s.<\/p>\n
Z\u00e1r\u00e1sul egy figyelmeztet\u00e9s. NEM minden esetben gyorsabb a HierarchyID a hagyom\u00e1nyos rekurz\u00edv kompoz\u00edci\u00f3val szemben<\/strong>, sz\u00f3val ne halljam vissza, hogy soci azt mondta, dobjuk ki a ManagerID-t, \u00e9s haszn\u00e1ljunk HiearchyID-t. Nem, a k\u00f6vetkez\u0151 r\u00e9szben mutatok olyan esetet, amikor a hagyom\u00e1nyos rel\u00e1ci\u00f3s megold\u00e1s gyorsabb. Nyilv\u00e1n olyan lek\u00e9rdez\u00e9st kell megfogalmazni, ami nem tudja kihaszn\u00e1lni a HiearchyID m\u00e9lys\u00e9gi bej\u00e1r\u00e1s alap\u00fa indexel\u00e9s\u00e9t.<\/p>\n","protected":false},"excerpt":{"rendered":"
Mire j\u00f3 a HierarchyID? Vannak m\u0171veletek, amelyeket gyorsabban lehet v\u00e9grehajtani a seg\u00edts\u00e9g\u00e9vel, mivel a node-ok el\u00e9r\u00e9si \u00fatja van enk\u00f3dolva az idben, \u00edgy…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6,58],"tags":[],"class_list":["post-380","post","type-post","status-publish","format-standard","hentry","category-adatbazisok","category-sql-server-2008"],"_links":{"self":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/380","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/comments?post=380"}],"version-history":[{"count":0,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/380\/revisions"}],"wp:attachment":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/media?parent=380"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/categories?post=380"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/tags?post=380"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}