{"id":1977,"date":"2015-03-05T20:40:02","date_gmt":"2015-03-05T18:40:02","guid":{"rendered":"http:\/\/soci.hu\/blog\/?p=1977"},"modified":"2015-03-05T20:40:02","modified_gmt":"2015-03-05T18:40:02","slug":"forceseek-hint-szeretem","status":"publish","type":"post","link":"https:\/\/soci.hu\/blog\/index.php\/2015\/03\/05\/forceseek-hint-szeretem\/","title":{"rendered":"FORCESEEK hint – szeretem"},"content":{"rendered":"

Mindig im\u00e1dom, ha valami geeks\u00e9get be tudok vetni a gyakorlatban. SQL Server 2012-ben j\u00f6tt be az az \u00fajdons\u00e1g, hogy a FORCESEEK hintnek meg lehet adni egy index oszlopait is.<\/p>\n

A tanfolyamok kedv\u00e9\u00e9rt lehet tal\u00e1lni p\u00e9ld\u00e1kat, aminek seg\u00edt egy hint, de v\u00e9gre tal\u00e1ltam valamit, ami \u00e9l\u0151ben is demonstr\u00e1lja a dolgot.<\/p>\n

Az al\u00e1bbi lek\u00e9rdez\u00e9s 1 perces t\u0151zsdei adatokban keres adathi\u00e1nyt. Az AllTH \u00f6sszeszedi minden napra a nyitvatart\u00e1si id\u0151ket. <\/p>\n

\r\n;with AllTH as\r\n(\r\n\tselect \r\n\tD, \r\n\tdateadd(DAY, 1, D) D1,\r\n\tcast(DATEADD(HOUR, DATEPART(HOUR, OpeningTime), DATEADD(MINUTE, DATEPART(MINUTE, OpeningTime), cast(D as datetime2))) as datetime2) ExpectedOpeningTimeAsDate, \r\n\tcast(DATEADD(HOUR, DATEPART(HOUR, ClosingTime), DATEADD(MINUTE, DATEPART(MINUTE, ClosingTime), cast(D as datetime2))) as datetime2) ExpectedClosingTimeAsDate, \r\n\tOpeningTime, ClosingTime from TradingHoursFlat\r\n\twhere TickerId = @thTickerId\r\n\tand D >= (select min(TradeTime) from Tick where TickerID = @tickerId and tradetime > dateadd(day, -180, getdate()))\r\n\tand D < dateadd(day, -1, GETDATE())\r\n),\r\nFilteredBars as\r\n(\r\n\tselect cast(TradeTime as datetime) TradeTime,\r\n\tt.D, t.OpeningTime, t.ClosingTime\r\n\tfrom AllTH t\r\n\tjoin Tick b\r\n\twith(forceseek (IX_Natural_Key (TickerId, TradeTime)))\r\n\t--on t.D = cast(TradeTime as date) \r\n\ton TradeTime between t.D and t.D1\r\n\t--on TradeTime between t.D and t.D+1\r\n\twhere b.TickerID = @tickerId\r\n\t--and cast(TradeTime as time) between t.OpeningTime and t.ClosingTime\r\n\tand tradetime between t.ExpectedOpeningTimeAsDate and t.ExpectedClosingTimeAsDate\r\n),\r\nT as\r\n(\r\n\tselect a.D, min(TradeTime) ActualOpeningTime, max(TradeTime) ActualClosingTime from FilteredBars b \r\n\tright join AllTH a\r\n\ton a.D = cast(TradeTime as date)\r\n\tgroup by a.D\r\n), U as\r\n(\r\n\tselect a.D, ExpectedOpeningTimeAsDate, ActualOpeningTime, ExpectedClosingTimeAsDate, ActualClosingTime,\r\n\tDATEDIFF(MINUTE, ExpectedOpeningTimeAsDate, ActualOpeningTime) OpeningGap,\r\n\tDATEDIFF(MINUTE, ActualClosingTime, ExpectedClosingTimeAsDate) ClosingGap\r\n\tfrom T\r\n\tright join AllTH a\r\n\ton a.D = cast(t.ActualOpeningTime as date)\r\n)\r\n,V as\r\n(\r\n\tselect * from U\r\n\twhere OpeningGap > @tolerance or ClosingGap > @tolerance\r\n\tor ActualOpeningTime is null or ActualClosingTime is null\r\n)\r\nselect \r\nROW_NUMBER() over(order by D) Id,\r\nExpectedOpeningTimeAsDate ExpectedOpeningTime,\r\nActualOpeningTime,\r\nExpectedClosingTimeAsDate ExpectedClosingTime,\r\nActualClosingTime,\r\ncase when ActualOpeningTime is null then 'MissingDay' else 'MissingIntradayData' end GapKind \r\nfrom V\r\norder by D\r\n<\/pre>\n
A hivatkozott Tick t\u00e1bl\u00e1ban ebben a pillanatban ennyi sor van: 61646572157. Nem \u00edrtam el, ez 61 milli\u00e1rd sor!<\/p>\n
Itt van az SQL 2012-es hint bar\u00e1tunk:
\njoin Tick b with(forceseek (IX_Natural_Key (TickerId, TradeTime)))<\/p>\n
Furcsa m\u00f3don hint n\u00e9lk\u00fcl a becs\u00fclt<\/strong> plan 60x olcs\u00f3bb<\/em>, de m\u00e9gis, a hinttel r\u00e1k\u00e9nyszer\u00edtett (sz\u00e1momra, aki ismeri az adatokat logikus) plan sokkal gyorsabban fut le.
\nHa nem \u00edrom ki az oszlopokat, vagy csak foreceseek vagy forcessek + index n\u00e9v nem veszi r\u00e1, hogy az \u00e9n tervemet val\u00f3s\u00edtsa meg.<\/p>\n
Ezzel nem azt sugallom, hogy hinteljetek mint az \u00e1llat, csak azt, hogy egyes hat\u00e1resetekben j\u00f3l j\u00f6het, ha tudunk r\u00f3la.<\/p>\n
Egy tipp m\u00e9g. Vigy\u00e1zni kell, hogy ne keverj\u00fck a datetime2 k\u00fcl\u00f6nb\u00f6z\u0151 hossz\u00fas\u00e1g\u00fa v\u00e1ltozatait, mert ezeket implicit konvert\u00e1lni fogja a szerver, megint elesve a seekekt\u0151l.<\/p>\n","protected":false},"excerpt":{"rendered":"
Mindig im\u00e1dom, ha valami geeks\u00e9get be tudok vetni a gyakorlatban. SQL Server 2012-ben j\u00f6tt be az az \u00fajdons\u00e1g, hogy a FORCESEEK hintnek…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,30,78,87],"tags":[],"class_list":["post-1977","post","type-post","status-publish","format-standard","hentry","category-szakmai-elet","category-sql-server","category-sql-server-2012","category-sql-server-2014"],"_links":{"self":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/1977","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=1977"}],"version-history":[{"count":3,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/1977\/revisions"}],"predecessor-version":[{"id":1980,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/1977\/revisions\/1980"}],"wp:attachment":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/media?parent=1977"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/categories?post=1977"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/tags?post=1977"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}