Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

trigger performance intolerably slow

6 posts in Trigger Last posting was on 2006-11-09 21:05:08.0Z
Paul Man Posted on 2006-11-06 14:44:43.0Z
From: "Paul Man" <paulman@datasoft.ie>
Newsgroups: advantage.trigger
Subject: trigger performance intolerably slow
Date: Mon, 6 Nov 2006 14:44:43 -0000
Lines: 76
Organization: DSoft
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962
NNTP-Posting-Host: 82.141.233.142
Message-ID: <454f497c@solutions.advantagedatabase.com>
X-Trace: 6 Nov 2006 07:41:00 -0700, 82.141.233.142
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!82.141.233.142
Xref: solutions.advantagedatabase.com Advantage.Trigger:278
Article PK: 1136343

I have the following trigger that basically looks for changes to cost fields
and recalulates and updates the real cost if there are changes.

declare @homeval double;
declare @carriage double;
declare @duty double;
declare @landed double;
declare @currcode string;
declare @exchRate double;

declare @diff1 double;
declare @diff2 double;
declare @diff3 double;

set @diff1 = (select currcost1 + carperc1 + duty1 from __new) - (select
currcost1 +carperc1 + duty1 from __old);
set @diff2 = (select currcost2 + carperc2 + duty2 from __new) - (select
currcost2 +carperc2 + duty2 from __old);
set @diff3 = (select currcost3 + carperc3 + duty3 from __new) - (select
currcost3 +carperc3 + duty3 from __old);

if isnull(@diff1, 1) <> 0 then
set @currcode = (select currcode from placc where code = (select supp1 from
__new ));
set @exchRate = isnull((SELECT ExchangeRate from NLCURRENCY where CODE=
@currcode ), 1);
set @homeVal = (select isnull(currcost1,0) * @exchRate from __new );
set @duty = ( select isnull(duty1, 0) from __new );
set @carriage = ( select isnull(CARPERC1, 0) from __new );
set @carriage = @homeVal * @Carriage / 100.0;
set @duty = @homeVal * @Duty / 100.0;
set @Landed = @homeVal + @carriage + @duty;
update skitem set cost1 = @landed where code = (select code from __new);
end if;


if isnull( @diff2, 1) <> 0 then
set @currcode = (select currcode from placc where code = (select supp2 from
__new ));
set @exchRate = isnull((SELECT ExchangeRate from NLCURRENCY where CODE=
@currcode ), 1);
set @homeVal = (select isnull(currcost2,0) * @exchRate from __new );
set @duty = ( select isnull(duty2, 0) from __new );
set @carriage = ( select isnull(CARPERC2, 0) from __new );
set @carriage = @homeVal * @Carriage / 100.0;
set @duty = @homeVal * @Duty / 100.0;
set @Landed = @homeVal + @carriage + @duty;
update skitem set cost2 = @landed where code = (select code from __new);
end if;


if isnull( @diff3, 1) <> 0 then
set @currcode = (select currcode from placc where code = (select supp3 from
__new ));
set @exchRate = isnull((SELECT ExchangeRate from NLCURRENCY where CODE=
@currcode ), 1);
set @homeVal = (select isnull(currcost3,0) * @exchRate from __new );
set @duty = ( select isnull(duty3, 0) from __new );
set @carriage = ( select isnull(CARPERC3, 0) from __new );
set @carriage = @homeVal * @Carriage / 100.0;
set @duty = @homeVal * @Duty / 100.0;
set @Landed = @homeVal + @carriage + @duty;
update skitem set cost3 = @landed where code = (select code from __new);
end if;


If I run a sql like

update skitem set currcost1 = currcost1+1,
currcost2 = currcost2+1,
currcost3 = currcost3+1;

to force a recalculation then the update takes forever and the preogrss bar
repeats itself over and over. why should it operate so slowly.


Paul Man Posted on 2006-11-06 15:46:20.0Z
From: "Paul Man" <paulman@datasoft.ie>
Newsgroups: advantage.trigger
References: <454f497c@solutions.advantagedatabase.com>
Subject: Re: trigger performance intolerably slow
Date: Mon, 6 Nov 2006 15:46:20 -0000
Lines: 85
Organization: DSoft
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962
NNTP-Posting-Host: 82.141.233.142
Message-ID: <454f57f5@solutions.advantagedatabase.com>
X-Trace: 6 Nov 2006 08:42:45 -0700, 82.141.233.142
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!82.141.233.142
Xref: solutions.advantagedatabase.com Advantage.Trigger:279
Article PK: 1136346

Looking at some of the other posts, I tried using cursors in the trigger for
both the __new and the __old but it has seemingly made the trigger slower.
I suspect the primary cause is that the exchange rate is searched each time
for each cost and this might be an expensive operation. but still it seems
that script based triggers are not very practical at all.

"Paul Man" <paulman@datasoft.ie> wrote in message
news:454f497c@solutions.advantagedatabase.com...
>I have the following trigger that basically looks for changes to cost
>fields and recalulates and updates the real cost if there are changes.
>
> declare @homeval double;
> declare @carriage double;
> declare @duty double;
> declare @landed double;
> declare @currcode string;
> declare @exchRate double;
>
> declare @diff1 double;
> declare @diff2 double;
> declare @diff3 double;
>
> set @diff1 = (select currcost1 + carperc1 + duty1 from __new) - (select
> currcost1 +carperc1 + duty1 from __old);
> set @diff2 = (select currcost2 + carperc2 + duty2 from __new) - (select
> currcost2 +carperc2 + duty2 from __old);
> set @diff3 = (select currcost3 + carperc3 + duty3 from __new) - (select
> currcost3 +carperc3 + duty3 from __old);
>
> if isnull(@diff1, 1) <> 0 then
> set @currcode = (select currcode from placc where code = (select supp1
> from __new ));
> set @exchRate = isnull((SELECT ExchangeRate from NLCURRENCY where CODE=
> @currcode ), 1);
> set @homeVal = (select isnull(currcost1,0) * @exchRate from __new );
> set @duty = ( select isnull(duty1, 0) from __new );
> set @carriage = ( select isnull(CARPERC1, 0) from __new );
> set @carriage = @homeVal * @Carriage / 100.0;
> set @duty = @homeVal * @Duty / 100.0;
> set @Landed = @homeVal + @carriage + @duty;
> update skitem set cost1 = @landed where code = (select code from __new);
> end if;
>
>
> if isnull( @diff2, 1) <> 0 then
> set @currcode = (select currcode from placc where code = (select supp2
> from __new ));
> set @exchRate = isnull((SELECT ExchangeRate from NLCURRENCY where CODE=
> @currcode ), 1);
> set @homeVal = (select isnull(currcost2,0) * @exchRate from __new );
> set @duty = ( select isnull(duty2, 0) from __new );
> set @carriage = ( select isnull(CARPERC2, 0) from __new );
> set @carriage = @homeVal * @Carriage / 100.0;
> set @duty = @homeVal * @Duty / 100.0;
> set @Landed = @homeVal + @carriage + @duty;
> update skitem set cost2 = @landed where code = (select code from __new);
> end if;
>
>
> if isnull( @diff3, 1) <> 0 then
> set @currcode = (select currcode from placc where code = (select supp3
> from __new ));
> set @exchRate = isnull((SELECT ExchangeRate from NLCURRENCY where CODE=
> @currcode ), 1);
> set @homeVal = (select isnull(currcost3,0) * @exchRate from __new );
> set @duty = ( select isnull(duty3, 0) from __new );
> set @carriage = ( select isnull(CARPERC3, 0) from __new );
> set @carriage = @homeVal * @Carriage / 100.0;
> set @duty = @homeVal * @Duty / 100.0;
> set @Landed = @homeVal + @carriage + @duty;
> update skitem set cost3 = @landed where code = (select code from __new);
> end if;
>
>
> If I run a sql like
>
> update skitem set currcost1 = currcost1+1,
> currcost2 = currcost2+1,
> currcost3 = currcost3+1;
>
> to force a recalculation then the update takes forever and the preogrss
> bar repeats itself over and over. why should it operate so slowly.
>


Lance Schmidt Posted on 2006-11-06 23:28:24.0Z
From: "Lance Schmidt" <LanceSc@ExtendSys.com>
Newsgroups: advantage.trigger
References: <454f497c@solutions.advantagedatabase.com> <454f57f5@solutions.advantagedatabase.com>
Subject: Re: trigger performance intolerably slow
Date: Mon, 6 Nov 2006 16:28:24 -0700
Lines: 200
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962
NNTP-Posting-Host: 10.24.38.197
Message-ID: <454fc422@solutions.advantagedatabase.com>
X-Trace: 6 Nov 2006 16:24:18 -0700, 10.24.38.197
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!10.24.38.197
Xref: solutions.advantagedatabase.com Advantage.Trigger:283
Article PK: 1136349

Hi Paul,

Here is a version of your script that I re-wrote to get significantly better
performance. A large part of the problem was the two SELECT statements to
get the exchange rate.

-- BEGIN TRIGGER BODY
*******************************************************
declare @homeval double;
declare @carriage double;
declare @duty double;
declare @landed1 double;
declare @landed2 double;
declare @landed3 double;
declare @currcode INTEGER;
declare @exchRate double;

declare @diff1 double;
declare @diff2 double;
declare @diff3 double;
declare c cursor;

DECLARE NewValues CURSOR AS SELECT * FROM __NEW;
DECLARE OldValues CURSOR AS SELECT * FROM __OLD;
OPEN NewValues;
FETCH NewValues;

OPEN OldValues;
FETCH OldValues;

set @diff1 = (NewValues.currcost1 + NewValues.carperc1 + NewValues.duty1) -
(OldValues.currcost1 + OldValues.carperc1 + OldValues.duty1);
set @diff2 = (NewValues.currcost2 + NewValues.carperc2 + NewValues.duty2) -
(OldValues.currcost2 + OldValues.carperc2 + OldValues.duty2);
set @diff3 = (NewValues.currcost3 + NewValues.carperc3 + NewValues.duty3) -
(OldValues.currcost3 + OldValues.carperc3 + OldValues.duty3);

if isnull(@diff1, 1) <> 0 then

// Use cursor to get the value
open c as SELECT ExchangeRate from NLCURRENCY LEFT OUTER JOIN placc ON
NLCURRENCY.code = placc.currcode WHERE NewValues.supp1 = placc.code;
try
fetch c;
set @exchRate = isnull( c.ExchangeRate, 0 );
finally
close c;
end;

set @homeVal = (isnull( NewValues.currcost1,0) * @exchRate );
set @duty = isnull( NewValues.duty1, 0 );
set @carriage = isnull(NewValues.CARPERC1, 0);
set @carriage = @homeVal * @Carriage / 100.0;
set @duty = @homeVal * @Duty / 100.0;
set @Landed1 = @homeVal + @carriage + @duty;
ELSE
set @Landed1 = NewValues.cost1;
end if;


if isnull( @diff2, 1) <> 0 then

// Use cursor to get the value
open c as SELECT ExchangeRate from NLCURRENCY LEFT OUTER JOIN placc ON
NLCURRENCY.code = placc.currcode WHERE NewValues.supp2 = placc.code;
try
fetch c;
set @exchRate = isnull( c.ExchangeRate, 0 );
finally
close c;
end;
set @homeVal = isnull( NewValues.currcost2,0) * @exchRate;
set @duty = isnull( NewValues.duty2, 0);
set @carriage = isnull( NewValues.CARPERC2, 0);
set @carriage = @homeVal * @Carriage / 100.0;
set @duty = @homeVal * @Duty / 100.0;
set @Landed2 = @homeVal + @carriage + @duty;
ELSE
set @Landed2 = NewValues.cost2;
end if;


if isnull( @diff3, 1) <> 0 then
// Use cursor to get the value
open c as SELECT ExchangeRate from NLCURRENCY LEFT OUTER JOIN placc ON
NLCURRENCY.code = placc.currcode WHERE NewValues.supp3 = placc.code;
try
fetch c;
set @exchRate = isnull( c.ExchangeRate, 0 );
finally
close c;
end;
set @homeVal = isnull( NewValues.currcost3,0) * @exchRate ;
set @duty = isnull( NewValues.duty3, 0);
set @carriage = isnull( NewValues.CARPERC3, 0);
set @carriage = @homeVal * @Carriage / 100.0;
set @duty = @homeVal * @Duty / 100.0;
set @Landed3 = @homeVal + @carriage + @duty;
ELSE
set @Landed3 = NewValues.cost3;
end if;

update skitem set cost1 = @landed1, cost2 = @landed2, cost3 = @landed3 where
code = NewValues.code;
-- END TRIGGER BODY *******************************************************


Best Regards,

Lance Schmidt
Advantage R&D

"Paul Man" <paulman@datasoft.ie> wrote in message
news:454f57f5@solutions.advantagedatabase.com...
> Looking at some of the other posts, I tried using cursors in the trigger
> for both the __new and the __old but it has seemingly made the trigger
> slower. I suspect the primary cause is that the exchange rate is searched
> each time for each cost and this might be an expensive operation. but
> still it seems that script based triggers are not very practical at all.
>
> "Paul Man" <paulman@datasoft.ie> wrote in message
> news:454f497c@solutions.advantagedatabase.com...
>>I have the following trigger that basically looks for changes to cost
>>fields and recalulates and updates the real cost if there are changes.
>>
>> declare @homeval double;
>> declare @carriage double;
>> declare @duty double;
>> declare @landed double;
>> declare @currcode string;
>> declare @exchRate double;
>>
>> declare @diff1 double;
>> declare @diff2 double;
>> declare @diff3 double;
>>
>> set @diff1 = (select currcost1 + carperc1 + duty1 from __new) - (select
>> currcost1 +carperc1 + duty1 from __old);
>> set @diff2 = (select currcost2 + carperc2 + duty2 from __new) - (select
>> currcost2 +carperc2 + duty2 from __old);
>> set @diff3 = (select currcost3 + carperc3 + duty3 from __new) - (select
>> currcost3 +carperc3 + duty3 from __old);
>>
>> if isnull(@diff1, 1) <> 0 then
>> set @currcode = (select currcode from placc where code = (select supp1
>> from __new ));
>> set @exchRate = isnull((SELECT ExchangeRate from NLCURRENCY where CODE=
>> @currcode ), 1);
>> set @homeVal = (select isnull(currcost1,0) * @exchRate from __new );
>> set @duty = ( select isnull(duty1, 0) from __new );
>> set @carriage = ( select isnull(CARPERC1, 0) from __new );
>> set @carriage = @homeVal * @Carriage / 100.0;
>> set @duty = @homeVal * @Duty / 100.0;
>> set @Landed = @homeVal + @carriage + @duty;
>> update skitem set cost1 = @landed where code = (select code from __new);
>> end if;
>>
>>
>> if isnull( @diff2, 1) <> 0 then
>> set @currcode = (select currcode from placc where code = (select supp2
>> from __new ));
>> set @exchRate = isnull((SELECT ExchangeRate from NLCURRENCY where CODE=
>> @currcode ), 1);
>> set @homeVal = (select isnull(currcost2,0) * @exchRate from __new );
>> set @duty = ( select isnull(duty2, 0) from __new );
>> set @carriage = ( select isnull(CARPERC2, 0) from __new );
>> set @carriage = @homeVal * @Carriage / 100.0;
>> set @duty = @homeVal * @Duty / 100.0;
>> set @Landed = @homeVal + @carriage + @duty;
>> update skitem set cost2 = @landed where code = (select code from __new);
>> end if;
>>
>>
>> if isnull( @diff3, 1) <> 0 then
>> set @currcode = (select currcode from placc where code = (select supp3
>> from __new ));
>> set @exchRate = isnull((SELECT ExchangeRate from NLCURRENCY where CODE=
>> @currcode ), 1);
>> set @homeVal = (select isnull(currcost3,0) * @exchRate from __new );
>> set @duty = ( select isnull(duty3, 0) from __new );
>> set @carriage = ( select isnull(CARPERC3, 0) from __new );
>> set @carriage = @homeVal * @Carriage / 100.0;
>> set @duty = @homeVal * @Duty / 100.0;
>> set @Landed = @homeVal + @carriage + @duty;
>> update skitem set cost3 = @landed where code = (select code from __new);
>> end if;
>>
>>
>> If I run a sql like
>>
>> update skitem set currcost1 = currcost1+1,
>> currcost2 = currcost2+1,
>> currcost3 = currcost3+1;
>>
>> to force a recalculation then the update takes forever and the preogrss
>> bar repeats itself over and over. why should it operate so slowly.
>>
>
>


"Alex Wong \(ADS\)" <alexw Posted on 2006-11-06 20:43:28.0Z
From: "Alex Wong \(ADS\)" <alexw@*n_o__s_p_a_m*sybase.com>
Newsgroups: advantage.trigger
References: <454f497c@solutions.advantagedatabase.com>
Subject: Re: trigger performance intolerably slow
Date: Mon, 6 Nov 2006 13:43:28 -0700
Lines: 122
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962
NNTP-Posting-Host: 10.24.38.183
Message-ID: <454f9d6e@solutions.advantagedatabase.com>
X-Trace: 6 Nov 2006 13:39:10 -0700, 10.24.38.183
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!10.24.38.183
Xref: solutions.advantagedatabase.com Advantage.Trigger:281
Article PK: 1136347

Hi Paul,

Trigger does add some overhead but I think you may be hitting a known
performance issue that we recently discovered. The bug affects complex
expression in the assignment and conditional statements in the SQL script.
The evaluation of the complex expression is not optimized. The definition of
complex expression in this case is any expression involving a subquery. An
example from your script is:

> set @currcode = (select currcode from placc where code = (select supp1
> from __new ));

The expression on the right hand side will be very slow because the index on
the "code" field is not used. The work around for the moment will be to use
a cursor instead of direct assignment using subquery.

// Add this line to the declare section
declare c cursor;

// Use cursor to get the value
open c as select currcode from placc where code = (select supp1 from
__new );
try
fetch c;
set @currcode = c.currcode;
finally
close c;
end;

You have to do something similar with the following assignments of the
@exchRate as well:
> set @exchRate = isnull((SELECT ExchangeRate from NLCURRENCY where CODE=
> @currcode ), 1);

We will have this issue fixed in the next server update. Please let us know
if the workaroudn does not fix your problem.

-Alex





"Paul Man" <paulman@datasoft.ie> wrote in message
news:454f497c@solutions.advantagedatabase.com...
>I have the following trigger that basically looks for changes to cost
>fields and recalulates and updates the real cost if there are changes.
>
> declare @homeval double;
> declare @carriage double;
> declare @duty double;
> declare @landed double;
> declare @currcode string;
> declare @exchRate double;
>
> declare @diff1 double;
> declare @diff2 double;
> declare @diff3 double;
>
> set @diff1 = (select currcost1 + carperc1 + duty1 from __new) - (select
> currcost1 +carperc1 + duty1 from __old);
> set @diff2 = (select currcost2 + carperc2 + duty2 from __new) - (select
> currcost2 +carperc2 + duty2 from __old);
> set @diff3 = (select currcost3 + carperc3 + duty3 from __new) - (select
> currcost3 +carperc3 + duty3 from __old);
>
> if isnull(@diff1, 1) <> 0 then
> set @currcode = (select currcode from placc where code = (select supp1
> from __new ));
> set @exchRate = isnull((SELECT ExchangeRate from NLCURRENCY where CODE=
> @currcode ), 1);
> set @homeVal = (select isnull(currcost1,0) * @exchRate from __new );
> set @duty = ( select isnull(duty1, 0) from __new );
> set @carriage = ( select isnull(CARPERC1, 0) from __new );
> set @carriage = @homeVal * @Carriage / 100.0;
> set @duty = @homeVal * @Duty / 100.0;
> set @Landed = @homeVal + @carriage + @duty;
> update skitem set cost1 = @landed where code = (select code from __new);
> end if;
>
>
> if isnull( @diff2, 1) <> 0 then
> set @currcode = (select currcode from placc where code = (select supp2
> from __new ));
> set @exchRate = isnull((SELECT ExchangeRate from NLCURRENCY where CODE=
> @currcode ), 1);
> set @homeVal = (select isnull(currcost2,0) * @exchRate from __new );
> set @duty = ( select isnull(duty2, 0) from __new );
> set @carriage = ( select isnull(CARPERC2, 0) from __new );
> set @carriage = @homeVal * @Carriage / 100.0;
> set @duty = @homeVal * @Duty / 100.0;
> set @Landed = @homeVal + @carriage + @duty;
> update skitem set cost2 = @landed where code = (select code from __new);
> end if;
>
>
> if isnull( @diff3, 1) <> 0 then
> set @currcode = (select currcode from placc where code = (select supp3
> from __new ));
> set @exchRate = isnull((SELECT ExchangeRate from NLCURRENCY where CODE=
> @currcode ), 1);
> set @homeVal = (select isnull(currcost3,0) * @exchRate from __new );
> set @duty = ( select isnull(duty3, 0) from __new );
> set @carriage = ( select isnull(CARPERC3, 0) from __new );
> set @carriage = @homeVal * @Carriage / 100.0;
> set @duty = @homeVal * @Duty / 100.0;
> set @Landed = @homeVal + @carriage + @duty;
> update skitem set cost3 = @landed where code = (select code from __new);
> end if;
>
>
> If I run a sql like
>
> update skitem set currcost1 = currcost1+1,
> currcost2 = currcost2+1,
> currcost3 = currcost3+1;
>
> to force a recalculation then the update takes forever and the preogrss
> bar repeats itself over and over. why should it operate so slowly.
>


Paul Man Posted on 2006-11-09 17:03:54.0Z
From: "Paul Man" <paulman@datasoft.ie>
Newsgroups: advantage.trigger
References: <454f497c@solutions.advantagedatabase.com> <454f9d6e@solutions.advantagedatabase.com>
Subject: Re: trigger performance intolerably slow
Date: Thu, 9 Nov 2006 17:03:54 -0000
Lines: 138
Organization: DSoft
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: 82.141.233.142
Message-ID: <45535ea3@solutions.advantagedatabase.com>
X-Trace: 9 Nov 2006 10:00:19 -0700, 82.141.233.142
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!82.141.233.142
Xref: solutions.advantagedatabase.com Advantage.Trigger:286
Article PK: 1136352

With the changes Lance suggested it is feasible for a single record update.
However if I do an update across the board like:

update skitem set currcost=currcost+1

then that would take ages.

I'll try this as yuo suggest...
I wonder would a native Delphi trigger operate faster. I could then cache
the Exchange rates as they do not change very often. Whats the typical
performance ratio of script to native Delphi?

"Alex Wong (ADS)" <alexw@*n_o__s_p_a_m*sybase.com> wrote in message
news:454f9d6e@solutions.advantagedatabase.com...
> Hi Paul,
>
> Trigger does add some overhead but I think you may be hitting a known
> performance issue that we recently discovered. The bug affects complex
> expression in the assignment and conditional statements in the SQL script.
> The evaluation of the complex expression is not optimized. The definition
> of complex expression in this case is any expression involving a subquery.
> An example from your script is:
>
>> set @currcode = (select currcode from placc where code = (select supp1
>> from __new ));
>
> The expression on the right hand side will be very slow because the index
> on the "code" field is not used. The work around for the moment will be to
> use a cursor instead of direct assignment using subquery.
>
> // Add this line to the declare section
> declare c cursor;
>
> // Use cursor to get the value
> open c as select currcode from placc where code = (select supp1 from
> __new );
> try
> fetch c;
> set @currcode = c.currcode;
> finally
> close c;
> end;
>
> You have to do something similar with the following assignments of the
> @exchRate as well:
>> set @exchRate = isnull((SELECT ExchangeRate from NLCURRENCY where CODE=
>> @currcode ), 1);
>
> We will have this issue fixed in the next server update. Please let us
> know if the workaroudn does not fix your problem.
>
> -Alex
>
>
>
>
>
> "Paul Man" <paulman@datasoft.ie> wrote in message
> news:454f497c@solutions.advantagedatabase.com...
>>I have the following trigger that basically looks for changes to cost
>>fields and recalulates and updates the real cost if there are changes.
>>
>> declare @homeval double;
>> declare @carriage double;
>> declare @duty double;
>> declare @landed double;
>> declare @currcode string;
>> declare @exchRate double;
>>
>> declare @diff1 double;
>> declare @diff2 double;
>> declare @diff3 double;
>>
>> set @diff1 = (select currcost1 + carperc1 + duty1 from __new) - (select
>> currcost1 +carperc1 + duty1 from __old);
>> set @diff2 = (select currcost2 + carperc2 + duty2 from __new) - (select
>> currcost2 +carperc2 + duty2 from __old);
>> set @diff3 = (select currcost3 + carperc3 + duty3 from __new) - (select
>> currcost3 +carperc3 + duty3 from __old);
>>
>> if isnull(@diff1, 1) <> 0 then
>> set @currcode = (select currcode from placc where code = (select supp1
>> from __new ));
>> set @exchRate = isnull((SELECT ExchangeRate from NLCURRENCY where CODE=
>> @currcode ), 1);
>> set @homeVal = (select isnull(currcost1,0) * @exchRate from __new );
>> set @duty = ( select isnull(duty1, 0) from __new );
>> set @carriage = ( select isnull(CARPERC1, 0) from __new );
>> set @carriage = @homeVal * @Carriage / 100.0;
>> set @duty = @homeVal * @Duty / 100.0;
>> set @Landed = @homeVal + @carriage + @duty;
>> update skitem set cost1 = @landed where code = (select code from __new);
>> end if;
>>
>>
>> if isnull( @diff2, 1) <> 0 then
>> set @currcode = (select currcode from placc where code = (select supp2
>> from __new ));
>> set @exchRate = isnull((SELECT ExchangeRate from NLCURRENCY where CODE=
>> @currcode ), 1);
>> set @homeVal = (select isnull(currcost2,0) * @exchRate from __new );
>> set @duty = ( select isnull(duty2, 0) from __new );
>> set @carriage = ( select isnull(CARPERC2, 0) from __new );
>> set @carriage = @homeVal * @Carriage / 100.0;
>> set @duty = @homeVal * @Duty / 100.0;
>> set @Landed = @homeVal + @carriage + @duty;
>> update skitem set cost2 = @landed where code = (select code from __new);
>> end if;
>>
>>
>> if isnull( @diff3, 1) <> 0 then
>> set @currcode = (select currcode from placc where code = (select supp3
>> from __new ));
>> set @exchRate = isnull((SELECT ExchangeRate from NLCURRENCY where CODE=
>> @currcode ), 1);
>> set @homeVal = (select isnull(currcost3,0) * @exchRate from __new );
>> set @duty = ( select isnull(duty3, 0) from __new );
>> set @carriage = ( select isnull(CARPERC3, 0) from __new );
>> set @carriage = @homeVal * @Carriage / 100.0;
>> set @duty = @homeVal * @Duty / 100.0;
>> set @Landed = @homeVal + @carriage + @duty;
>> update skitem set cost3 = @landed where code = (select code from __new);
>> end if;
>>
>>
>> If I run a sql like
>>
>> update skitem set currcost1 = currcost1+1,
>> currcost2 = currcost2+1,
>> currcost3 = currcost3+1;
>>
>> to force a recalculation then the update takes forever and the preogrss
>> bar repeats itself over and over. why should it operate so slowly.
>>
>
>


"Alex Wong \(ADS\)" <alexw Posted on 2006-11-09 21:05:08.0Z
From: "Alex Wong \(ADS\)" <alexw@*n_o__s_p_a_m*sybase.com>
Newsgroups: advantage.trigger
References: <454f497c@solutions.advantagedatabase.com> <454f9d6e@solutions.advantagedatabase.com> <45535ea3@solutions.advantagedatabase.com>
Subject: Re: trigger performance intolerably slow
Date: Thu, 9 Nov 2006 14:05:08 -0700
Lines: 161
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: 10.24.38.183
Message-ID: <4553970c@solutions.advantagedatabase.com>
X-Trace: 9 Nov 2006 14:01:00 -0700, 10.24.38.183
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!10.24.38.183
Xref: solutions.advantagedatabase.com Advantage.Trigger:288
Article PK: 1136353

Are you on 8.0 or 8.1 release of the server?

In 8.0 release, each time the trigger is executed, the script is parsed and
prepared and that can lead to poor performance. In 8.1, the script is parsed
and prepared once so you should see much better performance with 8.1
release. Lance and I both tested the sample script and we found that
updating 100 rows takes about 800ms in our environment with the trigger. Of
course, if your table is significantly larger, the time will be
proportionally longer.

A nativie Delphi trigger may be faster if you don't use any query component
in it. It is probably impossible to give a specific ratio because the result
will vary greatly depending on what you do in the trigger and how you do it.

--
Alex

"Paul Man" <paulman@datasoft.ie> wrote in message
news:45535ea3@solutions.advantagedatabase.com...
> With the changes Lance suggested it is feasible for a single record
> update. However if I do an update across the board like:
>
> update skitem set currcost=currcost+1
>
> then that would take ages.
>
> I'll try this as yuo suggest...
> I wonder would a native Delphi trigger operate faster. I could then cache
> the Exchange rates as they do not change very often. Whats the typical
> performance ratio of script to native Delphi?
>
> "Alex Wong (ADS)" <alexw@*n_o__s_p_a_m*sybase.com> wrote in message
> news:454f9d6e@solutions.advantagedatabase.com...
>> Hi Paul,
>>
>> Trigger does add some overhead but I think you may be hitting a known
>> performance issue that we recently discovered. The bug affects complex
>> expression in the assignment and conditional statements in the SQL
>> script. The evaluation of the complex expression is not optimized. The
>> definition of complex expression in this case is any expression involving
>> a subquery. An example from your script is:
>>
>>> set @currcode = (select currcode from placc where code = (select supp1
>>> from __new ));
>>
>> The expression on the right hand side will be very slow because the index
>> on the "code" field is not used. The work around for the moment will be
>> to use a cursor instead of direct assignment using subquery.
>>
>> // Add this line to the declare section
>> declare c cursor;
>>
>> // Use cursor to get the value
>> open c as select currcode from placc where code = (select supp1 from
>> __new );
>> try
>> fetch c;
>> set @currcode = c.currcode;
>> finally
>> close c;
>> end;
>>
>> You have to do something similar with the following assignments of the
>> @exchRate as well:
>>> set @exchRate = isnull((SELECT ExchangeRate from NLCURRENCY where CODE=
>>> @currcode ), 1);
>>
>> We will have this issue fixed in the next server update. Please let us
>> know if the workaroudn does not fix your problem.
>>
>> -Alex
>>
>>
>>
>>
>>
>> "Paul Man" <paulman@datasoft.ie> wrote in message
>> news:454f497c@solutions.advantagedatabase.com...
>>>I have the following trigger that basically looks for changes to cost
>>>fields and recalulates and updates the real cost if there are changes.
>>>
>>> declare @homeval double;
>>> declare @carriage double;
>>> declare @duty double;
>>> declare @landed double;
>>> declare @currcode string;
>>> declare @exchRate double;
>>>
>>> declare @diff1 double;
>>> declare @diff2 double;
>>> declare @diff3 double;
>>>
>>> set @diff1 = (select currcost1 + carperc1 + duty1 from __new) - (select
>>> currcost1 +carperc1 + duty1 from __old);
>>> set @diff2 = (select currcost2 + carperc2 + duty2 from __new) - (select
>>> currcost2 +carperc2 + duty2 from __old);
>>> set @diff3 = (select currcost3 + carperc3 + duty3 from __new) - (select
>>> currcost3 +carperc3 + duty3 from __old);
>>>
>>> if isnull(@diff1, 1) <> 0 then
>>> set @currcode = (select currcode from placc where code = (select supp1
>>> from __new ));
>>> set @exchRate = isnull((SELECT ExchangeRate from NLCURRENCY where CODE=
>>> @currcode ), 1);
>>> set @homeVal = (select isnull(currcost1,0) * @exchRate from __new );
>>> set @duty = ( select isnull(duty1, 0) from __new );
>>> set @carriage = ( select isnull(CARPERC1, 0) from __new );
>>> set @carriage = @homeVal * @Carriage / 100.0;
>>> set @duty = @homeVal * @Duty / 100.0;
>>> set @Landed = @homeVal + @carriage + @duty;
>>> update skitem set cost1 = @landed where code = (select code from __new);
>>> end if;
>>>
>>>
>>> if isnull( @diff2, 1) <> 0 then
>>> set @currcode = (select currcode from placc where code = (select supp2
>>> from __new ));
>>> set @exchRate = isnull((SELECT ExchangeRate from NLCURRENCY where CODE=
>>> @currcode ), 1);
>>> set @homeVal = (select isnull(currcost2,0) * @exchRate from __new );
>>> set @duty = ( select isnull(duty2, 0) from __new );
>>> set @carriage = ( select isnull(CARPERC2, 0) from __new );
>>> set @carriage = @homeVal * @Carriage / 100.0;
>>> set @duty = @homeVal * @Duty / 100.0;
>>> set @Landed = @homeVal + @carriage + @duty;
>>> update skitem set cost2 = @landed where code = (select code from __new);
>>> end if;
>>>
>>>
>>> if isnull( @diff3, 1) <> 0 then
>>> set @currcode = (select currcode from placc where code = (select supp3
>>> from __new ));
>>> set @exchRate = isnull((SELECT ExchangeRate from NLCURRENCY where CODE=
>>> @currcode ), 1);
>>> set @homeVal = (select isnull(currcost3,0) * @exchRate from __new );
>>> set @duty = ( select isnull(duty3, 0) from __new );
>>> set @carriage = ( select isnull(CARPERC3, 0) from __new );
>>> set @carriage = @homeVal * @Carriage / 100.0;
>>> set @duty = @homeVal * @Duty / 100.0;
>>> set @Landed = @homeVal + @carriage + @duty;
>>> update skitem set cost3 = @landed where code = (select code from __new);
>>> end if;
>>>
>>>
>>> If I run a sql like
>>>
>>> update skitem set currcost1 = currcost1+1,
>>> currcost2 = currcost2+1,
>>> currcost3 = currcost3+1;
>>>
>>> to force a recalculation then the update takes forever and the preogrss
>>> bar repeats itself over and over. why should it operate so slowly.
>>>
>>
>>
>
>