1 / 53

Insert, Update & Delete Performance

Insert, Update & Delete Performance. Joe Chang jchang6@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

dana
Download Presentation

Insert, Update & Delete Performance

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Insert, Update & Delete Performance Joe Chang jchang6@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 > 300 rows Note: difference in I/O cost

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

  6. Insert – Clustered Index > 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 • Write to disk, continue after confirmation • 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 @I < 100,000 BEGIN INSERT Table(…) VALUES (@I, …) SET @I = @I + 1 END BEGIN TRANSACTION WHILE @I < 100,000 BEGIN INSERT Table(…) VALUES (@I, …) SET @I = @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 don’t 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.0000001 Table Update I/O: 0.010071216 CPU: 0.00000100 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‘ UNION ALL SELECT 322,'TYI539087J'

  20. Multi-row Inserts – Union All INSERT N1C(ID,Value) SELECT 321,'TYI539087J‘ UNION 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 @Value1 = VALUE FROM M2C WHERE ID = @ID1 SELECT @Value2 = VALUE FROM M2C WHERE ID = @ID2 SELECT @Value1 = CASE ID WHEN @ID1 THEN VALUE ELSE @Value1 END, @Value2 = CASE ID WHEN @ID2 THEN VALUE ELSE @Value2 END FROM M2C WHERE ID IN (@ID1,@ID2) Plan Cost is lower than 2 separate selects, but actual performance is worse!

  25. Multi-row Delete DECLARE @ID1 int, @ID2 int SELECT @ID1 = 1, @ID2 = 49999 DELETE MIC WHERE ID IN (@ID1,@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 > ~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. Delete–FK & 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

More Related