5

I know that this question has been asked in the past but I need to be more specific about the performance of the network.

I need to send from 1 to 100 rows at the same time over the network and I need a specific answer for which method is more suitable in my case. Also which method is more suitable if many clients will run this query at the same time?

The more important thing for my project is the execution speed.

In my results, there was no difference. Α Little more faster was the one insert with multiple values, and the speed of execution was a bit more quickly. But I need to know if there is any trap or something that I need to prevent for these queries.

Thank you in advance.

Example

INSERT INTO Customers (Name, Age, Active) ('Name1',21,1) INSERT INTO Customers (Name, Age, Active) ('Name2',21,1)

Vs

INSERT INTO Customers (Name, Age, Active) ('Name1',21,1), ('Name2',21,1)

I forgot to mention that they are simple inserts.

Improve this question
5
  • 1
    One insert would typically be faster than multiple inserts. If these are wrapped in the same transaction, the difference in speed might not be noticeable. Commented Jul 16, 2018 at 19:52
  • 2
    For just two inserts the performance difference would be so negligible that you probably can't even measure a difference. The performance difference will scale up if you start inserting lots of rows. Commented Jul 16, 2018 at 19:52
  • What you mean wraped in same transaction? Are you tal kingfor multiple inserts?Cause i thing one insert with multiple values using already transaction Commented Jul 16, 2018 at 19:53
  • @Sean Lange my insert rows number is from 1 row till 100.Also i send them from mobile into an sql server, i dont know if a method is better than other in this case Commented Jul 16, 2018 at 19:54
  • @daadaa Are you sending 100 insert statements a single time to the database or are you sending a single statement 100 times? If you pass in a block of text with 100 inserts, that is a single network transmission to the database. 100 calls would have fairly significant latency time overall for serial network transmissions and single statement transactions. 100 would not be horrible, but would certainly be worse than 1. Commented Jul 16, 2018 at 20:01

2 Answers 2

Reset to default
7

One insert versus multiple inserts:

  • One insert is going to be a wee bit faster because the query only needs to be parsed once.
  • If each statement is committed independently (the default), then a single insert is going to be much faster, because it only has one commit.
  • If a record fails to insert, all records fail in a single insert. With multiple inserts, some may succeed (.
  • Insert triggers on the table should be built to handle multiple rows at the same time. This is a comment, but wouldn't affect performance.

My guess is that the difference in performance will be pretty minor in your case, but it is worth measuring. It can become important if you have lots of rows at the same time and your table structure includes multiple indexes.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

Do the inserts rely on each other - in other words, should the all be added, or none be added ? If they are interdependent, then it would be better to wrap them in one transaction. The down side is nothing else can happen to that table until the transaction is done ( mostly, there are exceptions) If you insert the second way (one instert for many answers) , then as was said above, your insert triggers need to be checked to see if they deal with multiple inserts.

Improve this answer

3 Comments

What you mean second method single insert with multiple values are commiting each insert?
Method one will be sent to the database server one insert at a time. Method two the server engine will see the inserts happening all at once. In the trigger, they will all be in the inserted table. You just have to remember that there could be more than one record in the inserted table .
I believe that its better to go in post center once and sending three letters, than to go three times and sending one letter each time.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.