asked
1000 individual blob data inserts the same performance as 1 batched insert
I have a small table in which I insert 20,000 bytes of binary data along with some supporting columns. When I run 1000 individual inserts on SS 2017 it takes about 5 seconds. When I batch the inserts and execute all 1000 rows in one insert statement it also takes about 5 seconds. Why is the batched insert not any faster? How can I improve the insert performance? I cannot use BULK LOAD as each row in the application comes from devices and will be different. The table schema and the two SQL insert scripts are in the Gists below: Waveform-TABLE.sql - [
https://gist.github.com/tgspacelabs/756e91c7e0c50d0ba7b7d37ee071d739][1] Waveforms Insert-1000-Individual.sql - [
https://gist.github.com/tgspacelabs/f2bd5eaa693b5ead46a62c6931a474b8][2] Waveforms Insert-1000-Batched.sql - [
https://gist.github.com/tgspacelabs/d71206d0b62eb607c1e1cf847fca3776][3] Thanks, Tony [1]:
https://gist.github.com/tgspacelabs/756e91c7e0c50d0ba7b7d37ee071d739 [2]:
https://gist.github.com/tgspacelabs/f2bd5eaa693b5ead46a62c6931a474b8 [3]:
https://gist.github.com/tgspacelabs/d71206d0b62eb607c1e1cf847fca3776
@runamuk0 In this scenario the batched insert might not be any faster. The amount of data that needs to be located to data pages is still the same (about 20 MB). It is possible that locating it to the pages just takes some time, and there is not much that can be done. You can try changing the **large value types out of row** via [sp_tableoption][1], maybe this will help if you change it to 1 from 0 in order to reduce the number of in-row data pages locating the entire binary column contents, rather than just parts of it, to LOB. The other option would be to create a user defined table type, feed all rows from the app to a single procedure parameter via SqlDbType.Structured, but I doubt that this will help. Here is the [link to the answer][2] which has code sample showing how to send multiple rows of data as a single parameter to the procedure. The answer is old (7 years)m but the technique still works. [1]:
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-tableoption-transact-sql [2]:
https://ask.sqlservercentral.com/questions/8438/how-to-update-the-session-datatable-from-cnet-to-s.html
@runamuk0 -- to add to Oleg's comment on TVPs, since your are 2017... I wonder if were to create this TVP as memory optimized (if you have enough RAM) and then INSERT INTO table WITH (TABLOCK) SELECT ... FROM @TVP (for parallel load). Wonder if you test it and it makes a difference
19 People are following this question.
