>From the description I'd guess its something (say a stringlist) not being cleared between calls so things slow down because there's more to do.

I haven't done any experimenting but if it was caused by transactions I'd expect either a deadlock or transaction rollback on error.<

So it's the session that's slowing down, not the server? I thought it was the server. After each Insert, the trigger executes a lot more SQL statements plus it modifies the same table that just got the row inserted. If all of this is done inside of a transaction, then I'd expect a lot of waiting for table locks if the trigger can't be completed by the time another insert is initiated by another user. If this is the case, then all users should slow down at the same time when inserting rows into the table. Terminating a session will speed things up for the other users.

But if the slowdown is with a single client because of a memory leak, then I'd recommend getting EurekaLog to find the problem. Even their eval will find the problem pretty quick.

Also the client should be using a restricted transaction so transactions only involves the table(s) that are being updated, otherwise every table in the database will be in the transaction.

BTW, I sent you an email Saturday to newcontact at your nlh address. Did you get it or should I try something else?

Barry

Barry


>BTW, I sent you an email Saturday to newcontact at your nlh address. Did you get it or should I try something else?

Got it, replied to it. If it doesn't turn up soon let me know.

As I asked in the email why not use the email address I use here?

Roy

Barry,

I tested the issue with only one connection to the server, executing a script from EDB Manager and the problem persisted.

I rewrote a procedure segment to remove the calls to the functions and performance is up and the leak appears to be a lot less now. The functions did't make any modifications, just SELECT's, so no locks to be had. The rest of the procedure remains the same.

It is my conclusion that there is some flaw in the way functions calls are deallocated by the engine.

I'll monitor the behaviour and report back.

Thank you all for your time and support.

Regards,
Mario

Mario


You keep using the word "leak" but I'm far from sure that's what's happening. Can you post a test system into the binaries so that we can have a play with it?

Roy Lambert [Team Elevate]

+1

I'm wondering if the trigger is creating a circular call. That's why I mentioned in my earlier post to log messages to see how many procedures are actually executed. For example, if the trigger was accidentally defined as "After Insert/Update" instead of "After Insert" and the trigger directly or indirectly updates a record of the same table, then the trigger will be called again for a different record, ad-infinitum or until a condition prevents another update of the record.

By putting a "Set Log Message to 'Function abc -START'" at the start of each function, and one at the end of each function "Set Log Message to "Function abc -END" you'll see the cascading effect of the function calls.

Barry

Barry,

<< I'm wondering if the trigger is creating a circular call. That's why I
mentioned in my earlier post to log messages to see how many procedures are
actually executed. For example, if the trigger was accidentally defined as
"After Insert/Update" instead of "After Insert" and the trigger directly or
indirectly updates a record of the same table, then the trigger will be
called again for a different record, ad-infinitum or until a condition
prevents another update of the record.  >>

Just coming in here, but yes, this could definitely cause some issues,
especially if, as you say, the transactions start to pile up because of it.
EDB will happily allow you to keep triggering the same trigger over and over
again, and will simply keep pushing the current context on to a stack for
each level of execution.

Tim Young
Elevate Software
www.elevatesoft.com

Mario,

<< There's an After Insert trigger that calls a procedure that in turn call
several functions and runs lots of EXECUTE IMMEDIATE's (Selects, Inserts,
Updates), and one or two cursor as well, all within the context of a
transaction.

The first time it is executed it runs fine, but with every subsequent run it
grows slower and slower until the whole operation comes to a halt. To test
this, I wrote an script to call the above mentioned procedure several time
in a loop to reproduce it. >>

If you can, please send me the script that you're using to reproduce the
problem along with a database that can be used to reproduce it.  If it is
indeed a bug, then I would like to get it fixed before the next EDB release,
which is coming out soon.

Thanks,

Tim Young
Elevate Software
www.elevatesoft.com

Roy Lambert wrote:

Mario


You keep using the word "leak" but I'm far from sure that's what's happening. Can you post a test system into the binaries so that we can have a play with it?

Roy Lambert [Team Elevate]

Roy,

If anyone is willing to take a look I would gladly prepare and post a test system.

I keep using  of the word "leak" because it appears the server is withholding resources that should be deallocated, to my understanding that is. Even thought is quite possible I might be miss using some EDB features  

regards,
Mario

Tim,

I would post a test system, just give me a day or two please.

Regards,
Mario

Mario,

<< I would post a test system, just give me a day or two please. >>

No problem.

Thanks,

Tim Young
Elevate Software
www.elevatesoft.com