Edit Question - KnowledgeHub

Title Body

I've table that contains some buy/sell data, with around 8M records in it: CREATE TABLE [dbo].[Transactions]( [id] [int] IDENTITY(1,1) NOT NULL, [itemId] [bigint] NOT NULL, [dt] [datetime] NOT NULL, [count] [int] NOT NULL, [price] [float] NOT NULL, [platform] [char](1) NOT NULL ) ON [PRIMARY] Every X mins my program gets new transactions for each itemId and I need to update it. My first solution is two step DELETE+INSERT: delete from Transactions where platform=@platform and itemid=@itemid insert into Transactions (platform,itemid,dt,count,price) values (@platform,@itemid,@dt,@count,@price) [...] insert into Transactions (platform,itemid,dt,count,price) values (@platform,@itemid,@dt,@count,@price) The problem is, that this DELETE statement takes average 5 seconds. It's much too long. The second solution I found is to use MERGE. I've created such Stored Procedure, wchich takes Table-valued parameter: CREATE PROCEDURE [dbo].[sp_updateTransactions] @Table dbo.tp_Transactions readonly, @itemId bigint, @platform char(1) AS BEGIN MERGE Transactions AS TARGET USING @Table AS SOURCE ON ( TARGET.[itemId] = SOURCE.[itemId] AND TARGET.[platform] = SOURCE.[platform] AND TARGET.[dt] = SOURCE.[dt] AND TARGET.[count] = SOURCE.[count] AND TARGET.[price] = SOURCE.[price] ) WHEN NOT MATCHED BY TARGET THEN INSERT VALUES (SOURCE.[itemId], SOURCE.[dt], SOURCE.[count], SOURCE.[price], SOURCE.[platform]) WHEN NOT MATCHED BY SOURCE AND TARGET.[itemId] = @itemId AND TARGET.[platform] = @platform THEN DELETE; END This procedure takes around 7 seconds with table with 70k records. So with 8M it would probably take few minutes. The bottleneck is "When not matched" - when I commented this line, this procedure runs on average 0,01 second. So the question is: how to improve perfomance of the delete s

Tags (comma-separated) Cancel