Could you hire me? Contact me if you like what I’ve done in this article and think I can create value for your company with my skills.

April 2, 2008 / by Zsolt Soczó

SQL Server 2008 újdonságok 29. – insert-select bulk optimalizálások

Ahogy már korábban is írtam, közismert, hogy a minimálisan logolt vagy más néven bulk műveletek nagyon gyorsak, mert a tranzakciós logba nem kerül bele minden egyes pl. beszúrt sor a egy bulk insertnél, hanem csak azok az lapok (extentek? mikor mit írnak) kerülnek megjelölésre, amelyek módosulnak. Azaz sokkal kevesebb adat kerül a logba, több tucatszor gyorsabb lehet a művelet. Nem csak a bulk insert minimálisan logolt, hanem a truncate table, select into, writetext, updatetext, bizonyos index műveletek.

Ez eddig is így volt. Nade, 2008-ban már bizonyos esetkben az insert-select is miminálisan logolt! Ez nagy szám ám, mert sokan úgy töltenek be adatokat, hogy először nyersen behúzzák azokat egy átmeneti táblába, aztán elemezgetik, javítgatják, tisztítgatják, majd áttöltik egy másik táblába. A nyers betöltés mehetett már eddig is gyorsan bulk inserttel (.NETből a cégnél most használtam nemrég az SqlBulkCopy osztályt, durván gyorsabb volt, mint a sima insert), de a két tábla közötti adatátvitel eddig logolt, ergo lassú volt. Eddig.

No, most már megy gyors módon is az insert-select, ha:

“No nonclustered index exists on the table.
If the clustered index is unique, the IGNORE_DUP_KEY option must be set to OFF.
A table lock is specified on the target table.
Inserting insert into an empty table that has a clustered index
Inserting into a heap (azaz olyan tábla, amin nincs clustered index) that has no indexes but that can be non-empty”

Szóval azért nem lehet ezt ész nélkül használni, de odafigyelve nagyon gyors lehet.
Egy egyszerű példa. Előkészületek:

drop table Celtabla
drop table Forrastabla

create table Celtabla
(
id int,
adat nvarchar(1000)
)
go
create table Forrastabla
(
id int,
adat nvarchar(1000)
)
go

set nocount on

declare @i int = 1
while (@i < 1001) begin insert into Forrastabla values (@i, replicate('a', 1000)) set @i += 1 end [/source] Aztán a teszt: [source='sql'] begin tran --hogy ne truncate-olódjon a log mielőtt megnéznénk insert into Celtabla with (tablock) select * from Forrastabla select top 100 operation,context, [log record fixed length], [log record length], AllocUnitId, AllocUnitName, Context from fn_dblog(null, null) where allocunitname='dbo.Celtabla' order by [Current LSN] desc --rollback --commit checkpoint --hadd ürüljön a log (full recoverynél persze nem menne) [/source] [source='c'] operation context log record fixed length log record length AllocUnitId AllocUnitName Context ------------------------------ ------------------------------ ----------------------- ----------------- -------------------- ------------------------------ ------------------------------ LOP_SET_BITS LCX_GAM 54 72 72057594062045184 dbo.Celtabla LCX_GAM LOP_SET_BITS LCX_IAM 54 72 72057594062045184 dbo.Celtabla LCX_IAM LOP_MODIFY_ROW LCX_PFS 62 92 72057594062045184 dbo.Celtabla LCX_PFS LOP_MODIFY_ROW LCX_PFS 62 92 72057594062045184 dbo.Celtabla LCX_PFS LOP_MODIFY_ROW LCX_PFS 62 92 72057594062045184 dbo.Celtabla LCX_PFS ... [/source] A log record length oszlop az érdekes, bár a sor 2 kbyte-os, mégis csak 92 byte kerül a logba, mert mimimális logolással (bulk) megy a táblába az insert. Ha kivesszük a tablock hintet, akkor átmegy sima logolós insertbe, ezért 2108 byte-ot kell neki minden sorhoz logolni: [source='c'] ------------------------------ ------------------------------ ----------------------- ----------------- -------------------- ------------------------------ ------------------------------ LOP_INSERT_ROWS LCX_HEAP 62 2108 72057594061455360 dbo.Celtabla LCX_HEAP LOP_INSERT_ROWS LCX_HEAP 62 2108 72057594061455360 dbo.Celtabla LCX_HEAP LOP_INSERT_ROWS LCX_HEAP 62 2108 72057594061455360 dbo.Celtabla LCX_HEAP LOP_INSERT_ROWS LCX_HEAP 62 2108 72057594061455360 dbo.Celtabla LCX_HEAP LOP_FORMAT_PAGE LCX_HEAP 80 84 72057594061455360 dbo.Celtabla LCX_HEAP LOP_MODIFY_ROW LCX_PFS 62 80 72057594061455360 dbo.Celtabla LCX_PFS LOP_INSERT_ROWS LCX_HEAP 62 2108 72057594061455360 dbo.Celtabla LCX_HEAP ... [/source] Az egész dolog működése finomodik majd még a CTP6 (februári, most aktuális) után, akkor majd frissítem ez a bejegyzést. A restrikciók listája fog csökkenni.

Could you hire me? Contact me if you like what I’ve done in this article and think I can create value for your company with my skills.