September 12, 2013 at 8:35 am
Hi all,
I have the following statement in a trigger, in order to update a link table that has 5000000 records.
UPDATE LINK.DATABASE.dbo.PPP1
SET KODAN= @ITEMID
,ONANT=@ITEMNAME
--,REMOTE_PPP1.KODKA=SUBSTRING(I.ITEMGROUPID,1,17)
,KODKA=SUBSTRING(@AMDEVICEBRANDID,1,17)
,MM=SUBSTRING(@UNITID , 1 , 5)
,TVAL=@AMOUNT
,THON=@WHOLESALEAMOUNT
WHERE KODAN2= cast(@REFRECID as char(25))
When I run it from managemet studio it runs quickly. When it runs from the trigger it takes about 30 minutes to do the update.
I would appreciate if someone could help.
Thanks,
John
September 12, 2013 at 8:47 am
Can you post the entire trigger code? I don't see in this trigger any reference to inserted or deleted. I also see a bunch of variables. That gives me a feeling that your trigger needs some modifications.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 12, 2013 at 8:54 am
HI,
This is the code
ALTER TRIGGER [dbo].[CREATE_ITEM] ON [dbo].[ITEMMASTER]
AFTER INSERT,UPDATE
AS
DECLARE @ITEMID NVARCHAR(21)
DECLARE @ITEMNAME NVARCHAR(40)
DECLARE @AMOUNT NUMERIC(15,2)
DECLARE @REFRECID NUMERIC(18,0)
DECLARE @UNITID NVARCHAR(5)
DECLARE @ITEMGROUPID NVARCHAR(17)
DECLARE @WHOLESALEAMOUNT NUMERIC(15,2)
DECLARE @AMDEVICEBRANDID NVARCHAR(5)
select @ITEMID=ITEMID COLLATE Greek_CS_AS
,@REFRECID = recid
,@ITEMNAME=ITEMNAME COLLATE Greek_CS_AS
,@UNITID = SUBSTRING(UNITID,1,5) COLLATE Greek_CS_AS
--,@ITEMGROUPID=SUBSTRING(ITEMGROUPID,1,17) COLLATE Greek_CS_AS
,@AMDEVICEBRANDID=AMDEVICEBRANDID
,@AMOUNT = AMOUNT
,@WHOLESALEAMOUNT = WHOLESALEAMOUNT
from inserted
IF NOT EXISTS (SELECT KODAN2 FROM SERVER.DATABASE.dbo.PPP1 WHERE KODAN2 = cast(@REFRECID as char(25) ))
BEGIN
INSERT INTO SERVER.DATABASE.dbo.PPP1( KODAN
,KODAN2
,ONANT
,KODKA
,MM
,TVAL
,THON
,SPKATHG
,ISOT
,CH_ISOT
,FPA)
SELECT @ITEMID
, cast(@REFRECID as char(25))
, @ITEMNAME
--, @ITEMGROUPID
,@AMDEVICEBRANDID
, @UNITID
, @AMOUNT
, @WHOLESALEAMOUNT
, '00'
, 1
, '3'
, 23
FROM INSERTED
END
ELSE
BEGIN
UPDATE SERVER.DATABASE.dbo.PPP1
SET KODAN= @ITEMID
,ONANT=@ITEMNAME
,KODKA=SUBSTRING(@AMDEVICEBRANDID,1,17)
,MM=SUBSTRING(@UNITID , 1 , 5)
,TVAL=@AMOUNT
,THON=@WHOLESALEAMOUNT
WHERE KODAN2= cast(@REFRECID as char(25))
END
Thanks,
September 12, 2013 at 9:47 am
Your trigger does not support multiple rows correctly. If you have more than 1 row in inserted you will only have a single value in your variables. You need to pull that information from the table in your insert. I would recommend removing ALL of the variables in here completely.
You will have to run two statements. The first will be the update followed by an insert.
Something like this should be close:
UPDATE SERVER.DATABASE.dbo.PPP1
SET KODAN = p.ITEMID COLLATE Greek_CS_AS
,ONANT = p.ITEMNAME COLLATE Greek_CS_AS
,KODKA = SUBSTRING(p.AMDEVICEBRANDID, 1, 17)
,MM = SUBSTRING(p.UNITID, 1, 5) COLLATE Greek_CS_AS
,TVAL = p.AMOUNT
,THON = p.WHOLESALEAMOUNT
from SERVER.DATABASE.dbo.PPP1 p
join Inserted i on i.recid = p.KODAN2
insert SERVER.DATABASE.dbo.PPP1
(
KODAN
,KODAN2
,ONANT
,KODKA
,MM
,TVAL
,THON
,SPKATHG
,ISOT
,CH_ISOT
,FPA
)
select ITEMID COLLATE Greek_CS_AS,
cast(recid as char(25)),
ITEMNAME COLLATE Greek_CS_AS,
AMDEVICEBRANDID,
SUBSTRING(UNITID, 1, 5) COLLATE Greek_CS_AS,
AMOUNT,
WHOLESALEAMOUNT,
'00',
1,
'3',
23
FROM INSERTED
WHERE NOT EXISTS (SELECT KODAN2 FROM SERVER.DATABASE.dbo.PPP1 WHERE KODAN2 = cast(recid as char(25)))
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 12, 2013 at 11:17 am
Again update from trigger is very slow.
September 12, 2013 at 12:43 pm
j.grimanis (9/12/2013)
Again update from trigger is very slow.
At least the trigger will now get the right data. 😉
OK in order to be able to help much we will need to see the actual execution plan, tables structures and indexes would be a big help too.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply