CODEWITHSUNDEEP
mysqlnode-mysql
josh
josh

Reputation: 145

Inserting multiple rows, simple loop or MySQL stored procedure?

I have a user who can press a button that generates a list of data, and each element in that list needs to have a corresponding row of data inserted in the database table.

When they press said button, I will be inserting about 4-8 rows of data, with about 5 fields, so not a big query at all. So, speaking in performance terms, would it be better practice to just create a for loop that runs 4 separate INSERT queries, or is there something I can do with a stored procedure? I'm not sure if it's worth the hassle of actually creating a stored procedure for something as small as this.

I can't really use one INSERT query to insert multiple rows at once, because that would require a lot of extra code to build the query string on my node.js server.

Upvotes: 0

Views: 816

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269663

If you are inserting fewer than 10 rows, I see no problem with running insert multiple times. You do incur a lot of overhead, starting and committing transactions and passing data into the database. But, databases can handle hundreds of inserts a second, so for a small user base, this is probably good enough.

However, I don't see why insert . . . values would be hard:

insert into t(a, b, c)
    values ('a1', 'b1', 'c1'),
           ('a2', 'b2', 'c2'),
           . . .;

This doesn't seem much harder than looping over the data in the first place.

Upvotes: 1

Related Questions