March 13, 2012 at 1:59 am
Hi,
I use MSSQL 2005/2008 servers. I want to speed up trigger, the problem is that I has 2 tables one is Product table other is Product Attributes, both tables is really large.
So I want to somehow improve speed of this trigger somehow preventing cursor and do more faster concatenation with less overhead lot of update queries.
Here the part of trigger pattern that I want to optimize and prevent somehow to use cursors:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[ProductAttributesChange] ON [dbo].[ProductAttributes]
WITH EXECUTE AS CALLER
FOR UPDATE, INSERT
AS
set nocount on
Declare @field1 as nvarchar(50);
Declare @field2 as nvarchar(50);
/*...*/
Declare @SKU as nvarchar(50);
declare C1 cursor fast_forward for
select SKU from inserted;
open C1;
fetch next from C1 into @SKU;
while @@FETCH_STATUS = 0
begin
declare C0 cursor fast_forward for
select field1, field2, ... from ProductAttributes where ID=(select ID from Product Where SKU=@SKU)
open C0;
fetch next from C0 into @field1, @field2, ..;
while @@FETCH_STATUS = 0
begin
/* lot of concatenation's and IF goes here */
/*update Product Set .. */
fetch next from C0 into @field1, @field2, ..;
end
close C0;
deallocate C0;
/*update Product Set .. */
fetch next from C1 into @SKU;
end
close C1;
deallocate C1;
I'm waiting for any performance optimization for trigger not tables.
Thanks
March 13, 2012 at 2:57 am
It's impossible to suggest optimizations without seeing the code.
The part you have to rewrite without triggers is the one you left out and replace with comments:
/* lot of concatenation's and IF goes here */
/*update Product Set .. */
This is what you have to replace with set-based code.
If you post it I'm sure lots of people will try to help.
-- Gianluca Sartori
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply