Insert,%20Update%20

1
Insert, Update Delete Performance

• Joe Chang
• jchang6_at_yahoo.com

2
Insert, Update and Delete

• IUD Basics
• Multi-row Inserts
• Logical IO count
• IUD Operations and Indexes
• IUD Operations and Foreign Keys

3
Insert Plan 1 Row
Insert Table() Values()
No indexes other than primary key No foreign keys
4
Insert I/O Cost
Insert I/O cost depends On number of row in table!
0 1 row
gt 300 rows
Note difference in I/O cost
5
Insert Plan I/O Cost versus Rows
I/O cost
6
Insert Clustered Index
gt 320 rows
Clustered index more or less same as Table
7
Insert Plan Cost Logical I/O
Insert Cost Formula I/O 0.010068378 to
0.016756756 CPU 0.00000100 per
row Total 0.010070635 to 0.016759014
Plan cost independent of indexes at low row counts
Logical I/O count 1st row Table 'MIC_01'. Scan
count 0, logical reads 4, physical reads 0,
read-ahead reads 0. 2nd row Table 'MIC_01'. Scan
count 0, logical reads 2, physical reads 0,
read-ahead reads 0. Row 65,000 Table 'MIC_01'.
Scan count 0, logical reads 3, physical reads 0,
read-ahead reads 0.
8
Disk Setting
Settings vary from Windows 2000 Server Windows
XP Windows Server 2003
Enable Write Caching and Enable Advanced
Performance has large impact on log
write performance

1. Write to disk, continue after confirmation
2. Write to disk, continue immediately

9
INSERT Physical Disk
Each standalone INSERT statement must be matched
to 1 or more write I/Os to transaction log, may
or may not result in write to data, SQL Server
may consolidate transaction log entries from
separate threads (Process ID or SPIDs) into a
single I/O on the transaction log file Log
writes for statements inside BEGIN/COMMIT
TRANSACTION are consolidated?
10
BEGIN/COMMIT TRAN
Which is faster and more efficient?
WHILE _at_I lt 100,000 BEGIN INSERT Table() VALUES
(_at_I, ) SET _at_I _at_I 1 END BEGIN TRANSACTION
WHILE _at_I lt 100,000 BEGIN INSERT Table()
VALUES (_at_I, ) SET _at_I _at_I 1 END COMMIT
TRANSACTION
A
B
11
Update
UPDATE N1N SET Value 'ABC123456D WHERE ID 1
UPDATE MXN SET ID9 1 WHERE ID 1
Non integer values
No Compute Scalar for Updates to Clustered Index
12
Table Update Index Seek
Same as plain Index Seek
13
Table Update CS Top
CPU 0.0000001 / row
CPU 0.0000001 / row
14
Table Update
15
Clustered Index Update
Single component, but numbers dont add up
16
Update Plan Cost
Same cost structure as Insert plus additional
Index Seek cost (I/O costs depend on Table
density and row count) Clustered Index I/O
0.010068378 CPU 0.00000100 per
row Total 0.016477678 Table Index Seek
0.0064081 Compute Scalar 0.0000001 Top 0.000
0001 Table Update I/O 0.010071216 CPU 0.000001
00 Total 0.016480517
Index Seek cost implied?
17
Delete
18
Delete
19
Multi-row Inserts
Compare two separate Insert statements INSERT
N1C(ID,Value) VALUES (321,'TYI539087J') INSERT
N1C(ID,Value) VALUES (322,'TYI539087J') With
statement below
INSERT N1C(ID,Value) SELECT 321,'TYI539087J UNIO
N ALL SELECT 322,'TYI539087J'
20
Multi-row Inserts Union All
INSERT N1C(ID,Value) SELECT 321,'TYI539087J UNIO
N ALL SELECT 322,'TYI539087J'
21
Multi-row Inserts
22
Multi-row Inserts
2 rows
I/O same
CPU 2X
23
Multi-row Inserts
24
Multi-row Select
SELECT _at_Value1 VALUE FROM M2C WHERE ID
_at_ID1 SELECT _at_Value2 VALUE FROM M2C WHERE ID
_at_ID2
SELECT _at_Value1 CASE ID WHEN _at_ID1 THEN VALUE
ELSE _at_Value1 END, _at_Value2 CASE ID WHEN _at_ID2
THEN VALUE ELSE _at_Value2 END FROM M2C WHERE ID IN
(_at_ID1,_at_ID2)
Plan Cost is lower than 2 separate selects, but
actual performance is worse!
25
Multi-row Delete
DECLARE _at_ID1 int, _at_ID2 int SELECT _at_ID1 1, _at_ID2
49999 DELETE MIC WHERE ID IN (_at_ID1,_at_ID2)
Has not been tested!
26
IUD with Additional Indexes

• IUD ops may need to modify indexes
• Insert Delete always
• Update only if modified value is in index
• Plan costs for low row counts
• Not dependent on indexes
• Counter intuitive, but plan not impacted
• IUD w/larger row counts
• Plan depends on indexes

27
Inserts with indexes - I/O count
Index depth Clustered 2, Nonclustered 1 No
indexes other than primary key Table 'MIC'. Scan
count 0, logical reads 2, physical reads 0,
read-ahead reads 0. 1 Nonclustered index Table
'MIC'. Scan count 0, logical reads 3, physical
reads 0, read-ahead reads 0. 2 Nonclustered
indexes Table 'MIC'. Scan count 0, logical reads
4, physical reads 0, read-ahead reads 0.
2 I/O for Clustered Index (Index Depth 2) 1 I/O
for each nonclustered index at Index Depth 1
28
Insert with Select Query
Primary key clustered, and 1 nonclustered index
Up to 500 rows
INSERT
SELECT
gt 505 rows
INSERT MIC() SELECT FROM M2C
29
Multiple Indexes
30
Update w/IX, large row count
600 rows
31
Update multiple IX, large row count
One for each index excluding PK
32
Spool Sequence
Spool I/O 0.008752485 0.0074975/page Spool CPU
0.00000040 0.000000360/row
Sequence CPU 0.0000020/row
33
Delete w/Index large row count
505 rows
1 NC Index
2 NC Indexes
34
Foreign Keys
ALTER TABLE dbo.M2C ADD CONSTRAINT
FK_M2C_M2D FOREIGN KEY ( ID2 ) REFERENCES
dbo.M2D ( ID ) ON DELETE NO ACTION ON
UPDATE NO ACTION
35
Insert w/Foreign Key Constraint
FK
PK
INSERT M2C () VALUES (50001,)
Statistics IO Table 'M2D'. Scan count 1, logical
reads 2, physical reads 0, read-ahead reads
0. Table 'M2C'. Scan count 0, logical reads 2,
physical reads 0, read-ahead reads 0.
Index depth 2, both tables
36
Insert FK details
37
Delete w/FK Constraint
PK
FK
DELETE M2D WHERE ID 50001
Statistics IO Table 'M2C'. Scan count 1, logical
reads 507, physical reads 0, read-ahead reads
0. Table 'M2D'. Scan count 1, logical reads 2,
physical reads 0, read-ahead reads 0.
506 leaf level pages
38
DeleteFK Table Scan compared
PK
FK
39
Delete Reference Table Scan
From Delete op FK Reference
Unusually low cost
From normal Table scan
Expected cost for 506 pages, 50,000 rows
40
Index on Foreign Key
CREATE INDEX IX_M2C_ID2 ON M2C(ID2)
INSERT M2C () VALUES (50001,) Statistics
IO Table 'M2D'. Scan count 1, logical reads 2,
physical reads 0, read-ahead reads 0. Table
'M2C'. Scan count 0, logical reads 4, physical
reads 0, read-ahead reads 0.
DELETE M2C WHERE ID 50001 Statistics IO Table
'M2C'. Scan count 1, logical reads 4, physical
reads 0, read-ahead reads 0.
41
Delete with Indexed Foreign Key
PK
FK
DELETE M2D WHERE ID 50001
Statistics IO Table 'M2C'. Scan count 1, logical
reads 2, physical reads 0, read-ahead reads
0. Table 'M2D'. Scan count 1, logical reads 2,
physical reads 0, read-ahead reads 0.
42
Update with Foreign Key
Update Primary Key table
PK
FK
Update Foreign Key table
FK
PK
43
Query Cost Model
Actual Query Costs in CPU-Cycles Stored Procedure
Cost RPC cost (once per procedure)
Type cost (once per procedure?) Query cost
(once per query) Query one or more
components Component Cost Component base
cost Additional row or page costs
44
INSERT Characteristics
Single row INSERT Clustered index, no other
indexes No Foreign Keys 2x2.4GHz server
Net CPU-cycles cost excludes RPC cost
45
Clustered, Heap, Non-Clust.
Log write consolidation? Context switch
reduction?
Single row INSERT 1) Clustered index 2) Heap
with no indexes 3) Heap with 1 non-clustered
index
46
INSERT Multiple Rows
Multiple rows per INSERT statement (UNION ALL) 8
threads
Multiple single row INSERT statements per stored
proc 8 threads
47
IUD Cost Structure
P4/Xeon Notes RPC cost 240,000 Higher for
threads, owner m/m Type Cost 130,000 once per
procedure IUD Base 170,000 once per IUD
statement Single row IUD 300,000 Range
200,000-400,000 Multi-row Insert Cost per row
90,000 cost per additional row
INSERT, UPDATE DELETE cost structure very
similar Multi-row UPDATE DELETE not fully
investigated
Use Windows NT fibers on
48
INSERT Cost Structure
Index and Foreign Key not fully explored Early
measurements 50-70,000 per additional
index 50-70,000 per foreign key
49
IUD Summary

• Consolidate IUD statements where possible
• Large impact on performance
• Verify impact of BEGIN/COMMIT TRAN
• REPEATABLE READ SERIALIZABLE not tested
• Index Foreign Key overhead
• Some cost on IUD for each index
• Most app 90 Read, 10 Write?
• Is FK required for data integrity?

50
Test Tables
CREATE TABLE dbo.M2C ( ID int NOT NULL
, ID2 int NOT NULL , ID3 int NOT NULL
, ID4 int NOT NULL , ID5 int NOT NULL
, ID6 int NOT NULL , GroupID int NOT
NULL , CodeID int NOT NULL , Value char
(10) NOT NULL , randDecimal decimal(9, 4)
NOT NULL , randMoney money NOT NULL
, randDate datetime NOT NULL , seqDate
datetime NOT NULL ) ON PRIMARY
50,000 rows Index depth 2 99 row per page 506
pages
51
Test Data
DECLARE _at_I int, _at_rowCnt int, _at_p int, _at_sc1 int,
_at_dv1 int SELECT _at_I 1, _at_rowCnt 50000, _at_p
100, _at_sc1 10 SELECT _at_dv1 _at_rowCnt/_at_sc1 WHILE
_at_I lt _at_RowCnt BEGIN INSERT M2C (ID, ID2, ID3,
ID4, ID5, ID6, GroupID, CodeID, Value,
randDecimal, randMoney, randDate, seqDate)
VALUES ( _at_I, _at_I, 1 (_at_I-1)_at_p/_at_rowCnt
((_at_I-1)_at_p)_at_rowCnt, _at_I/4, _at_I/10, (_at_I-1)(320)
1, (_at_I-1)/_at_sc1 1, (_at_I-1)(_at_dv1) 1,
CHAR(6526rand())CHAR(6526rand())CHAR(6526r
and()) CONVERT(char(6), CONVERT(int,100000(9.0
rand()1.0)))CHAR(65 26rand()),
10000rand(), 10000rand(), DATEADD(hour,120000ra
nd(),'1990-01-01'), DATEADD(hour,3_at_I,'1990-01-0
1') ) SET _at_I _at_I1 END
52
Test Data Sequences
WHILE loop variable _at_I 1,2,3, Function Sequenc
e (_at_I-1)/10 1 increments every 10
rows (_at_I-1)(10) 1 10 distinct values
repeating 1,2,3,4,5,6,7,9,10,1,2,3
53
Links
www.sql-server-performance.com/joe_chang.asp SQL
Server Quantitative Performance Analysis Server
System Architecture Processor Performance Direct
Connect Gigabit Networking Parallel Execution
Plans Large Data Operations Transferring
Statistics SQL Server Backup Performance with
Imceda LiteSpeed jchang6_at_yahoo.com