Ez egy igen durva optimaliz\u00e1l\u00e1si lehet\u0151s\u00e9g azoknak (is), akik nagy t\u00e1bl\u00e1kkal dolgoznak.
\nNorm\u00e1l esetben egy indexfa a teljes t\u00e1bla adatainak egy r\u00e9szhalmaz\u00e1t, a kulcsk\u00e9nt kijel\u00f6lt oszlopok tartalm\u00e1t t\u00e1rolj\u00e1k rendezetten egy f\u00e1ban, \u00edgy nagyon gyorsan lehet a kulcs(ok) alapj\u00e1n keresni. A hangs\u00faly a teljes t\u00e1bl\u00e1n van. Mi van, ha mondjuk t\u00f6bb \u00e9vnyi adat van egy t\u00e1bl\u00e1ban, de mi sokszor (de nem mindig) csak az ut\u00f3bbi egy h\u00f3nappal dolgozunk? Egyr\u00e9szt lehet particion\u00e1lni, ebbe most nem megyek bele, b\u00e1r fontos t\u00e9ma.
\nHa NC indexet rakunk a d\u00e1tum oszlopra \u00e9s kev\u00e9s sort sz\u0171r\u00fcnk le van es\u00e9ly\u00fcnk a hat\u00e9kony sz\u0171r\u00e9sre, de ha sok adatunk van j\u00f3 nagy lesz az indexfa, feleslegesen, hisz mint \u00e1ll\u00edtottam, a lek\u00e9rdez\u00e9seink tipikusan NEM \u00e9rintik a r\u00e9gi adatokat, azaz az indexfa csak kis r\u00e9sz\u00e9t haszn\u00e1ljuk ki. R\u00e1ad\u00e1sul sokszor \u00fagy hozunk l\u00e9tre NC indexet, hogy j\u00f3 sok oszlopot pakolunk bele, ezzel lefedve bizonyos lek\u00e9rdez\u00e9seket, amelyeknek nagyon gyorsnak kell lenni. Azaz az index minden oszlopot tartalmaz, amire a lek\u00e9rdez\u00e9s hivatkozik, b\u00e1rmely r\u00e9sz\u00e9ben, ekkor csak az indexf\u00e1b\u00f3l is v\u00e9grehajthat\u00f3 a lek\u00e9rdez\u00e9s, nem kell az adatlapokhoz ny\u00falni a szervernek. Ezt h\u00edvj\u00e1k cover indexnek \u00e9s querynek. A cover indexek sokszor piszok nagyok, hisz lehet, hogy tucatnyi oszlopot is tartalmaznak. 2005-ben megjelent az included columns az indexben, ezzel a nem kulcs adatokat, azaz amelyekre nem sz\u0171r\u00fcnk az index t\u00e1mogat\u00e1s\u00e1val csak a lefedetts\u00e9g miatt kell csak lev\u00e9lszinten rakja be a szerver az indexbe, k\u00f6zbens\u0151 szinten nem. Ennek \u00f6r\u00fclt\u00fcnk, egyfajta mini clustered indexet hozhattunk l\u00e9tre az oszlopok egy r\u00e9szhalmaz\u00e1b\u00f3l. Piszok gyors lek\u00e9rdez\u00e9seket lehetett \u00edgy \u00f6sszehozni, de marha nagy lett az indexfa.<\/p>\n
J\u00f6hetnek a filtered indexek.<\/p>\n
Filtered index eset\u00e9n az index l\u00e9trehoz\u00e1skor meg lehet adni egy where felt\u00e9telt, \u00edgy az indexfa csak az \u00e1ltalunk fontosnak v\u00e9lt adatokat tartalmazza. Az el\u0151bbi p\u00e9ld\u00e1ban mondjuk csak az utols\u00f3 h\u00f3nap adatait. Vagy csak egy adott megye adatait. Vagy a nem NULL adatokat. \u00cdgy az index nem tud megt\u00e1mogatni minden lek\u00e9rdez\u00e9st, hisz nincs benne minden adat, viszont kisebb \u00e9s gyorsabb lehet, mint egy nem sz\u0171rt index. R\u00e1ad\u00e1sul a statisztik\u00e1k is az adatok r\u00e9szhalmaz\u00e1ra vonatkozik, \u00edgy pontosabbak, mint a teljes t\u00e1bl\u00e1sak.
\nAz index karbantart\u00e1sa is kisebb k\u00f6lts\u00e9g\u0171 adatm\u00f3dos\u00edt\u00e1sok eset\u00e9n, hisz lehet, hogy az adott sor nincs is az indexben, illetve kisebb az indexfa is, \u00edgy a statisztika friss\u00edt\u00e9se is kisebb k\u00f6lts\u00e9g\u0171.<\/p>\n
L\u00e1ssunk egy p\u00e9ld\u00e1t a BOL-b\u00f3l. Kiindul\u00e1si t\u00e1bl\u00e1nk az AdventureWorks BillOfMaterials t\u00e1bl\u00e1ja. Nem nagy, 4e soros, ez\u00e9rt nem lesznek l\u00e1tv\u00e1nyosak a gyorsul\u00e1sok, de legal\u00e1bb k\u00e9zn\u00e9l van. Ebben az EndDate oszlop NULL-os, vannak is benne NULL \u00e9rt\u00e9kek:<\/p>\n
\r\nselect \r\nsum(case when EndDate is not null then 1 else 0 end) NotNullos,\r\nsum(case when EndDate is null then 1 else 0 end) Nullos\r\nfrom Production.BillOfMaterials\r\n<\/pre>\n\r\nNotNullos Nullos\r\n----------- -----------\r\n199 2480\r\n<\/pre>\nSok gyakorlati lek\u00e9rdez\u00e9st l\u00e1ttam m\u00e1r, amely \u00fagy n\u00e9z ki, hogy a where felt\u00e9telben van valamilyen sz\u0171r\u00e9si felt\u00e9tel AND valamilyen oszlop IS NOT NULL. No, ezeket j\u00f3l meg tudja t\u00e1mogatni egy filtered index, ami csak a nem nullos adatokat indexeli.<\/p>\n
N\u00e9zz\u00fck ezt a lek\u00e9rdez\u00e9st a BOL-b\u00f3l:<\/p>\n
\r\nselect ProductAssemblyID, ComponentID, StartDate \r\nfrom Production.BillOfMaterials\r\nwhere EndDate is not null;\r\n<\/pre>\nIndex n\u00e9lk\u00fcl. Cost: 20ms, IO: 22. Nem sok, deh\u00e1t kicsi a t\u00e1bla.<\/p>\n
A BOL ezt a filtered indexet rakja r\u00e1:<\/p>\n
\r\ncreate nonclustered index idx_FIBillOfMaterialsWithEndDate\r\non Production.BillOfMaterials (ComponentID, StartDate)\r\nwhere EndDate IS NOT NULL;\r\n<\/pre>\nEz csak a not nullos EndDate \u00e9rt\u00e9k\u0171 sorokat tartalmazza, azaz csak 199 sorb\u00f3l \u00e1ll. Picike. De nem az EndDate szerint van rendezve, hanem ComponentID, StartDate szerint. Mit jelent ez az el\u0151bbi lek\u00e9rdez\u00e9sre n\u00e9zve?
\nFelhaszn\u00e1lja az indexet a szerver, Index Scan<\/strong> lesz (nem seek), Cost: 3.5ms, IO: 2. N\u00e9zz\u00fck csak. Mi\u00e9rt haszn\u00e1lja egy\u00e1ltal\u00e1n az indexet, hisz nincs is EndDate szerint rendezve?
\nEz egy olyan lek\u00e9rdez\u00e9s, amit az index lefed, mert minden oszlopa benne van az indexben. ProductAssemblyID, ComponentID, StartDate, EndDate, ezekre hivatkozik a select. ComponentID, StartDate trivi\u00e1lisan benne van az indexben, annak a kulcsa. Az EndDate nincs benne, de a sz\u0171r\u00e9si felt\u00e9tel pont azonos az index\u00e9vel, \u00edgy tudjuk, hogy az index pont azokat a sorokat tartalmazza, amelyek a felt\u00e9telt kiel\u00e9g\u00edtik. \u00c9s mi a helyzet a ProductAssemblyID-vel? Nos, mint tudjuk (tudjuk?) minden NC indexben benne vannak a CLU index oszlopai is, hisz az NC index lev\u00e9lszinten ilyenkor nem Row Locatorokat tartalmaz, hanem CLU kulcsokat, azaz az NC index haszn\u00e1lata ut\u00e1n m\u00e9g a CLU indexen is v\u00e9gigmegy a szerver. Azaz az el\u0151bbi index\u00fcnk lev\u00e9lszinten a k\u00f6vetkez\u0151ket tartalmazza: ComponentID, StartDate, ProductAssemblyID, ComponentID, StartDate, mert az ut\u00f3bbi 3 alkotja a Clu indexet. Hogy mi, k\u00e9t oszlop is k\u00e9tszer van az indexben? Igen, ez\u00e9rt nem szerencs\u00e9s a p\u00e9lda. Csak zab\u00e1ln\u00e1 a helyet. Szerencs\u00e9re a szerver okosabb enn\u00e9l (legal\u00e1bbis a 2008, a DBCC PAGE-dzsel val\u00f3 vizsg\u00e1l\u00f3d\u00e1saim alapj\u00e1n), \u00e9s csak egyszer t\u00e1rolja le a t\u00f6bbsz\u00f6r szerepl\u0151 oszlopokat. (Miut\u00e1n meg\u00edrtam ezt a bejegyz\u00e9st tal\u00e1ltam egy linket<\/a>, ami pont ezt a t\u00e9m\u00e1t taglalja.)
\nNo, a lek\u00e9rdez\u00e9st teh\u00e1t lefedi az index, ez\u00e9rt nem kell az adatsorokhoz ny\u00falni, csak az indexb\u0151l teljes\u00edthet\u0151 a lek\u00e9rdez\u00e9s. Az\u00e9rt Scan \u00e9s nem Seek a m\u0171velet, mert a teljes index tartalm\u00e1t bej\u00e1rj\u00e1k, hisz nincs sz\u0171r\u00e9s az index kulcs adataira. \u00cdgy is hat\u00e9kony a select, mert kicsi az indexfa. Hogy igaz\u00e1n elem\u00e9ben legyen az index, sz\u0171rj\u00fcnk m\u00e1r egy kicsit a kulcs\u00e1ra is:<\/p>\n\r\nselect ProductAssemblyID, ComponentID, StartDate \r\nfrom Production.BillOfMaterials\r\nwhere ComponentID = 324 and EndDate is not null;\r\n<\/pre>\nEkkor haszn\u00e1lja az indexet, seekel. Cover query seek-kel, ez a leggyorsabb index m\u0171velet ami csak l\u00e9tezik a szerverben.<\/p>\n
Nem akarom t\u00falragozni a k\u00e9rd\u00e9st tov\u00e1bb, aki tudja, hogyan kell cover query-t k\u00e9sz\u00edteni NC index-szel, az most k\u00e9sz\u00edthet sokkal kisebb index f\u00e1t is, filtered index seg\u00edts\u00e9g\u00e9vel, ha tudja, hogy a nagyon felgyors\u00edtand\u00f3 lek\u00e9rdez\u00e9s(ek) tudj\u00e1k haszn\u00e1lni a sz\u0171rt, kicsi indexf\u00e1t.
\nNem cover helyzetben is j\u00f3 lehet ez, csak ekkor m\u00e9g el\u0151 kell szedni a sorokat az adatlapokr\u00f3l, amivel n\u0151 ugyan a k\u00f6lts\u00e9g, de m\u00e9g mindig gyors lehet.<\/p>\nAki ismeri az SQL Server 2000-ben bevezetett indexelt n\u00e9zeteket, az most azt mondja, mi ebben az \u00faj? Indexelt n\u00e9zettel ugyanezt meg lehet csin\u00e1lni. Nos, filtered indexben sokkal sz\u0171kebb a sz\u0171r\u00e9si lehet\u0151s\u00e9gek:<\/p>\n
“The filter predicate uses simple comparison logic and cannot reference a computed column, a UDT column, a spatial data type column, or a hierarchyID data type column. Comparisons using NULL literals are not allowed with the comparison operators. Use the IS NULL and IS NOT NULL operators instead.”<\/p>\n
Emiatt gyorsabb lehet, mint az indexed view. Az indexelt view elk\u00e9peszt\u0151en fel tud gyors\u00edtani pl. aggreg\u00e1l\u00f3 m\u0171veleteket haszn\u00e1l\u00f3 lek\u00e9rdez\u00e9seket vagy el\u0151t\u00e1rol join-okat, de finny\u00e1s a k\u00fcl\u00f6nb\u00f6z\u0151 set opci\u00f3kra, emiatt viszont korl\u00e1tozottabban lehet csak haszn\u00e1lni, ha nem stimmelnek lek\u00e9rdez\u00e9skor, akkor nem haszn\u00e1lja az indexet, illetve az alapt\u00e1bl\u00e1k m\u00f3dos\u00edt\u00e1sa sem fog m\u0171k\u00f6dni, hib\u00e1t jelez. Emiatt j\u00f3val kevesebb helyen tudt\u00e1k bevetni az indexelt n\u00e9zeteket, mint ahol be fogj\u00e1k a filtered indexet. Pont a nagyon behat\u00e1rolt sz\u0171r\u00e9sek miatt nincsenek a set opci\u00f3s megk\u00f6t\u00e9sek mint a view-n\u00e1l (a nullos kit\u00e9telek is ez\u00e9rt vannak), ez\u00e9rt k\u00e9nyelmesebben lehet haszn\u00e1lni, noha but\u00e1bb, mint az indexelt n\u00e9zet.<\/p>\n
Pl. ha rendszeresen egyes kateg\u00f3ri\u00e1kra kell sz\u0171rni egy nagy t\u00e1bl\u00e1b\u00f3l, akkor azt j\u00f3l meg lehet majd gyors\u00edtani egy adott kateg\u00f3ri\u00e1kat t\u00e1rol\u00f3 filtered index-szel.<\/p>\n
Szeress\u00fck a filtered indexet, mert az j\u00f3.<\/p>\n","protected":false},"excerpt":{"rendered":"
Ez egy igen durva optimaliz\u00e1l\u00e1si lehet\u0151s\u00e9g azoknak (is), akik nagy t\u00e1bl\u00e1kkal dolgoznak. Norm\u00e1l esetben egy indexfa a teljes t\u00e1bla adatainak egy r\u00e9szhalmaz\u00e1t,…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6,4,30,58],"tags":[],"class_list":["post-372","post","type-post","status-publish","format-standard","hentry","category-adatbazisok","category-szakmai-elet","category-sql-server","category-sql-server-2008"],"_links":{"self":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/372","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=372"}],"version-history":[{"count":0,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/372\/revisions"}],"wp:attachment":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/media?parent=372"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/categories?post=372"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/tags?post=372"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}