Insert Update Delete Performance Joe Chang jchang 6yahoo

Insert, Update & Delete Performance Joe Chang jchang 6@yahoo. com

Insert, Update and Delete IUD Basics Multi-row Inserts Logical IO count IUD Operations and Indexes IUD Operations and Foreign Keys

Insert Plan – 1 Row Insert Table(…) Values(…) No indexes other than primary key No foreign keys

Insert – I/O Cost Insert I/O cost depends On number of row in table! 0 & 1 row > 300 rows Note: difference in I/O cost

Insert Plan I/O Cost versus Rows I/O cost

Insert – Clustered Index > 320 rows Clustered index more or less same as Table

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 1 st row Table 'MIC_01'. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0. 2 nd 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.

Disk 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

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?

BEGIN/COMMIT TRAN Which is faster and more efficient? A WHILE @I < 100, 000 BEGIN INSERT Table(…) VALUES (@I, …) SET @I = @I + 1 END B BEGIN TRANSACTION WHILE @I < 100, 000 BEGIN INSERT Table(…) VALUES (@I, …) SET @I = @I + 1 END COMMIT TRANSACTION

Update UPDATE N 1 N SET Value = 'ABC 123456 D‘ WHERE ID = 1 UPDATE MXN SET ID 9 = 1 WHERE ID = 1 No Compute Scalar for Updates to Clustered Index Non integer values

Table Update – Index Seek Same as plain Index Seek

Table Update – CS & Top CPU: 0. 0000001 / row

Table Update

Clustered Index Update Single component, but numbers don’t add up

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: Compute Scalar: Top: Table Update I/O: CPU: Total: 0. 0064081 0. 0000001 0. 010071216 0. 00000100 0. 016480517 Index Seek cost implied?

Delete

Delete

Multi-row Inserts Compare two separate Insert statements: INSERT N 1 C(ID, Value) VALUES (321, 'TYI 539087 J') INSERT N 1 C(ID, Value) VALUES (322, 'TYI 539087 J') With statement below INSERT N 1 C(ID, Value) SELECT 321, 'TYI 539087 J‘ UNION ALL SELECT 322, 'TYI 539087 J'

Multi-row Inserts – Union All INSERT N 1 C(ID, Value) SELECT 321, 'TYI 539087 J‘ UNION ALL SELECT 322, 'TYI 539087 J'

Multi-row Inserts

Multi-row Inserts 2 rows I/O: same CPU: 2 X

Multi-row Inserts

Multi-row Select SELECT @Value 1 = VALUE FROM M 2 C WHERE ID = @ID 1 SELECT @Value 2 = VALUE FROM M 2 C WHERE ID = @ID 2 SELECT @Value 1 = CASE ID WHEN @ID 1 THEN VALUE ELSE @Value 1 END, @Value 2 = CASE ID WHEN @ID 2 THEN VALUE ELSE @Value 2 END FROM M 2 C WHERE ID IN (@ID 1, @ID 2) Plan Cost is lower than 2 separate selects, but actual performance is worse!

Multi-row Delete DECLARE @ID 1 int, @ID 2 int SELECT @ID 1 = 1, @ID 2 = 49999 DELETE MIC WHERE ID IN (@ID 1, @ID 2) Has not been tested!

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

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

Insert with Select Query Primary key – clustered, and 1 nonclustered index Up to ~500 rows > ~505 rows INSERT MIC(…) SELECT … FROM M 2 C SELECT

Multiple Indexes

Update w/IX, large row count 600 rows

Update multiple IX, large row count One for each index excluding PK

Spool & Sequence Spool I/O 0. 008752485 + 0. 0074975/page Spool CPU 0. 00000040 + 0. 000000360/row Sequence CPU 0. 0000020/row

Delete w/Index large row count 505 rows 1 NC Index 2 NC Indexes

Foreign Keys ALTER TABLE [dbo]. [M 2 C] ADD CONSTRAINT [FK_M 2 C_M 2 D] FOREIGN KEY ( [ID 2] ) REFERENCES [dbo]. [M 2 D] ( [ID] ) ON DELETE NO ACTION ON UPDATE NO ACTION

Insert w/Foreign Key Constraint FK PK INSERT M 2 C (…) VALUES (50001, …) Statistics IO: Table 'M 2 D'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0. Table 'M 2 C'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0. Index depth 2, both tables

Insert FK details

Delete w/FK Constraint PK FK DELETE M 2 D WHERE ID = 50001 Statistics IO: Table 'M 2 C'. Scan count 1, logical reads 507, physical reads 0, read-ahead reads 0. Table 'M 2 D'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0. 506 leaf level pages

Delete–FK & Table Scan compared PK FK

Delete – Reference Table Scan From Delete op FK Reference Unusually low cost From normal Table scan Expected cost for 506 pages, 50, 000 rows

Index on Foreign Key CREATE INDEX IX_M 2 C_ID 2 ON M 2 C(ID 2) INSERT M 2 C (…) VALUES (50001, …) Statistics IO: Table 'M 2 D'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0. Table 'M 2 C'. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0. DELETE M 2 C WHERE ID = 50001 Statistics IO: Table 'M 2 C'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0.

Delete with Indexed Foreign Key PK FK DELETE M 2 D WHERE ID = 50001 Statistics IO: Table 'M 2 C'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0. Table 'M 2 D'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.

Update with Foreign Key Update Primary Key table PK FK Update Foreign Key table FK PK

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

INSERT Characteristics Single row INSERT Clustered index, no other indexes No Foreign Keys 2 x 2. 4 GHz server Net CPU-cycles cost – excludes RPC cost

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

INSERT – Multiple Rows Multiple single row INSERT statements per stored proc 8 threads Multiple rows per INSERT statement (UNION ALL) 8 threads

IUD Cost Structure P 4/Xeon* Notes RPC cost 240, 000 Higher for threads, owner m/m Type Cost IUD Base Single row IUD 130, 000 170, 000 300, 000 once per procedure once per IUD statement 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

INSERT Cost Structure Index and Foreign Key not fully explored Early measurements: 50 -70, 000 per additional index 50 -70, 000 per foreign key

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?

Test Tables CREATE TABLE [dbo]. [M 2 C] ( [ID] [int] NOT NULL , [ID 2] [int] NOT NULL , [ID 3] [int] NOT NULL , [ID 4] [int] NOT NULL , [ID 5] [int] NOT NULL , [ID 6] [int] NOT NULL , [Group. ID] [int] NOT NULL , [Code. ID] [int] NOT NULL , [Value] [char] (10) NOT NULL , [rand. Decimal] [decimal](9, 4) NOT NULL , [rand. Money] [money] NOT NULL , [rand. Date] [datetime] NOT NULL , [seq. Date] [datetime] NOT NULL ) ON [PRIMARY] 50, 000 rows Index depth 2 99 row per page 506 pages

Test Data DECLARE @I int, @row. Cnt int, @p int, @sc 1 int, @dv 1 int SELECT @I = 1, @row. Cnt = 50000, @p = 100, @sc 1 = 10 SELECT @dv 1 = @row. Cnt/@sc 1 WHILE @I <= @Row. Cnt BEGIN INSERT M 2 C (ID, ID 2, ID 3, ID 4, ID 5, ID 6, Group. ID, Code. ID, Value, rand. Decimal, rand. Money, rand. Date, seq. Date) VALUES ( @I, 1 + (@I-1)*@p/@row. Cnt + ((@I-1)*@p)%@row. Cnt, @I/4, @I/10, (@I-1)%(320) + 1, (@I-1)/@sc 1 + 1, (@I-1)%(@dv 1) + 1, CHAR(65+26*rand())+CHAR(65+26*rand()) +CONVERT(char(6), CONVERT(int, 100000*(9. 0*rand()+1. 0)))+CHAR(65 + 26*rand()), 10000*rand(), DATEADD(hour, 120000*rand(), '1990 -01 -01'), DATEADD(hour, 3*@I, '1990 -01 -01') ) SET @I = @I+1 END

Test Data Sequences WHILE loop variable @I: 1, 2, 3, … Function (@I-1)/10 + 1 (@I-1)%(10) + 1 Sequence increments every 10 rows 10 distinct values repeating 1, 2, 3, 4, 5, 6, 7, 9, 10, 1, 2, 3

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 Lite. Speed jchang 6@yahoo. com