Equivalent of TMyLoader for UPDATE

[GuzunNicolae]

Hello

Thank you very much for your previous help.
But I have a small question now.

As I know TMyLoader is used to insert very fast tables into mysql.
Is there any equivalent to speed up a lot of UPDATE statements? Now I am using a simple TMyCommand. So each time I need to do the update I say "UPDATE table SET fiel1=:value WHERE id = :primarykey". I tried to use TMyScript hoping that it will be very fast, but it is the same TMyCommand, it executes the queries one by one.

[Antaeus]

MySQL lets inserting several records with a single INSERT statement. TMyLoader uses this possibility.
But there is no possibility to do the same for UPDATE statements.

To increase performance you can try to use preparation or execute several statements at the same time with TMyCommand. Also take a look at he "Increasing performance" topic in MyDAC help.

[eduardosic]

Hello

Thank you very much for your previous help.
But I have a small question now.

As I know TMyLoader is used to insert very fast tables into mysql.
Is there any equivalent to speed up a lot of UPDATE statements? Now I am using a simple TMyCommand. So each time I need to do the update I say "UPDATE table SET fiel1=:value WHERE id = :primarykey". I tried to use TMyScript hoping that it will be very fast, but it is the same TMyCommand, it executes the queries one by one.

Try this to increase Update/Insert with any Component

SET AUTOCOMMIT=0;
UPDATE TableName Set Field=value where Key=XX;
UPDATE TableName Set Field=value where Key=XX;
UPDATE TableName Set Field=value where Key=XX;
UPDATE TableName Set Field=value where Key=XX;
COMMIT;

it's work very fast.

[GuzunNicolae]

Hello

Thank you very much for your previous help.
But I have a small question now.

As I know TMyLoader is used to insert very fast tables into mysql.
Is there any equivalent to speed up a lot of UPDATE statements? Now I am using a simple TMyCommand. So each time I need to do the update I say "UPDATE table SET fiel1=:value WHERE id = :primarykey". I tried to use TMyScript hoping that it will be very fast, but it is the same TMyCommand, it executes the queries one by one.

Try this to increase Update/Insert with any Component

SET AUTOCOMMIT=0;
UPDATE TableName Set Field=value where Key=XX;
UPDATE TableName Set Field=value where Key=XX;
UPDATE TableName Set Field=value where Key=XX;
UPDATE TableName Set Field=value where Key=XX;
COMMIT;

it's work very fast.

Thank you.
But I found the following
START TRANSACTION;
UPDATE TableName Set Field=value where Key=XX;
UPDATE TableName Set Field=value where Key=XX;
UPDATE TableName Set Field=value where Key=XX;
UPDATE TableName Set Field=value where Key=XX;
COMMIT;

It works 100 times faster :p. I am very happy

How is START TRANSACTION different than AUTOCOMIT =0 ?

[eduardosic]

Hello

Thank you very much for your previous help.
But I have a small question now.

As I know TMyLoader is used to insert very fast tables into mysql.
Is there any equivalent to speed up a lot of UPDATE statements? Now I am using a simple TMyCommand. So each time I need to do the update I say "UPDATE table SET fiel1=:value WHERE id = :primarykey". I tried to use TMyScript hoping that it will be very fast, but it is the same TMyCommand, it executes the queries one by one.

Try this to increase Update/Insert with any Component

SET AUTOCOMMIT=0;
UPDATE TableName Set Field=value where Key=XX;
UPDATE TableName Set Field=value where Key=XX;
UPDATE TableName Set Field=value where Key=XX;
UPDATE TableName Set Field=value where Key=XX;
COMMIT;

it's work very fast.

Thank you.
But I found the following
START TRANSACTION;
UPDATE TableName Set Field=value where Key=XX;
UPDATE TableName Set Field=value where Key=XX;
UPDATE TableName Set Field=value where Key=XX;
UPDATE TableName Set Field=value where Key=XX;
COMMIT;

It works 100 times faster :p. I am very happy

How is START TRANSACTION different than AUTOCOMIT =0 ?

Good notice!!

setting SET FOREIGN_KEY_CHECKS=0; increase a speed of Inser/Update