InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 14:48:37 GMT20http://www.sqlservercentral.com/Forums/Topic1382338-391-1.aspxInserting into a huge table such as your history table may take quite a while.consider partitioning the table on e.g. a date or datetime column (partition per day / week / month, whatever the case may be) for an example of partitioning, see [url=http://arturicast.co.za/blogs/?p=5]Table Partitioning[/url]Thu, 08 Nov 2012 02:31:32 GMTStewart "Arturius" Campbellhttp://www.sqlservercentral.com/Forums/Topic1382338-391-1.aspxHelloHelloplatform: MSSQL 2008R2I'm facing a performance problem using the merge statement, described below:begin try set transaction isolation level read uncommitted begin tran insert into dbo.history --table containing history of data changes (20M of rows; quite a few indexes defined upon it) (pool, ticket, oid, old_value, new_value, [userId], eCode, TCode) select [pool], ticket, 1360, isnull(cast(old_value as varchar),''), cast(new_Value as varchar), 1, 'Merge', 'test' --I guess cast may also be a problem from ( merge into dbo.table2 AS target_table using (select vm.ticket, vm.num from dbo._interface_1 vm inner join dbo.table3 T3 on T3.ticket = vm.ticket and T3.Form = 11 and T3.isDeleted = 0 where vm.Inserted = 1) as source_table (ticket, num) --source data select contains 27K records, performs super fast when run separately on (target_table.ticket = source_table.ticket) --destination table contains unique index on columns ticket when matched then update set OValue = num when not matched then insert ([pool], ticket, OValue) values (23, ticket, num) output inserted.[pool], inserted.ticket, deleted.num, inserted.num) as spr([pool], ticket, old_value, new_Value); commit tranend trybegin catch rollback tran select error_message()end catchNumbers: 27K of rows having been either inserted or updated + 27K of rows having been inserted into history table.The operation takes almost [b]5 minutes [u][/u][/b]?? to complete. I've checked for indexes & statistics and they all seem to be in shape. No missing indexes can be found.Hardware problem? Don't think so. Is a tough and resiliant piece of equipment.Anwhow; has anyone got an idea why the statement performs so poorly? I know it's hard to give a piece of advice with so little to start with. I can't stop thinking that I've missed the point along the way - something in general, regarding the merge statement rules. I don't know. I've read the MS article about merge statement recommendations.Thanks in advanceThu, 08 Nov 2012 02:13:55 GMTmarko.celarc