{"id":387,"date":"2008-01-09T13:09:23","date_gmt":"2008-01-09T12:09:23","guid":{"rendered":"http:\/\/soci.hu\/blog\/index.php\/2008\/01\/09\/sql-server-2008-ujdonsagok-11-hierarchyid-adattipus-3\/"},"modified":"2008-01-25T10:24:18","modified_gmt":"2008-01-25T09:24:18","slug":"sql-server-2008-ujdonsagok-11-hierarchyid-adattipus-3","status":"publish","type":"post","link":"https:\/\/soci.hu\/blog\/index.php\/2008\/01\/09\/sql-server-2008-ujdonsagok-11-hierarchyid-adattipus-3\/","title":{"rendered":"SQL Server 2008 \u00fajdons\u00e1gok 12. – HierarchyID adatt\u00edpus 3."},"content":{"rendered":"

L\u00e1ssunk most egy olyan lek\u00e9rdez\u00e9st, amiben nem annyira f\u00e9nyes a HierarcyID.<\/p>\n

Szeretn\u00e9m lek\u00e9rdezni a k\u00f6zvetlen beosztottakat, azaz egy node els\u0151 szint\u0171 gyerekeit, az indirekt ut\u00f3dok nem \u00e9rdekelnek. Ehhez egy \u00faj met\u00f3dust vet\u00fcnk be, a GetAncestort, melynek param\u00e9tere a k\u00edv\u00e1nt szint\u0171 el\u0151d, eset\u00fcnkben 1, mert a k\u00f6zvetlen sz\u00fcl\u0151 \u00e9rdekel:<\/p>\n

\r\ndeclare @manager hierarchyid = (select OrgNode \r\nfrom HumanResources.NewOrg\r\nwhere LoginID = 'adventure-works\\roberto0')\r\n\r\nselect @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 OrgNode.GetAncestor(1) = @manager\r\n<\/pre>\n
Kimenet:<\/p>\n
\r\n------------------------------\r\n\/2\/1\/\r\n\r\nOrdPath                        EmployeeID  LoginID                        ManagerID   Title\r\n------------------------------ ----------- ------------------------------ ----------- ------------------------------\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\/5\/                        263         adventure-works\\ovidiu0        3           Senior 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
Szemre j\u00f3nak n\u00e9z ki a kimenet, csak az els\u0151 szint\u0171 ut\u00f3dok j\u00f6ttek le. L\u00e1ssuk a v\u00e9grehajt\u00e1si tervet:<\/p>\n
K\u00f6zvetlen ut\u00f3dok lek\u00e9rdez\u00e9s\u00e9nek v\u00e9grehajt\u00e1si terve<\/a><\/p>\n
L\u00e1that\u00f3, hogy a HierarchyID alapj\u00e1n m\u00e1r nem k\u00e9pes csak index seek-kel lev\u00e1logatni a tartalmat, mert azzal csak az \u00f6sszes ut\u00f3dot tudja megsz\u0171rni, nek\u00fcnk meg csak a k\u00f6zvetlen ut\u00f3dok kellenek. Ez\u00e9rt van ott a Filter oper\u00e1tor, \u0151 az \u00f6sszes ut\u00f3db\u00f3l lev\u00e1logatja a k\u00e9rt szinten lev\u0151ket.<\/p>\n
Hogy ez mennyire nem hat\u00e9kony \u00fagy buktathat\u00f3 le, ha keres\u00fcnk egy olyan embert, akinek nagyon sok idirekt beosztottja van, de csak kev\u00e9s k\u00f6zvetlen. Nyilv\u00e1n a fa tetej\u00e9n \u00fclnek ezek, n\u00e9zz\u00fcnk csak egy kis statisztik\u00e1t:<\/p>\n
\r\nselect LoginID, OrgNode.ToString() as Path,\r\n(select COUNT(*) c from HumanResources.NewOrg i\r\nwhere o.OrgNode.IsDescendant(i.OrgNode) = 1) \r\nas TotalNumberOfDescedants\r\nfrom HumanResources.NewOrg o\r\norder by TotalNumberOfDescedants desc\r\n<\/pre>\n
Futtatva:<\/p>\n
\r\nLoginID                        Path                           TotalNumberOfDescedants\r\n------------------------------ ------------------------------ -----------------------\r\nadventure-works\\ken0           \/                              290\r\nadventure-works\\james1         \/5\/                            209\r\nadventure-works\\peter0         \/5\/1\/                          185\r\nadventure-works\\laura1         \/4\/                            29\r\nadventure-works\\brian3         \/6\/                            18\r\nadventure-works\\jack0          \/5\/1\/19\/                       14\r\nadventure-works\\terri0         \/2\/                            14\r\n...\r\n<\/pre>\n
Ken0 a nagykutya, futtassuk le vele a kiindul\u00f3 lek\u00e9rdez\u00e9st, \u00e9s n\u00e9zz\u00fck meg a t\u00e9nyleges<\/em> (nem j\u00f3solt) v\u00e9grehajt\u00e1si tervet:<\/p>\n
K\u00f6zvetlen ut\u00f3dok lek\u00e9rdez\u00e9s\u00e9nek v\u00e9grehajt\u00e1si terve nagysz\u00e1m\u00fa indirekt ut\u00f3d eset\u00e9n<\/a><\/p>\n
L\u00e1that\u00f3, hogy vastag ny\u00edl j\u00f6n ki az index seekb\u0151l, 290 sort v\u00e1logat le az index, ami nem meglep\u0151 az el\u0151z\u0151 t\u00e1bl\u00e1zat alapj\u00e1n. A Filter ebb\u0151l csak 6-ot tart meg. Tulajdonk\u00e9ppen b\u00e1r index seek az oper\u00e1tor, a teljes t\u00e1bl\u00e1t v\u00e9gignyalta (table scan), ennek megfelel\u0151en az IO k\u00f6lts\u00e9ge 9 lapolvas\u00e1s, ekkora a t\u00e1bla (ha prec\u00edz akarok lenni, a t\u00e1bla 7 lapb\u00f3l \u00e1ll, plusz 2 IO m\u00e1sra megy el).<\/p>\n
Vess\u00fck ezt \u00f6ssze a rel\u00e1ci\u00f3s lek\u00e9rdez\u00e9ssel, ami trivi\u00e1lis lesz:<\/p>\n
\r\ndeclare @ManagerID int = (\r\nselect EmployeeID from HumanResources.NewOrg\r\nwhere LoginID = 'adventure-works\\ken0')\r\n\r\nselect * from HumanResources.NewOrg\r\nwhere ManagerID = @ManagerID\r\n<\/pre>\n
Ez puszt\u00e1n 2 lapolvas\u00e1ssal j\u00e1r (csak a 2. select, az els\u0151t a m\u00e9r\u00e9sekn\u00e9l nem veszem figyelembe), j\u00f3, \u00e9p\u00edt az el\u0151z\u0151 r\u00e9szben<\/a> fel\u00e9p\u00edtett cover indexre. A v\u00e9grehajt\u00e1si terv puszt\u00e1n egy index seekb\u0151l \u00e1ll, az\u00e9rt ilyen piszok hat\u00e9kony.<\/p>\n
\u00d6sszegezve, direkt gyermekek lek\u00e9rdez\u00e9se eset\u00e9n a rel\u00e1ci\u00f3s megold\u00e1s \u00e1ltal\u00e1ban gyorsabb<\/strong>, mint a HierarchyID alap\u00fa. Persze, az\u00e9rt denormaliz\u00e1l\u00e1ssal lehet m\u00e9g itt alak\u00edtani.<\/p>\n
A HierarchyID indexe a k\u00f6vetkez\u0151 sorrendben rendezi be az adatokat (BOL-b\u00f3l lopva):<\/p>\n
M\u00e9lys\u00e9gi bej\u00e1r\u00e1s alapj\u00e1n elk\u00e9sz\u00edtett index (ez az alap a HieararchyID-n)<\/a><\/p>\n
Ami nek\u00fcnk kellene a direkt gyerekek hat\u00e9kony sz\u0171r\u00e9s\u00e9hez, az a sz\u00e9less\u00e9gi bej\u00e1r\u00e1s alapj\u00e1n rendezett index:<\/p>\n
Szintenk\u00e9nti bej\u00e1r\u00e1s alapj\u00e1n elk\u00e9sz\u00edtett index (sz\u00e1m\u00edtott, idexelt oszloppal k\u00e9pezhet\u0151)<\/a><\/p>\n
K\u00e9sz\u00edts\u00fcnk ilyet! Ehhez fel kell venn\u00fcnk a t\u00e1bl\u00e1ba egy \u00faj, sz\u00e1m\u00edtott oszlopot, ami a node-ok szintj\u00e9t sz\u00e1molja ki (GetLevel met\u00f3dus):<\/p>\n
\r\nalter table HumanResources.NewOrg\r\nadd OrgLevel as OrgNode.GetLevel()\r\n<\/pre>\n
Csak, hogy l\u00e1ssuk, hogy is n\u00e9z ez ki:<\/p>\n
\r\nselect OrgNode.ToString(), OrgLevel, \r\n* from HumanResources.NewOrg\r\norder by OrgNode\r\n<\/pre>\n
\r\nPath                           OrgLevel EmployeeId  ManagerID\r\n------------------------------ -------- ----------- -----------\r\n\/                              0        109         NULL\r\n\/1\/                            1        6           109\r\n\/1\/1\/                          2        2           6\r\n\/1\/2\/                          2        46          6\r\n\/1\/3\/                          2        106         6\r\n\/1\/4\/                          2        119         6\r\n\/1\/5\/                          2        203         6\r\n\/1\/6\/                          2        269         6\r\n\/1\/7\/                          2        271         6\r\n\/1\/8\/                          2        272         6\r\n\/2\/                            1        12          109\r\n\/2\/1\/                          2        3           12\r\n\/2\/1\/1\/                        3        4           3\r\n\/2\/1\/2\/                        3        9           3\r\n\/2\/1\/3\/                        3        11          3\r\n\/2\/1\/4\/                        3        158         3\r\n\/2\/1\/4\/1\/                      4        79          158\r\n<\/pre>\n
No, most j\u00f6n az index a sz\u00e1m\u00edtott oszlopra:<\/p>\n
\r\ncreate nonclustered index IDX_Org_Breadth_First \r\nON HumanResources.NewOrg(OrgLevel, OrgNode) \r\ninclude (EmployeeID, LoginID, ManagerID, Title);\r\n<\/pre>\n
Megint egy bazi nagy index, nem biztos, hogy kell ennyi oszlop az include r\u00e9szbe, de \u00edgy lefedt\u00fck a *-os lek\u00e9rdez\u00e9seket is.<\/p>\n
A kiindul\u00f3 lek\u00e9rdez\u00e9s (ken0-val) v\u00e9grehajt\u00e1si terve alaposan megv\u00e1ltozik:<\/p>\n
K\u00f6zvetlen ut\u00f3dok lek\u00e9rdez\u00e9s\u00e9nek v\u00e9grehajt\u00e1si terve nagysz\u00e1m\u00fa indirekt ut\u00f3d eset\u00e9n, breadth-first indexxel megt\u00e1mogatva<\/a><\/p>\n
\u00c1llat, mi? Sima index seek lett, r\u00e1harapott az \u00fajdons\u00fclt index\u00fcnkre! Fel van arra k\u00e9sz\u00edtve az optimizer, hogy a GetAncestor-t hat\u00e9konyan tudja v\u00e9grehajtani, ha meg van t\u00e1mogatva egy j\u00f3 kis breadth-first index-szel.
\nAm\u00fagy 2 lapolvas\u00e1sb\u00f3l \u00e1ll \u00edgy a lek\u00e9rdez\u00e9s, az eredeti 8 helyett. Mondanom sem kell, a po\u00e9n az, hogy ha a t\u00e1bla mondjuk 2%-a j\u00f6n le a lek\u00e9rdez\u00e9s hat\u00e1s\u00e1ra, akkor ez az optimaliz\u00e1lt verzi\u00f3 csak a t\u00e1bla \u00f6tvened\u00e9t j\u00e1rja be, m\u00edg az eredeti az eg\u00e9szet! Nagy f\u00e1k eset\u00e9n ez brut\u00e1lis k\u00fcl\u00f6nbs\u00e9get jelent, nem csak n\u00e9gyszereset, mint a p\u00e9ld\u00e1mban.<\/p>\n
A t\u00e9m\u00e1r\u00f3l b\u0151vebben a BOL-ban lehet olvasni<\/a>.
\nEbben kielemzik a HierarchyID vs. rekurz\u00edv vs. xml lehet\u0151s\u00e9geket, ezt egyel\u0151re nem r\u00e9szletezem tov\u00e1bb, annyi sok m\u00e1s \u00fajdons\u00e1gr\u00f3l akarok m\u00e9g \u00edrni.<\/p>\n
A k\u00f6vetkez\u0151 r\u00e9szben m\u00e9g egyszer j\u00e1tszunk a HierarchyID-vel, nem csak lek\u00e9rdezz\u00fck, de m\u00f3dos\u00edtjuk is a f\u00e1t.<\/p>\n","protected":false},"excerpt":{"rendered":"
L\u00e1ssunk most egy olyan lek\u00e9rdez\u00e9st, amiben nem annyira f\u00e9nyes a HierarcyID. Szeretn\u00e9m lek\u00e9rdezni a k\u00f6zvetlen beosztottakat, azaz egy node els\u0151 szint\u0171 gyerekeit,…<\/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-387","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\/387","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=387"}],"version-history":[{"count":0,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/387\/revisions"}],"wp:attachment":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/media?parent=387"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/categories?post=387"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/tags?post=387"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}