172-32-1176 PS3333 1 ПС v..- i ? )C-n,s::n

213-46-8915 BU1032 2 К С-;-- .а'-:о ;е

213-46-8915 BU2G75 1 ICO Gfr Hn-jOlO

238-95-7766 PC1035 1 ICi Слч; - Che-y!

Добавляя OTii избыточные сто.тбцы в таб.шну titleauthor. nuii 11,!баБЛяе.\к:я от одного соединения, которое было бы neooxivuiMo лля по.зучеиия трсбуе.мо!! инфор.мации. В случае запроса, o6pa6ari)iiiainira.T() .чт.тиюиы .iannceil, это .может привести к зиачительио.му уве.тичеиию пр(Л13вол1ггсльиосги. Конечно, д.ш того чтобы гарантировать ирав!!льность эти.х пзбьггочиых зиачети'!, иеобходи.мо использовать механиз.м, подобпьй! oneparoii\ UPDATE. и|м.дставлснпо.му в примере.

Сводные таблицы

Другой все чаще прн.меияе.\и>н'1 .\1ет(;л дстюр.ма.шзацип д.чя уве;П!чения иропз-водительностн заключается в создати! сводных табл]щ - таблиц, содержащих cyM.viapHbie данные из других таблиц. ,)гот мегод стал популярным из-за того, что некоторые нроизводгггели СУБД ирсл()с гав.-и!!ог [ггроеиную иод.гержку сводных таблгщ. Создание су.\!марной таблицы об!лчио заключаегся в вьпюлнении часто используемого запроса (который, воз.можио. 1и.И!о.-тяется довольно долго) заранее и сохранении его результатов в суммарно!! таблшщ. Когда приложению потребуются эти да1П1ые, оно будег об[)ащагься к oroii сгагической таблице. Зате.м, в периоды наи.меньшей загрузки пли когда .по иаибо.тсс удобно, запрос для получения суммарных данных .мол<ет быть вьиюл.чеи снова для обновления таблицы актуальной ннформациеГь Это хороигая альтернатива часто.\[у выполнению долгих запросов.

Одна проблема в это.м подходе ~ обс.чужнваиие. Создание cyM.viapnoii таблицы для таблиц с отдельпьгчги да1Н1ы,\П! удваивает заграпл па обслуживание этой таблтщы. Если у вас есть десять хратит\гьт\ пронедур для первоначальной таблицы, теперь ва.м, скорее всего, понадобится двадцать. Все, что вы выполняете для исходной! таблицы в сфере ал.\иии1стрировап)1Я, теперь следует делать избыточно - все триггеры, ограничеши! и гак далее иеобходнлю будет поддерживать в двух местах вместо одного. Че.м болыпе су.\1ма)пых таблиц у вас есть, тем больще будет головное! болгт

Вариант решения этой административно!! д!!лех!1\!ы и при это.м увел1!чения производительности запроса с помощь!о су.\!мар1!ых таблиц - встроенное суммирование. Встроеи1!ое су.\гмирован!1е !!одразуме!!аег !!ебольшое nsMeiieiuie оригинальной таблицы так, чтоб!.! oi!a .vioma хранить как легал1>!1Ь1е, так и су.\г\юрные данные, зате.м cyMMiipoaainie части данных !! вста!!ку этих сум.мар1!ых данных назад в саму таблицу, воз.мож!!о с удале!!!1ем !1ли apxnimpain!eii первоначальных детальных данных. Одно нз 1!ре1!муществ этого подхода в to.vi, что сум.марные и детальные данные .можно легко получить в.месте ил!! отдельно ~ фактически, запросы обычно не зиа!0т, !)аботают ли опп с лстал!л!ы.м1! или су.м.марными данными. По некоторы.м неза.метн!)1.м с !!ер!юго вз!ляла особе1!ностям можно определить, какие записи явля!отся суммарными, ио болы1!е 01!П никак неотлпчи.мы от своих детатьпых родстве![!£1;ко!!. За.%!(гьте, что. строго говоря, если вы удаляете детальные да!щые, вы также решаете проблсчил, гтшзаиные с .хра!1еннем

избыточных данных. Другое нреи.мушесты) этого rKUXo;.ui - нет нужды г.ынол-пять лиии11010 административную работу, необходимую в случае п])11.\1еиепия отдельной таблицы. Все ссылки внешних ключей, триггеры, ограничения, нрел-ставлення, запросы п хранн.\иле процедуры, работавшие с дета.тьиы.чн! даины.чщ, авто.матнчески будут также работать н с cy.m.\iapnbiNni ланны.\Н1.

Лучше всего проде.\10нстр1!ровать это па при.\!сре. Вот запрос, осуществляющий встроенное суммированне по таблице Orders базы данных Northwind;

USE Northwind GO

ALTER TABLE Orders ACD NL.mberOfCroe-s irt DEFAULT 1 -- Дсбзв.сяем сукмариый сгопбеи GO

UPDATE Orders SET Numbe-OfCraers-OEFAULT -- Те.чущле за.-ис.-! буду ичеть значение по умолчанию

-- Вставляем суммарную информацию

INSERT Orders (CustomerlD. EmployeelD. OrderDate. RequiredDate. ShippedDate.

ShipVia, Freight, ShipName, ShipAddress. ShipCity.

ShlpRegion, ShipPostalCode. ShipCountry. NuaberDfOrders) SELECT NULL. EmployeelD. CONVERT (char(6). Orde-Date. il2)*0r,

19000101, 19000101.1.0.. , . . ,.COUNT;*) -- Su.t), rize rows FROM Orders

WHERE OrderDate < 1998010Г

GROUP BY EmployeelD. C0NVERT(char(6), OrderDate. 112)+0Г

-- Удаляем из Order Details записи которые соответсвую; суммарным зап-лсям DELETE d

FRCM [Order Details] d JOIN Orders о ON d.OrderlD-o.OrderlO WHERE 0.OrderDate <1998010Г AND RequiredDate > 19000101 -- RequiredDate используется, чтобы оставить суммарные записи

-- Удаляем оригинальные версии просуммированных записей DELETE Orders

WHERE OrderDate < 199801C1 AND RequiredDate > 1900010Г -- RequiredDate используется, чтобы оставить сум.марные записи

Сначала этот запрос добавляет hobhii столбец в NumberOfOrders в таблицу Orders. До этого мы определяли количество заказов с по.мощью COUNT(*). Но все меняется с использова11ием встроенного су.ммиронання, NumberOfOrders служит для определения количества заказов, соответствующего данной записи. В случае детальной таблицы оно всегда равно 1 - из-за DEFAULT-ограничення. В случае суммарных записей это может быть любое значение до максимального, которое может хранить тип int. Таким образом, для вычисления суммарного количества заказов мы просто сум.мируем столбец NumberOfOrders. Независи.мо от того, являются ли суммируемые записи итоговыми или детальными, все раоота-ет правильно. Это означает, что, в.место того чтобы выполнять тако11 запрос для получения количества заказов в месяц:

SELECT C0NVERT(char(6). OrderDate. 112) AS OrderMonth.

COUNT(*} AS Total NumberOfO.-ders -- Используем COUNTO для подсчета ко/шчества зйМ.з'М EROM Oraers

GROUP BY C0NVERT(char(6). OrderDate, 112) ORDER BY OrderMonth

-ibi выполняем этот:

SFLECT C0hVtfii;criar-6;, CrcerOdte. 112) AS Qrae-XcriLh,

SEMlNumcerOfCrders i AS TotalNuniberOtG.-ders -- Илсгьзуеи SUM гля пс!1у ен;;я Kcrn-ojciBc

закаэоа FROM Orders

GROUP BY C0NVERT(cna-(6>. Ordeir.e. 112; ORDER BY Order.-lontn

Order>ionth lotaiNunfterOrCraers

199804 /4

199805 и

Воз.чюжио, будет полезно взглянуть на ca.vni даннь[е. В(л' небольшая и.х часть:

SELECT CustOiTierlO. Employeel; CrderCdte. Eeq.jiredCate, NjnterOfCrcers FROM Orders

WHERE Crde.-Odte BETWEEN 19-712G: .WO 19980:0 ORDER BY OrderDate, EaiployeelC

Обратите вии.\шиие, что столбец CustomerlD равен NULL в суы.\шрных записях, так как мы су.чкмируем по EmployeelD и OrderMonth. Это один нз способов отличить су.ммарные записи от детхтьпых. Друго!! способ - проверить столбец RequireDate - для су.ммаргпгх :йиисеГ1 он Bcerzia равен 01/01/1900 - это базовая дата SQL Server.

Вертикальное разделение

Поскольку SQL Server при.меняет несколько страигш, количество записей, которое помещается иа страницу, определяется д^тниой записи. Че.м длиннее запись, тем меньше записей по.мещается на каждой странице. О^изическое разделение таблицы на несколько таблиц позволяет большему количеству записеГ! по.ме-щаться на одной странице, а за счет этого воз.можио увеличение производительности запроса. Вот пример, в котором вертикально [разделяется таблица Orders базы данных Northwind из ири.меров баз данных;

SET NOCOUNT ON USE Northwind

BEGIN TRAN -- Чтобы можно было отменить наши действия

DECLARE (dpagebin binary(6). ipfiie Int. gpage int

-- Получаем первую странииу таб;1иць: (чаше всего)

SELECT ТОР 1 (apagebin=f1rst

FROM sysindexes

WHERE id=OBJECT ID(Orders)

ORDER BY indid

-- Преобразуем ее в номер фай;;а и номер страи'лиы EXEC sp decodepagebin @pagebin. @file OUT. (Spage GUI

-- Исго.ьзуем столбе ir, sloiCPi а за;оХВл= ;лра;-.1ив1 зни опре:;сЛеп/я KOiuneciBo 3arji,f,et

на с1оа ицу - сля э'01 стоач111,ы. овес rRACE0N(36C4) PR INГ СНАЯПЗ)

PRINT ***D-jrip4ic i -.t fTs: L-age c G4>bi i Bi 1 3к'г гг-е oartjt.ioning DBCC PAGECcrUMinO .gl 1 e.Pocye. 3.;;

-- Выполни - ;<ar-pcc, -.сСь; писксгреи рсиэьоаггельчссь зв-;ссса яо рвсдеяеи/я

SELECT *

INTO #ordert!iip!

FROM Craers

-- еперь еер'/1.о!:ьпо рсзяе;яе,ч raci:nuy :-с нзе с'!:ер.:>г.ь,е гаСпиць

-- Создаем таблицу для лране.-ия ссноеиой инфоохацил о заказа/ SELECT OrderlD, CjStomerlD. EmployeeiD. Orceruate, ReqiiiredCale INTO OrdersMain FRCM Orders

-- Добавпяек первичный кластерный кло--

ALTER TABLE OrdersMain ACQ CONSTRAINT pr CraesMcin PRIMAR/ KEY (OrderlD)

-- Создаем таблицу, которая будет хранить только информацию об отгрузке SELECT OrderlD. Freight. ShTpVia, ShipName. ShTpAodress, ShipCity. ShipRepTor. ShipPostalCode. ShipCounpry INTO OrdersShipping FROM Orders

-- ДоОавл!ем перв/.чный кластерный ключ

ALTER TABIE OrdersSmpping AOO CONSTRAINT PK OrdersShTpping PRIMARY KEY (OrderlD)

-- Теперь пооверям количество записв!- ча страницу в первой из двух новых таблиц. -- Вертикальное разделение таблицы Orders увеличило количество записей на страницу. -- за счет этого запросы должны выполняться бьютрее SELECT ТОР 1 PpagePinfTrst FROM sysindexes

WhERE idOBJECT ID( Orde-s.MaTn) ORDER BV indTd

EXEC sp decoaepagebin Фрадеотп, [ofile OCT. @page Gur PRINT CHAR(13)

PRINT ***Dwr,p;ng the first page of OrdersMa-.n AFTER the part,;Поптлд DBCC PAGE( NorthwTnd .Cfile.iapage.O.l)

-- Выполняем запрос, чтобы проверить производительность после разделения

SELECT *

INTO #ordertmp2

FROM OrdersMain

-- Проверим количество записей на страницу во второй таблице. SELEC! ТОР 1 @pagebin first FROM sysindexes

WHERE id=OBJECT ID(OrdersShipping) ORDER BY indid

EXEC sp decodepagebin gpageDin. (afile 001, lapage OUT

Dumpinq fi.g tirs: page с' 0Ue; bSrrpD;;< -.гГ-Ц свес page; fiorl.nwvd .lafiltfipage.Ci; DBCC traceoff(3604j

DROP lAbLt fordc-rt.Tip] DROP lABLF ffOrCertDDZ

KGLlBACK TRAN -- Отменяе ьср ди/. де.сиия

***DumD :nc; the first i:aqe of Orders ECFORE tr.e orxtitiDning

PAGE:

BUFFER;

BuF м/;;бзьссс

bpags 0xlFD9CC0C bhash = OxOOOGOOOC bpageno - (1;143) bdOC = 6 brefe-ences - 8 bkeep = I bstat - 0x9 bspin = 0 Cnext - OxCOOOOCOO

PAGE HEADER;

Page P0xlFD9000C

ii paceld = (I; 143) iP fieaderVers ion = i m type = 1

m typeFlagB-its - 0x0 ,Ti ievei = 0 rri fldgBits = 0x0

mobjld = 357576312 m incexld = С .TiprevPage =- (C;0)

ir, nextrage = (l;29i/ pininlen 58 m siotCnt = 42

m freeCnt = 146 m freeOata = 7962 ;ri reservedCnt С

m lsn = (18:151;6) m xaCLReserved = С m xactid = (0:0)

m ghostRecCnt = 0 (ii tornBUs = 81921

GAM (1:2) AlLCCATEO. SGA1-< (1:3) NOT ALLOCATED. PES (1:)) C,<bO MIXED 0 PCT FULL

DBCC execution completed. If DBCC printed error messages, contact yo adm-nistrator.

***Dumping the first page of OrdO sMain AFER tre partitJocng

PAGE:

BOFFER:

BUF g0xllB37EC0

bpage = OxlFCOeOGO Dnasn = OxOOOOOOGO bpageno = (l:424j

bdbid -- 6 brefe-ences = 0 bkeep = 1

Dstat = 0x9 bspm = 0 bnext = OxOGOOOGOO

PAGE HEADER-

Page Cc0xlFCC6000

m pageld = (1:424) m hedderVers-on = i r!i type - 1

m typeFlagBits = 0x0 m level = 0 iTi flagB;ts = 0x4

m objid - 2005582183 m indexld = 0 m prevPage (0:0;

!!i nextPage = (1:425) pmmlei; = 38 m slDtCnl = 188

m freeCnt ==12 m freebata - /804 ш reservedCnt = 0

iJ.Si - :2й: 1-4:24; п л<:С1ке5сi а;с - О к^-асИ.: --- (СО TjnosiRccCnt = 0 i ;c-nBU5 - С

5AM (1:2) ALLCCATED. SGAM (1:35 NO AELOCATED. PES (M) ОдЛО ALLOCATED 0 PCr Fili

DBCC execuJcn coroieted. If DECC cnnieG error mcsseces. coir.act your sysien aOTrisira.cr.

***uozciq f-e fircr tjice Tf QC&rsSr;oo;r,g Ai-IER ihe p6-L:tioniri!

PAGE;

BLFFEP:

ВСЕ (!!u/llB49E30

opage Cx205040&u OLdbL, = GxGGGCCCCG opageno (1;488)

bdbid = 6 brefererces = G bkeep - 1

Dstat = 0x9 bspin = G bnext - OxCOOOOOQO

PAGE HEADER:

Page PCx205C4CC0

m pageld = (1:488) njieaoere-sion 1 iTi type = 1 .:n tyDcEiagBlts = 0x0 n ieyel = 0 m flagBits GxO

mbjld = 2037582297 mlndexld = 0 mjrevPage =- (00)

m nextPage > 1:489) prrnnler: - 12 m slotCni 55

in fi~eeCr,t 43 ,11 ТгееОа:г = 8039 Ti reserveaCn: G

n lsn = (28:179.24) :л~xactr.eserved = 0 .T, xacr.Id = (0:0) m gh05tRecCnL = 0 !Ч 1огг;ВН5 = 0

GA:-! (1:2) ALLOCAIED. SGAH (1:3) tlCl ALL0:3A1ED. PES (1:1) 0x40 AILOGAIED C PCTJGL; Этот запрос осуществляет следующие действия.

1. Начинает траизакгиио, чтобы .можно было после заверщеиия отменить все действия.

2. Показывает, как выглядит иервм страница таблицы Orders до разделения. Мы видим, сколько записей хранится на странице (посмотрите на поле m slotcnt в заголовке страницы).

3. Выполняет запрос, которьн ! обрабатывает вс!о таблицу, для того чтобы можно было сравн!1ть про1!Звод!1тельность до н после разделения.

4. Ра:исляет таб.тицу Orders на две поиые таблищя с ]10.\10щью SELECT...INTO. По-.мещает основную HHc})opNrami!0, связанную с заказами, в одну таблицу, а информашио, связа]1ну!0 с отгрузко!!, - в дру!7ю.

.5. Отображает перву!о страницу первой из двух HO!ibix таблиц. Это noKasiiBaer 1!а.м, сколько занисей хранится в iiepBoit странице новой таблицы.

6. Снова выполняет предыдущий! запрос для первой из новых табл1!Ц, чтобы сравнить производительность за!гросов.

7. Отображает содержи.мое первой страннц1>1 второй новой таблицы. Это иока-31)!вает, сколько записей помещается па ее первой странице.

8. Уда.тяет временные таблицы, созданные тестовыми запросам!!.

9. Откатывает транзакгиио.

Когда иы UbiHu. iii,i !Ч- .ипцлк. ;i.i;v!;ii;i п- па но.тс m slotcnt в заголовке страницы каждого из иаГюрои, вьтсдепцих комаило!! DBCC PAGE. Это поле показывает, сколько мест пол заппсп и.\1ее1Ч'я иа показапно)! страитте. Вы увидите, что оно зпачпте.тьно уве.тпчплось и таблице OrdersMain lu) сравнению с первоначальной табл(нде11 Orders. Фактически в uoBoii таблице оно должно быть при.мерио в четыре ;)аза б(гилие. Что это .ишчит? Это значит, что запрос, нолучаюиин ! заппсп из OrdersMain, б\.1ет iipu.\Ri)iio и четыре раза эф(!)ективиее по сравнению с те.м, которьн ! получает да,чиые w.i таб.тмцы Orders, Даже если эти странины находятся в кэше, разница, очезнлио, будет- существенно!!. Пон.мите, что фактическое количество заппсеГ! на к'ажло пранице не является постоянны.м. То есть даже если на нерпой стратщс lioiioii таблицы может ио.местнться, скажем, 100 запнсеГ!, на BToiioii 100 :iauiiceii .молчст п не по.меститься. Это нронсходит по несколькн.м причииа.м. Во-первых, SQL Server не придерживает значения пара-,метра FILLFACTOR таблицы на протяжении всего времени. Модификации данных могут из.менить количество загшсей иа з.иаиш)!! странице. Во-вторых, если таблица содержит столбцы пере.меииг)!! .чли[1ы, длина каждой записи может из.ме-няться, те.м са.\Н)1.м .меняя количество aannceii, которое но.местится на страницу. Значение пара.метра FILLFACTOR, по умо.тчанию равное 0. не вынуждает страницы заполнят])Ся до конца -- это ие то же са.мос, что FILLFACTOR 100. Значение FILLFACTOR О 1К)Добш) 100 в то.м сли)1сле, что кластерные иплексы создаются с за-полнеины.ми страттамп данных, а иек.чагчерные индексы - с заполненныхн! страницахт уровня листьев. Однако зпачетте этого пара.метра, равное О, отличается, поскольку при это.м [)езервируется место в верхней части де)ева индекса н в нелистовых crpanmiax иклекса раз.мс])ом, равны.м .максн.мальному раз.меру одного элемента нилекса.

Данный 3aHp(Jc использует х1)ани,\уго процедуру sp decodepagebin. Процедура sp decodepagebin преобразует двоичные номера ([)а11Лов/странпи, такие как значения столбцов first, root и FirstlAM в системной таблице sysindexes, в целые значения, которые можно нпюльзовагь с DBCC PAGE. Если ей передать значение типа Ыпагу(б), подобное значешш.м сто.тбцов в sysindexes, она вернет два вы.ходных параметра, содержащие помер файла и страницы. Вот ее исходный код:

US[ master

IF OBJECT.IDisojmomi&ii-r.- J IS NO mil OROP PRCC sp cecociGpegetir

CREATE PRCC sp c:ecoc:eodceb-,:-, СрадеЬч; vcrchGr(12). gfile irt=NULL OUT. @page int-NULL OUT

О&ъек': sp c!ecodepageD;r

Описание: Преобразует яйсчи.е чекера ф1Г1лг,с;рс ицы (подоСнь.е столбцам root, first и FirstlAM raop.iusi 3ys ;nce<es) в целые значения

Использование: spdecodepjgetin gpcigeuin-pinaryfo) file/page number, tofile- выходно! параметр для номера фглл;. Рраус--=в:,Хо;;:-Сй гарскер для номера страницы

Воззраа,ает: (Ничего)

Разарсотана: Ken He.ndeson. Ь^к.::: 4her3<her,.сср. Версия: 1.2

;5uC0jilbLiy0 . ?\r.j-..i GU!. jij.age GUI

Создана; 1999-C5-13. Даа пссгсднен моянфхкацин: 1999-08-05.

DEC.ARt е^г.Ьтп Dipary(6; IF Opageoir /?) GO~G lieip SET ?1ПС1Л=СА5Т;(2рад9Ь:п AS Ртсагу; fc/} SELECT (afiie-(CAST(SLBSTRlNG;Cdr,blp.G.li AS 1 nt) -POWER (2. В)) + (CAS Г (SUBSTR1 .NG (S i r.O l r . 5.1 i AS Inl);. (apage=(CAST(SUBSTRING(i?lnPin.4,l) A3 inl;*FGWER(2.24;j

(CAST(SUBSTRING(?1nbin,3.l) AS ;nu*P0WER(2,16)) +

(CAST(SUBSTRING{@inDir,.2.1) AS ml)-?0лЕк{2.8)) -

(GASl(SUBSRl№((ainPlP.l,l) AS ini)) from sysindexes RETURN 0

Help:

EXEC sp usage Cocbjectname=sp c:ecocepagePin.

desc-Translates binary file/page nuibers (like those in the sysindexes root, first, and FirstlAM columns) into integers. (aparameters=@pagebin=binary(6) file/page number. @file=OUlPUT pa.rm for file

number. (apage=OUTPUT parm for page number. (aexample-EXEC sp decodeoagebir; 0x050000000100 . (amyfile OUT. Onypage CUT. (3author=i(en Henderson. (aeiiail = .;hen(3khen.com, (aversion 1, @revision-2. @datecreated=6/13/99. (adalelabtchanged=8/5/99 RETURN -i

Эта процедура необходи.ма, поскольку страницы first, root и FirstlAM бесполезны в их родио.м формате. На.м нужно получить з]1ачеиие first, чтобы получить стартовую страницу таблицы (хотя ие гараитируется, что значение в first будет ссылаться иа начальную страницу таблицы, к несчастью, это лучшее, что у нас есть). Чтобы преобразовать значение типа binary(6), хранящееся в столбце first в номер файла и страницы, необходимо по.меиять .местамр; байты в .этом значении, а зате.чг преобразовать это значипге из шестнадцатеричиого в десятичное. После того как мы по.меняем байты места.ми, первые два байта столбца first будут представлять собой ссылку на страницу, а послед}И1е четыре будут соответствовать но.меру страи1И1Ы. Ркшользуя sp decodepagebin, мы упрощаем процеду-])у получения этих значений.

Код в примере выполняет запрос SELECT * до и после разделения, чтобы по-с.\ютреть, как разделение сказывается на производительности запроса. Давайте посмотри.м на планы выполнения запроса до и после разделения (рис. 16.17 и 16.18).

Основное ра.зличие между этими двумя планами выполнения состоит в оценке раз.мера записи. Размер записи у.\геньшается с 240 в первом запросе до 41 во втором. Обычно это означает, что на данной странице помещается больше занисей, а соответственно, больше записей будет получено при каждо.м чтении страницы.

:ELr:~T -

CPU co.t:

Numtic ot j(Ct><lei toil

ЧиЫ( * coil

Рис. 16.17. План выполнения запроса до разделения

Compute 3Tfllai- OcdecsHa

Очесу 9: Cuety coat iieiative to the butch): l.69\ Quecv text: SELICT TOP i gpagebia-litat. fP.IH зуз

иВГН£ ifl-OBJECl Lowcal

Pow court, Ellimelod 10 iw

I/O cori: CPU co t

Number Ы eicecutei Сол- ubtioe L