Use same plan or pack together several insert statement and get a lot of single use plans?

Question

What would be the best for SQL-Server 2012.

If we have a collection that needs to be inserted to the db. One way is to execute each insert with a parameterized query like this (pseudo)

foreach(car in cars)
(
  sqlstring = "INSERT INTO car(name, type) VALUES(@name, @type)
  execute
)

Another approach is to each the insert statements together and only execute ones.

foreach(car in cars)
(
      sqlstring += "INSERT INTO car(name, type) VALUES(@name+index, @type+index)
)
execute

The second approach will get a lot of cached plans that will probably only be used once. The first approach I think is faster.

There is a third option I know of to use TableValued parameters but let's skip this for this discussion.

What would be the best approach regarding performance?

Answer 1

For a couple of thousand rows, creating an enormous statement to hold everything isn't practical -- parsing the statement and generating a plan will become a noticeable overhead, especially if the query size itself becomes variable. Use single INSERT statements instead, but wrap them in a transaction to minimize the overhead of logging.

Although you've discarded table-valued parameters, those would also be a viable solution (certainly almost always preferable to huge parameter lists, though there are specialized cases where those work better).

Answer 2

There's another option that would arguably work better given that you're working with SQL Server 2012: just do them as one insert:

sqlstring = "INSERT INTO car(name, type) VALUES "

foreach(car in cars)
(
      sqlstring += "(@name+index, @type+index),"
)
//Remove last comma
execute

It will treat the insert as a single batch, which should generally be better than a lot of single inserts (of course, as always, it's best to test this to be sure).