Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

merge statement performance problem Expand / Collapse
Author
Message
Posted Thursday, November 8, 2012 2:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 28, 2014 11:32 PM
Points: 15, Visits: 57
Hello

Hello

platform: MSSQL 2008R2

I'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 tran
end try
begin catch
rollback tran
select error_message()
end catch

Numbers: 27K of rows having been either inserted or updated + 27K of rows having been inserted into history table.
The operation takes almost 5 minutes ?? 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 advance
Post #1382338
Posted Thursday, November 8, 2012 2:31 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:53 AM
Points: 3,986, Visits: 5,240
Inserting 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 Table Partitioning


____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1382343
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse