0

I am facing a performance issue while sending a list of values to SQL Server DB by a stored procedure.

When I passing a list as a type-table param, the SQL Profiler shows me that the list execute as

INSERT INTO Entries (id, name) VALUES (@p1, @p2)                                                               
INSERT INTO Entries (id, name) VALUES (@p3, @p4)                                                               
INSERT INTO Entries (id, name) VALUES (@p5, @p6)                                                               
INSERT INTO Entries (id, name) VALUES (@p7, @p8)                                                                
INSERT INTO Entries (id, name) VALUES (@p9, @p10)

and I want to execute the list as(better performance)

INSERT INTO Entries (id, name) VALUES (@p1, @p2), (@p3, @p4), (@p5, @p6),(@p7, @p8),(@p9, @p10)

I tried to check the option with string_split for simple variables but it is not efficient for complex objects.

I also researched for solutions in Dapper and EF(still with SP) and both are still executing an insert loop on the list.

Any ideas?

Tal Shloman
  • 53
  • 12
  • What does this have to C#, EF, .Net Core and Dapper? Thea above statements are ***very*** simple; if they aren't performant then your table, `Entries` is likely overly indexed or you have some kind of terribly written trigger on it. – Thom A Oct 18 '21 at 08:07
  • A simple way to speed up 5 inserts is to perform them in a transaction, as this cuts down waiting on commits. – Jeroen Mostert Oct 18 '21 at 08:12
  • @Larnu , but if you passing 1000 rows.. there is a difference between the two options that I mentioned.. the second is better – Tal Shloman Oct 18 '21 at 08:26
  • 1
    You don't show your actual procedure code and how you are using the parameter. `Insert into... select...` is what I would expect to see. – Stu Oct 18 '21 at 08:28
  • Yes, because the second is a single statement, where as the former is lot's of single statements. Much like if you were asked to move a bunch of boxes from one place to another; if you moved them one at a time, that would take you far longer than moving them all in one go. – Thom A Oct 18 '21 at 08:36
  • Show your code. If you are doing `INSERT INTO Entries (id, name) SELECT id, name FROM @YourTvp` then I strongly doubt profiler would show that – Martin Smith Oct 18 '21 at 09:16
  • The profiler is simply showing the equivalent code if you wanted to execute it in SSMS. **It is not the code that actually gets executed.** TVPs use the bulk-load mechanism, which is much faster – Charlieface Oct 18 '21 at 09:53
  • @MartinSmith This is a known issue with SQL Profiler, it's simply showing what it looks like in SSMS – Charlieface Oct 18 '21 at 09:55
  • They claim that profiler is showing multiple `INSERT INTO Entries` - so this is the insert to the base table not to the TVP – Martin Smith Oct 18 '21 at 10:02
  • The claim is doubtless incorrect though – Martin Smith Oct 18 '21 at 10:06

0 Answers0