2

I am using sequelize as ORM for my nodejs application.

The scenario is, I have to insert a Poll and its Options and get their objects.

Approach 1:

1 - Insert Poll:

INSERT INTO `polls` (`id`,`start_at`,`expire_at`,`options_count`) VALUES (DEFAULT,1487945658,1487945965,3);

2 - Insert Options (bulk insert):

INSERT INTO `poll_options` (`id`,`poll_id`,`text`) VALUES (NULL,{id},'red'),(NULL,{id},'green'),(NULL,{id},'blue');

3 - Select Options (I have to select because I don't get id of inserted Option due to bulk insertion):

SELECT `id`, `poll_id`, `text` FROM `poll_options` WHERE `poll_id` = {id};

Approach 2:

1 - Insert Poll

2 - Insert each Option one by one (this way I can get inserted id as well, so no need of explicit SELECT at the end)

Question:

Which approach would be better?

Both tables have referential integrity enabled and using innodb engine.

2

If we're talking performance, first approach is better. Here is why:

Whenever new data is inserted into a table, the indexes will also be updated, and physically reordered. In the case on InnoDB, not only are the index files updated, but the data file itself could be reordered, it is clustered based on the primary key. So, re-ordering/updating is an expensive operation, and you want to minimize it.

When you do bulk insert, the indexes are updated at when the statement is finished. i.e. after inserting all rows. On the other hand, when you do individual inserting, physical files are updated after each insert.

The overhead of the last select statement in the first approach is negligible.

  • Talking about approach 2, if I use transaction. Will it make a difference? Because physical indexes will not be touched until commit. – Shaharyar Feb 24 '17 at 18:21
  • I think yes, it makes difference. However, you are still hitting the disk more often than in approach one. Because even transactions are stored on disk (In 'ib_logfile's). In other words, approach 2 with transactions is better than not using transaction, but still worse than approach 1. I'd like to see others' input about this point. – Jehad Keriaki Feb 24 '17 at 18:35
  • I get it, thanks a lot for detailed answer. – Shaharyar Feb 24 '17 at 18:50
  • Any decent RDBMS will offload the house keeping operations to background processes. You also do not discuss caching of blocks in memory. – Grimaldi Feb 24 '17 at 18:59
0

Although you're using an ORM, which hides the nitty gritty details of SQL for your developers, you seem to be interested in different approaches to insert a record and a multitude of detail records in a child table.

I would opt for the code, which conveys the intend most clearly and which can be maintained more easily. In my opinion, this will likely be the second option. Only if (measured) performance is an issue, then I would start optimizing.

  • Yeah I also prefer that, but it will not be too complicated and can be explained by comments, but performance boost I get here will be much much valuable. – Shaharyar Feb 25 '17 at 6:32
0

But those are not the same?? Approach 1 retrieves one of the 3 ids; Approach 2 retrieves each of 3 ids.

And, why do you need any id from poll_options?

Seems like this is all you need:

BEGIN;

INSERT INTO `polls` (`start_at`,`expire_at`,`options_count`)
    VALUES (1487945658,1487945965,3);
-- note: no mention of polls.id, above.

{id} = LAST_INSERT_ID() -- however your language does this

INSERT INTO `poll_options` (`poll_id`,`text`)
     VALUES
     ({id},'red'),
     ({id},'green'),
     ({id},'blue');
-- again, no mention of poll_options.id

COMMIT;

Even so, the BEGIN and COMMIT are not really required. (At worst, you could might store a row in polls, then fail to store the rows in poll_options.

Performance:

  • 1+1 inserts is nearly twice as fast as 1+3 inserts
  • Getting LAST_INSERT_ID() is faster than any SELECT.
  • (1) Both approaches are same, I am not SELECTing one poll_option I am actually getting all three by their parent id (which is poll_id). (2) I need to get all inserted ids for poll_options, how can I get them if I perform bulk insert? That is the main problem, I had to go for explicit SELECT to get ids. – Shaharyar Feb 25 '17 at 6:27
  • What will you then do with the 3 ids? – Rick James Feb 25 '17 at 16:44
  • Create the objects and respond to request, its an API – Shaharyar Feb 25 '17 at 20:54
  • Then it is sort of a tossup. Consider each INSERT and each SELECT ad "1 unit of effort"; exclude the fetching of a single LAST_INSERT_ID. From that, you can judge which Approach is slightly faster. The bottom line is: it won't matter a lot unless you have lots of rows. – Rick James Feb 25 '17 at 21:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.