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:

INSERT INTO myTable
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

pil0t
207575763

edited Sep 06 '10 at 04:58

Alex%20Yakunin's gravatar image

Alex Yakunin
29714412

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
29714412

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

Subscription:

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

Tags:

×572
×52
×11
×2

Asked: Aug 23 '10 at 02:46

Seen: 2,922 times

Last updated: Sep 06 '10 at 04:58

powered by OSQA