merge delete hangs - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

merge delete hangs

Author

Topic

lappin
Posting Yak Master

182 Posts

Posted - 2011-05-18 : 12:03:06

Hi, I have a merge statement which runs OK for INSERT and UPDATE, but I want to add DELETE - but this causes the proc to hang. My proc receives a single day as a date parameter and passes it into a UDF which it uses as a source table.
There is a clustered index on the date field DT in the target table with has over 10 million rows.
Here is simplified version:

Merge into dbo.MyTable as Target
Using (
Select distinct ID
,DT
,value
,[dept]
,dept_name
,[Cost]
,Location
,range
,'1' as Type

from (
SELECT f.ID
,DT
,value
,[dept]
,dept_name
,[Cost]
,Location
,range
, Type
FROM [dbo].[MyFunction] (
@ThisDate) f

) Used ) As Source

ON Target.ID=Source.ID
and Target.DT=Source.DT
and Target.[dept]=Source.[dept]
and Target.Location=Source.Location
and Target.range=Source.range
when matched then
update set Target.[value]=Source.[value],
Target.[dept_name]=Source.[dept_name],
Target.[Cost]=Source.[Cost],
Target.Type='1'

when not matched then
insert ([ID]
,[DT]
,[value]
,[dept]
,[dept_name]
,[Cost]
,[Location]
,[range]
,[Type]) values
(Source.[ID]
, Source.[DT]
, Source.[value]
, Source.[dept]
, Source.[dept_name]
, Source.[Cost]
, Source.[Location]
, Source.[range]
, '1')

--these last lines make the procedure hang
when not matched by source AND convert(date,target.DT) = @ThisDate
AND Target.Type='1' THEN DELETE;

X002548
Not Just a Number

15586 Posts

Posted - 2011-05-18 : 13:59:28

MERGE...holy cow...is this faster than a traditional I,U, D???

I gotta test this out

What does the udf return?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-05-18 : 14:10:44

wow....from bol with some minor changes..maybe this example will help



CREATE TABLE #Target(EmployeeID int, EmployeeName varchar(10), 
     CONSTRAINT Target_PK PRIMARY KEY(EmployeeID));
CREATE TABLE #Source(EmployeeID int, EmployeeName varchar(10), 
     CONSTRAINT Source_PK PRIMARY KEY(EmployeeID));
GO
INSERT #Target(EmployeeID, EmployeeName) VALUES(100, 'Mary');				--Delete
INSERT #Target(EmployeeID, EmployeeName) VALUES(101, 'Sara');				--Same
INSERT #Target(EmployeeID, EmployeeName) VALUES(102, 'Stefano');			--Change

GO
INSERT #Source(EmployeeID, EmployeeName) VALUES(101, 'Sara');				--Same
INSERT #Source(EmployeeID, EmployeeName) Values(102, 'Stilleto');			--Change
INSERT #Source(EmployeeID, EmployeeName) Values(103, 'Bob');				--New			
INSERT #Source(EmployeeID, EmployeeName) Values(104, 'Steve');				--New
GO

SELECT * FROM #Target
SELECT * FROM #Source
GO

 BEGIN TRAN;
MERGE #Target AS T
USING #Source AS S
ON (T.EmployeeID = S.EmployeeID) 
WHEN NOT MATCHED BY TARGET
    THEN INSERT(EmployeeID, EmployeeName) VALUES(S.EmployeeID, S.EmployeeName)
WHEN MATCHED 
    THEN UPDATE SET T.EmployeeName = S.EmployeeName
WHEN NOT MATCHED BY SOURCE
    THEN DELETE 
OUTPUT $action, inserted.*, deleted.*;

SELECT * FROM #Target
SELECT * FROM #Source

ROLLBACK TRAN;
GO

SELECT * FROM #Target
SELECT * FROM #Source
GO

DROP TABLE #Target, #Source
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

lappin
Posting Yak Master

182 Posts

Posted - 2011-05-19 : 04:22:45

Without the delete it does run much faster. The UDF performs some calculations / joins / lookups on the source data and applies these to the target. But it is for a subset of the target data so I cannot just use: WHEN NOT MATCHED BY SOURCE THEN DELETE, because it wipes every other run from the target table. For an example date the UDF returns around 400 rows and the UPDATE / INSERT runs in a few seconds, but adding the DELETE seems to be causing a table scan, even though there is a clustered index on the date field.

lappin
Posting Yak Master

182 Posts

Posted - 2011-05-19 : 06:57:44

My work around was: create a new column "modifieddate" and populate it inside INSERT and UPDATE. Then inside proc if no errors find rows which match the subset criteria but the modifieddate <> today, and delete these rows. This now runs in a few seconds again

If someone knows a better way let me know.

X002548
Not Just a Number

15586 Posts

Posted - 2011-05-19 : 09:56:34

I'll be implementing this for a process we are working on...will let you know how it goes...but what does the udf actually do? I'm sure it is returning a table, so I don't know why that would be a problem.

In any case...hopefully I don't see your same problem...becasue it's 1 input stream, it should be very fast, is my guess

Did you do an explain plan on your process, or put a trace on?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

lappin
Posting Yak Master

182 Posts

Posted - 2011-05-20 : 07:21:15

The UDF does a lot of calculations, joins etc. The whole process is part of a data warehouse where data is taken from normalised tables into a large de-normalised table to give fast access to data. As mentioned earlier the merge is very fast (in my case when used only for INSERT and update). My work around includes DELETE functionality outside MERGE.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources