Question about 45 Database Performance Tips for Developers

Question

Post reply

Question about 45 Database Performance Tips for Developers

sqldriver

SSChampion

Points: 10440
More actions
November 4, 2013 at 9:57 am

#395773

http://www.red-gate.com/products/sql-development/sql-prompt/entrypage/sql-performance-tips-ebook
I was reading through, and got sort of confused about the 23rd point:
If you need to insert many rows at once into a table,
use, where possible, the multi-row VALUES clause in
INSERT statements.
Which I understand as just being
INSERT INTO TABLE (column-a, [column-b, ...])
VALUES ('value-1a', ['value-1b', ...]),
('value-2a', ['value-2b', ...]),
...
What situations does this apply to? Day to day, if I'm inserting any amount of data, it's either from table to table, or from a file. When would you have a need to write something like this out for a large amount of data?
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Hunchback SSCrazy Points: 2649 More actions · Answer 1

Instead using:

insert into T1 (c1, c2)

select 1, 2;

insert into T1 (c1, c2)

select 3, 4;

or

insert into T1 (c1, c2)

select c1, c2

from (

select 1, 2;

union all

select 3, 4

) as Q(c1, c2)

use just one statement and the row constructor.

insert into T1(c1, c2) values (1, 2), (3, 4);

There is a limitation in the number of tuples (1000) that can be inserted using the row constructor.

sqldriver SSChampion Points: 10440 More actions · Answer 2

I understand that it's more succinct code (and apparently more performant?). I guess I'm trying to figure out a situation where I'd have to build an insert like that, especially for a large amount of data, as the doc suggests. That seems like a rare situation, unless I'm just sort of spoiled being able to import files and perform table to table inserts freely.

Thanks

Grant Fritchey SSC Guru Points: 398880 More actions · Answer 3

I'm not sure who wrote that tip, but in case it was me...

I must have meant to just clean up the code. It's actually not a major performance enhancement except that it's a single transaction instead of multiple ones. That may have been what I meant (if I wrote it, I don't remember writing that one).

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

sqldriver SSChampion Points: 10440 More actions · Answer 4

Grant Fritchey (11/4/2013)
I'm not sure who wrote that tip, but in case it was me...
I must have meant to just clean up the code. It's actually not a major performance enhancement except that it's a single transaction instead of multiple ones. That may have been what I meant (if I wrote it, I don't remember writing that one).

Yeah, I've had months like that.

Thanks

Jeff Moden SSC Guru Points: 1004311 More actions · Answer 5

erikd (11/4/2013)
http://www.red-gate.com/products/sql-development/sql-prompt/entrypage/sql-performance-tips-ebook
I was reading through, and got sort of confused about the 23rd point:
If you need to insert many rows at once into a table,
use, where possible, the multi-row VALUES clause in
INSERT statements.
Which I understand as just being
INSERT INTO TABLE (column-a, [column-b, ...])
VALUES ('value-1a', ['value-1b', ...]),
('value-2a', ['value-2b', ...]),
...
What situations does this apply to? Day to day, if I'm inserting any amount of data, it's either from table to table, or from a file. When would you have a need to write something like this out for a large amount of data?
Thanks

If you generate a datascript using the native tools in SQL Server, it'll generate an individual INSERT/VALUES statement for each row of data being scripted. I believe the example is just showing how bad things like that can be.

Of course, if it's a distribution script and the INSERT will create more than 10 or 12 rows, I tend to use a file and BULK INSERT. Some companies don't like you to put files on their boxes, though so I have also been known to generate code similar to the example given.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)