{var%20f='http://v.t.sina.com.cn/share/share.php?appkey=1515056452',u=z||d.location,p=['&url=',e(u),'&title=',e(t||d.title),'&source=',e(r),'&sourceUrl=',e(l),'&content=',c||'gb2312','&pic=',e(p||'')].join('');function%20a(){if(!window.open([f,p].join(''),'mb',['toolbar=0,status=0,resizable=1,width=440,height=430,left=',(s.width-440)/2,',top=',(s.height-430)/2].join('')))u.href=[f,p].join('');};if(/Firefox/.test(navigator.userAgent))setTimeout(a,0);else%20a();})(screen,document,encodeURIComponent,'','','https://www.manongdao.com/data/attach/logo/logo.png', '推荐 做个烂人 的问题《Fastest way to perform nested bulk inserts with sc》','https://www.manongdao.com/q-26360.html','页面编码gb2312|utf-8默认gb2312'));){kind=link}
In one of our (C#) applications we are inserting/updating a large graph (100's of inserts and updates). This is wrapped in a transaction because I need the entire thing to rollback in case of an error. We are using Dapper to execute the SQL statements.
Unfortunately the entire operation is currently taking between 2 to 8 seconds. That's 2 to 8 seconds the core tables in our database are locked, causing other applications to hang or to suffer from the locks.
I determined that one of the operations, inserts to a table containing over 120 million records, is taking up most of the time, but I'm not sure how I could optimize this.
Roughly, that table graph is set up as follows:
table A (
id int primary_key,
name nvarchar
)
table B (
id int primary_key,
a_id int foreign_key, # has an index
name nvarchar
)
When inserting data into A I also need to insert corresponding data into B. Thus, I'm using scope_identity() to get the id of the record and use it when inserting records into B.
Pseudo-wise this looks as follows:
# open transaction
# perform other table inserts
#
# this is one of the slowest operations
for item in list_a
id_a = exec "insert into A (name) values (" + item.name + "); select scope_identity() as int"
for bar in item.list_b
exec "insert into B (id_a, name) values (" + id_a + ", " + bar.name + ")"
#
# perform more operations
# commit transaction
When Googling this, one of the solutions is to wrap this in a transaction. But I don't know what the performance would be because this is already wrapped in the parent transaction. Would it solve the issue?
A second solution is given by inserting the records using a union all select..., but don't know if I could use the scope_identity() in that call.
How can I improve this particular case? What are some other things I can do to speed up the operation or to prevent other applications to suffer from these locks?
I've written something like this before, but in the end didn't get the chance to test it for performance.
To inserts data into 2 related table I've used a single stored procedure and some sql tricks like table valued parameters, merge and output clause.
Here is a sample script:
First, create the sample tables: tblBase and tblRelated are linked with a one to many foreign key.
Now that we have sample tables, we can start creating the stored procedure for insert.
Create user defined table types:
For this stored procedure, we need to use three user defined table types:
One for the base table,
one for the related table,
and one to map the temporary id to the database-assigned id.
Create the stored procedure:
Now that we have the user defined types, we can create the stored procedure to use them.
Testing:
Tested on 10 records in the base table and a 1000 records in the related table, executed in less then 1 second.
Here is the script for the test:
Clean up: