Why fractionate an Insert improves its performance?

Asked

Viewed 41 times

0

Supposing I have a table order_items that records the items of an order, and in this example, the user would be making 1 order from 10,000 computers. At the time of registering the order items in the database, I would like to understand why both operations of insert:

insert into order_items (user_id, order_id, name, price) values (1, 1, 'Computer', 3.400)
insert into order_items (user_id, order_id, name, price) values (1, 1, 'Computer', 3.400)
insert into order_items (user_id, order_id, name, price) values (1, 1, 'Computer', 3.400)
insert into order_items (user_id, order_id, name, price) values (1, 1, 'Computer', 3.400)
outros 10.000 inserts

insert into order_items (user_id, order_id, name, price) values (1, 1, 'Computer', 3.400),
                                                                (1, 1, 'Computer', 3.400),
                                                                (1, 1, 'Computer', 3.400),
                                                                (1, 1, 'Computer', 3.400),
                                                                +10.000 registros

They have a much lower performance compared to the model below?:

insert into order_items (user_id, order_id, name, price) values (1, 1, 'Computer', 3.400),
                                                                (1, 1, 'Computer', 3.400),
                                                                (1, 1, 'Computer', 3.400),
                                                                (1, 1, 'Computer', 3.400),
                                                                +2497 registros
insert into order_items (user_id, order_id, name, price) values (1, 1, 'Computer', 3.400),
                                                                (1, 1, 'Computer', 3.400),
                                                                (1, 1, 'Computer', 3.400),
                                                                (1, 1, 'Computer', 3.400),
                                                                +2497 registros
insert into order_items (user_id, order_id, name, price) values (1, 1, 'Computer', 3.400),
                                                                (1, 1, 'Computer', 3.400),
                                                                (1, 1, 'Computer', 3.400),
                                                                (1, 1, 'Computer', 3.400),
                                                                +2497 registros
insert into order_items (user_id, order_id, name, price) values (1, 1, 'Computer', 3.400),
                                                                (1, 1, 'Computer', 3.400),
                                                                (1, 1, 'Computer', 3.400),
                                                                (1, 1, 'Computer', 3.400),
                                                                +2497 registros

Test results with a bench MySQL:

For the 1st example `1.04 sec`

For the 2nd example `0.234 sec`

For the 3rd example `0.094 sec`

The impression I got is that the time difference gets clearer when you have more records being entered, but it’s just an impression

sql performance

  • It would be interesting to add the question to the analysis data of the queries in question.

  • 1

    "They perform very inferiorly" and how did you come to this conclusion? can you add this data in the question? And pf also inform the database, this makes all the difference in the answers. Anyway, it is much more performative to do a single operation, pq if validates and transacts at once, but depending on the bank, this involves logging to do rollback, update indexes, etc, so you need to see the execution plan and understand better.

  • If the AUTOCOMMIT option is turned on then the COMMIT procedures will be performed on each command. Such procedures have high cost and so when you put several tuples to each INSERT has a better performance. Evaluate defining a single transaction BEGIN (list of INSERT commands) COMMIT and observe the running time.

  • I updated the question with the bank used and the time it took to execute each operation. As I said there, the impression that me had is that this difference in time seems clearer when you are doing the insertion of a larger amount of records.

1 answer

0

This can be very relative. It can depend on the configuration and structure of the bank itself. In Sql Server this can usually be related to the fact that the bank needs to reserve resources for the log and transaction structure. Causing the DBMS to slow down while all the Inserts are being processed in the same transaction and the bank has not yet committed to release these memory-consuming and processing resources.

But for a more accurate analysis it is necessary to check the execution plan.

Browser other questions tagged sql performance

You are not signed in. Login or sign up in order to post.