question

runamuk0 avatar image
runamuk0 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
performanceinsertblob
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@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
0 Likes 0 ·
@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
0 Likes 0 ·
@Oleg Thanks for the response. I tried the sp_tableoption and the results were the same.
0 Likes 0 ·

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.