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.