I have table with many inserts, and now DO produce script to insert like this:

INSERT INTO myTable (id,text,blablabla...) VALUES (@p1_0, @p1_1, @p1_2)
INSERT INTO myTable (id,text,blablabla...) VALUES (@p2_0, @p2_1, @p2_2)
INSERT INTO myTable (id,text,blablabla...) VALUES (@p3_0, @p3_1, @p3_2)

but most efficient inserts is something like:

SELECT @p1_0, @p1_1, @p1_2
UNION SELECT @p2_0, @p2_1, @p2_2
UNION SELECT @p3_0, @p3_1, @p3_2

With 25 batch size i checked execution plan:

my insert of 25 rows take 8% and each row of first variant takes 3% (3%*25 = 75%) huge difference

measured time (with sql profiler) is about 61 ms for single insert and 400ms for 25 separate inserts.

asked Aug 23 '10 at 02:46

pil0t's gravatar image


edited Sep 06 '10 at 04:58

Alex%20Yakunin's gravatar image

Alex Yakunin

I think they have some issue with this to support more actions in batches, like DELETE FROM table1 WHERE ID IN (1, 2, 3, ....). Just set lower priority to implement such batches, as i guess.

(Aug 23 '10 at 07:40) Peter Ĺ ulek Peter%20%C5%A0ulek's gravatar image

True: http://code.google.com/p/dataobjectsdotnet/issues/detail?id=538

(Aug 24 '10 at 11:34) Alex Yakunin Alex%20Yakunin's gravatar image

One Answer:

We're aware of this, but for now we postpone all optimization-related works.

Btw, SQL Server supports multirow inserts, this must be even more efficient way.

Related issues:

  • http://code.google.com/p/dataobjectsdotnet/issues/detail?id=538
  • http://code.google.com/p/dataobjectsdotnet/issues/detail?id=787
  • http://code.google.com/p/dataobjectsdotnet/issues/detail?id=327

Guys, if this is really important for you, please vote for this question. We'll try to implement this sooner then.

answered Aug 24 '10 at 11:31

Alex%20Yakunin's gravatar image

Alex Yakunin

edited Aug 24 '10 at 11:35

Your answer
Please start posting your answer anonymously - your answer will be saved within the current session and published after you log in or create a new account. Please try to give a substantial answer, for discussions, please use comments and please do remember to vote (after you log in)!
toggle preview


Once you sign in you will be able to subscribe for any updates here



Asked: Aug 23 '10 at 02:46

Seen: 5,060 times

Last updated: Sep 06 '10 at 04:58

powered by OSQA