Always try to affect the maxiumum number of rows with the minimum number of transactions
Let's say you want to create and populate a simple table containing the phonetic alphabet. Let's create a table to hold this information. In practice phonetic_id would be an auto increment column.
create table phonetic (
phonetic_id int not null primary key,
phonetic varchar(10) not null unique
);
You could produce 26 insert statements like this:
insert phonetic(phonetic_id, phonetic) values (1, 'Alfa');
insert phonetic(phonetic_id, phonetic) values (2, 'Bravo');
insert phonetic(phonetic_id, phonetic) values (3, 'Charlie');
insert phonetic(phonetic_id, phonetic) values (4, 'Delta');
insert phonetic(phonetic_id, phonetic) values (5, 'Echo');
insert phonetic(phonetic_id, phonetic) values (6, 'Foxtrot');
...
insert phonetic(phonetic_id, phonetic) values (23, 'Whiskey');
insert phonetic(phonetic_id, phonetic) values (24, 'Xray');
insert phonetic(phonetic_id, phonetic) values (25, 'Yankee');
insert phonetic(phonetic_id, phonetic) values (26, 'Zulu');
Took 0.345 secs. We can do better:
This is 26 separate transactions and if one of them fails the table is left in an inconsistent state. A better approach is to create a select statement using union all. That way all 26 rows are inserted as a single transaction (atomicity). Either the statement succeeds and all rows are populated or it fails and the table remains empty.
insert phonetic (phonetic_id, phonetic)
select 1, 'Alfa' union all
select 2, 'Bravo' union all
select 3, 'Charlie' union all
select 4, 'Delta' union all
select 5, 'Echo' union all
select 6, 'Foxtrot' union all
select 7, 'Golf' union all
select 8, 'Hotel' union all
select 9, 'India' union all
select 10, 'Juliett' union all
select 11, 'Kilo' union all
select 12, 'Lima' union all
select 13, 'Mike' union all
select 14, 'November' union all
select 15, 'Oscar' union all
select 16, 'Papa' union all
select 17, 'Quebec' union all
select 18, 'Romeo' union all
select 19, 'Sierra' union all
select 20, 'Tango' union all
select 21, 'Uniform' union all
select 22, 'Victor' union all
select 23, 'Whiskey' union all
select 24, 'Xray' union all
select 25, 'Yankee' union all
select 26, 'Zulu';
Took 0.032 sec. Ten times faster than individual insert statements.