Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Question about 45 Database Performance Tips for Developers Expand / Collapse
Author
Message
Posted Monday, November 4, 2013 9:57 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 1:30 PM
Points: 388, Visits: 1,530
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
Post #1511186
Posted Monday, November 4, 2013 11:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 7:16 AM
Points: 114, Visits: 610
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.



Post #1511211
Posted Monday, November 4, 2013 11:21 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 1:30 PM
Points: 388, Visits: 1,530
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

Post #1511216
Posted Monday, November 4, 2013 12:24 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:27 PM
Points: 15,541, Visits: 27,919
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1511238
Posted Monday, November 4, 2013 12:38 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 1:30 PM
Points: 388, Visits: 1,530
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
Post #1511249
Posted Monday, November 4, 2013 5:21 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:06 PM
Points: 36,786, Visits: 31,243
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1511331
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse