Tim

><< I know you HATE these sort of questions but any rough idea as to a % >>
>
>There's no possible way that I could answer that. It depends upon what the
>original queries look like, and what the pre-prepared queries look like, and
>then what is done with the pre-prepared queries.

Hmmm. What we need is some nice easy way to instrument these things so we can, for our specific cases, get some real data on performance. The only way I can think of, apart from timing a process en bloc is to have another table and store info in there, probably using a trigger Any better suggestions welcome.

Roy Lambert

Roy.

I've made tests with my trigger and curiosly takes the same time deleting 1000 rows with
the instruction

 PREPARE stmt FROM 'SELECT * FROM ASIENTOS WHERE ASIENTO=?';
 OPEN TempCursor USING OLDROW.ASIENTO;
 IF ROWCOUNT(TempCursor)>0 THEN
   DELETE FROM TempCursor;
 END IF;

and

EXECUTE IMMEDIATE 'DELETE FROM ASIENTOS WHERE ASIENTO = '+QUOTEDSTR(CAST(OLDROW.ASIENTO AS
VARCHAR));


I am just as at first then I'm thinking in remove transaction from trigger because execute
in each row and change to delphi code to execute transaction in the table range, will it
be better?

Thanks

Another idea.

There are any way to lock entire database in exclusive mode to handle files in exclusive
mode with delphi code and triggers?

Thanks again (I'm an educated guy, :P)

Francisco

>I've made tests with my trigger and curiosly takes the same time deleting 1000 rows with
>the instruction
>
> PREPARE stmt FROM 'SELECT * FROM ASIENTOS WHERE ASIENTO=?';
> OPEN TempCursor USING OLDROW.ASIENTO;
> IF ROWCOUNT(TempCursor)>0 THEN
> DELETE FROM TempCursor;
> END IF;
>
>and
>
>EXECUTE IMMEDIATE 'DELETE FROM ASIENTOS WHERE ASIENTO = '+QUOTEDSTR(CAST(OLDROW.ASIENTO AS
>VARCHAR));

I'm impressed with Tim's processing of the code in the trigger in that case.
>
>I am just as at first then I'm thinking in remove transaction from trigger because execute
>in each row and change to delphi code to execute transaction in the table range, will it
>be better?

Unfortunately I have no idea. The only way is to try it and see.

Roy Lambert [Team Elevate]

Francisco,

<< I am just as at first then I'm thinking in remove transaction from
trigger because execute in each row and change to delphi code to execute
transaction in the table range, will it be better? >>

Probably not.  ElevateDB already aggresively buffers any INSERT, UPDATE, or
DELETE statement, regardless of whether you use a transaction or not.  So,
the biggest cause of the performance lag is probably just the sheer amount
of work going on.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


><< I am just as at first then I'm thinking in remove transaction from
>trigger because execute in each row and change to delphi code to execute
>transaction in the table range, will it be better? >>
>
>Probably not. ElevateDB already aggresively buffers any INSERT, UPDATE, or
>DELETE statement, regardless of whether you use a transaction or not. So,
>the biggest cause of the performance lag is probably just the sheer amount
>of work going on.

Your response made me think - unless the trigger can be converted to blocks of sql that can operate on the tables as a whole rather than row by row, but looking at the trigger it would be a lot of work, and that's if it can be done at all.

Roy Lambert

Tim, Roy

Cache updates can be useful working with triggers?, where can I see how works?

Thanks

Francisco,

<< Cache updates can be useful working with triggers?, where can I see how
works? >>

You can't use cached updates in SQL routines currently, so they won't be
able to help you.

--
Tim Young
Elevate Software
www.elevatesoft.com