Page 1 of 1
Equivalent of TMyLoader for UPDATE
Posted: Thu 22 Nov 2007 11:47
by 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.
Posted: Thu 22 Nov 2007 14:52
by 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.
Re: Equivalent of TMyLoader for UPDATE
Posted: Thu 22 Nov 2007 16:13
by eduardosic
GuzunNicolae wrote: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.
Re: Equivalent of TMyLoader for UPDATE
Posted: Thu 22 Nov 2007 17:29
by GuzunNicolae
eduardosic wrote:GuzunNicolae wrote: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 ?
Re: Equivalent of TMyLoader for UPDATE
Posted: Thu 22 Nov 2007 17:37
by eduardosic
GuzunNicolae wrote:eduardosic wrote:GuzunNicolae wrote: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