Ahogy m\u00e1r kor\u00e1bban is \u00edrtam, k\u00f6zismert, hogy a minim\u00e1lisan logolt vagy m\u00e1s n\u00e9ven bulk m\u0171veletek nagyon gyorsak, mert a tranzakci\u00f3s logba nem ker\u00fcl bele minden egyes pl. besz\u00fart sor a egy bulk insertn\u00e9l, hanem csak azok az lapok (extentek? mikor mit \u00edrnak) ker\u00fclnek megjel\u00f6l\u00e9sre, amelyek m\u00f3dosulnak. Azaz sokkal kevesebb adat ker\u00fcl a logba, t\u00f6bb tucatszor gyorsabb lehet a m\u0171velet. Nem csak a bulk insert minim\u00e1lisan logolt, hanem a truncate table, select into, writetext, updatetext, bizonyos index m\u0171veletek.<\/p>\n
Ez eddig is \u00edgy volt. Nade, 2008-ban m\u00e1r bizonyos esetkben az insert-select is mimin\u00e1lisan logolt! Ez nagy sz\u00e1m \u00e1m, mert sokan \u00fagy t\u00f6ltenek be adatokat, hogy el\u0151sz\u00f6r nyersen beh\u00fazz\u00e1k azokat egy \u00e1tmeneti t\u00e1bl\u00e1ba, azt\u00e1n elemezgetik, jav\u00edtgatj\u00e1k, tiszt\u00edtgatj\u00e1k, majd \u00e1tt\u00f6ltik egy m\u00e1sik t\u00e1bl\u00e1ba. A nyers bet\u00f6lt\u00e9s mehetett m\u00e1r eddig is gyorsan bulk inserttel (.NETb\u0151l a c\u00e9gn\u00e9l most haszn\u00e1ltam nemr\u00e9g az SqlBulkCopy oszt\u00e1lyt, durv\u00e1n gyorsabb volt, mint a sima insert), de a k\u00e9t t\u00e1bla k\u00f6z\u00f6tti adat\u00e1tvitel eddig logolt, ergo lass\u00fa volt. Eddig.<\/p>\n
No, most m\u00e1r megy gyors m\u00f3don is az insert-select, ha:<\/p>\n
“No nonclustered index exists on the table.
\nIf the clustered index is unique, the IGNORE_DUP_KEY option must be set to OFF.
\nA table lock is specified on the target table.
\nInserting insert into an empty table that has a clustered index
\nInserting into a heap (azaz olyan t\u00e1bla, amin nincs clustered index) that has no indexes but that can be non-empty”<\/p>\n
Sz\u00f3val az\u00e9rt nem lehet ezt \u00e9sz n\u00e9lk\u00fcl haszn\u00e1lni, de odafigyelve nagyon gyors lehet.
\nEgy egyszer\u0171 p\u00e9lda. El\u0151k\u00e9sz\u00fcletek:<\/p>\n
drop table Celtabla
\ndrop table Forrastabla<\/p>\n
create table Celtabla
\n(
\n id int,
\n adat nvarchar(1000)
\n)
\ngo
\ncreate table Forrastabla
\n(
\n id int,
\n adat nvarchar(1000)
\n)
\ngo<\/p>\n
set nocount on<\/p>\n
declare @i int = 1
\nwhile (@i < 1001)\nbegin\n insert into Forrastabla \n values (@i, replicate('a', 1000))\n set @i += 1\nend\n[\/source]\n\nAzt\u00e1n a teszt:\n\n[source='sql']\nbegin tran --hogy ne truncate-ol\u00f3djon a log miel\u0151tt megn\u00e9zn\u00e9nk\n\ninsert into Celtabla \nwith (tablock) \nselect * from Forrastabla\n\nselect top 100 operation,context, [log record fixed length], [log record length], AllocUnitId, AllocUnitName, Context\nfrom fn_dblog(null, null)\nwhere allocunitname='dbo.Celtabla'\norder by [Current LSN] desc\n\n--rollback\n--commit\n\ncheckpoint --hadd \u00fcr\u00fclj\u00f6n a log (full recoveryn\u00e9l persze nem menne)\n[\/source]\n\n[source='c']\noperation context log record fixed length log record length AllocUnitId AllocUnitName Context\n------------------------------ ------------------------------ ----------------------- ----------------- -------------------- ------------------------------ ------------------------------\nLOP_SET_BITS LCX_GAM 54 72 72057594062045184 dbo.Celtabla LCX_GAM\nLOP_SET_BITS LCX_IAM 54 72 72057594062045184 dbo.Celtabla LCX_IAM\nLOP_MODIFY_ROW LCX_PFS 62 92 72057594062045184 dbo.Celtabla LCX_PFS\nLOP_MODIFY_ROW LCX_PFS 62 92 72057594062045184 dbo.Celtabla LCX_PFS\nLOP_MODIFY_ROW LCX_PFS 62 92 72057594062045184 dbo.Celtabla LCX_PFS\n...\n[\/source]\n\nA log record length oszlop az \u00e9rdekes, b\u00e1r a sor 2 kbyte-os, m\u00e9gis csak 92 byte ker\u00fcl a logba, mert mimim\u00e1lis logol\u00e1ssal (bulk) megy a t\u00e1bl\u00e1ba az insert.\nHa kivessz\u00fck a tablock hintet, akkor \u00e1tmegy sima logol\u00f3s insertbe, ez\u00e9rt 2108 byte-ot kell neki minden sorhoz logolni:\n\n[source='c']\n------------------------------ ------------------------------ ----------------------- ----------------- -------------------- ------------------------------ ------------------------------\nLOP_INSERT_ROWS LCX_HEAP 62 2108 72057594061455360 dbo.Celtabla LCX_HEAP\nLOP_INSERT_ROWS LCX_HEAP 62 2108 72057594061455360 dbo.Celtabla LCX_HEAP\nLOP_INSERT_ROWS LCX_HEAP 62 2108 72057594061455360 dbo.Celtabla LCX_HEAP\nLOP_INSERT_ROWS LCX_HEAP 62 2108 72057594061455360 dbo.Celtabla LCX_HEAP\nLOP_FORMAT_PAGE LCX_HEAP 80 84 72057594061455360 dbo.Celtabla LCX_HEAP\nLOP_MODIFY_ROW LCX_PFS 62 80 72057594061455360 dbo.Celtabla LCX_PFS\nLOP_INSERT_ROWS LCX_HEAP 62 2108 72057594061455360 dbo.Celtabla LCX_HEAP\n...\n[\/source]\n\nAz eg\u00e9sz dolog m\u0171k\u00f6d\u00e9se finomodik majd m\u00e9g a CTP6 (febru\u00e1ri, most aktu\u00e1lis) ut\u00e1n, akkor majd friss\u00edtem ez a bejegyz\u00e9st. A restrikci\u00f3k list\u00e1ja fog cs\u00f6kkenni.\n<\/p>\n","protected":false},"excerpt":{"rendered":"
Ahogy m\u00e1r kor\u00e1bban is \u00edrtam, k\u00f6zismert, hogy a minim\u00e1lisan logolt vagy m\u00e1s n\u00e9ven bulk m\u0171veletek nagyon gyorsak, mert a tranzakci\u00f3s logba nem…<\/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-492","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\/492","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=492"}],"version-history":[{"count":0,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/492\/revisions"}],"wp:attachment":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/media?parent=492"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/categories?post=492"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/tags?post=492"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}