SQLProcessor slow when handle thousand record

[bgzretry]

Dear Master,

Need help, I have table in mysql which has 978.828 records. When I try this script, the process is very slow (the application like hang up)

zsqlprocessor1.Clear;
zsqlprocessor1.Script.Text := 'update tbharga Set HARGA="' + inttostr(Harga) + '" where NamaRs="' + RS + '" && KODEPRODUK="' + KodeProduk + '"';
zsqlprocessor1.Execute;

How to resolve this case? because when I try to reduce the record, the process running smoothly and faster. Waiting for your reply

Regards
Bagus

[jpnuage]

Just a little question : why don't you use a ZQuery ? Try this perhaps

ZQuery := TZQuery;
Zquery := TZQuery.create(nil);
Zquery.SQL.Clear;
ZQuery.SQL.Add('update tbharga Set HARGA= :Harga where NamaRs=' + RS + ' && KODEPRODUK=' + KodeProduk );
ZQuery.OaralNyName ('Harga').AsInteger := Harga;
ZQuery.Active := true;

[bgzretry]

Hi jpnuage,

I've tried before using ZQuery, but I got error "Can not open a ResultSet"

[bgzretry]

Zquery1.SQL.Clear;
Zquery1.SQL.Add('update tbharga Set HARGA= :Harga where NamaRs="' + RS + '" && KODEPRODUK="' + KodeProduk + '"');
zquery1.ParamByName('Harga').AsInteger := Harga;
zquery1.ExecSQL;

I've tried above script, but still the process is very slow. Need advise

[EgonHugeist]

bgzretry,

did you tried a prepared Statement too?
Var
PrepStatement: IZPreparedStatement;
begin

PrepStatement := ZConnection.DbcConnection.PrepareStatement('update tbharga Set HARGA= :Harga where NamaRs=' + RS + ' && KODEPRODUK=' + KodeProduk ');
PrepStatement.Prepare;
PrepStatement.SetInt(0, Harga);

for I := 1 to Your.Count-1 do
begin
PrepStatement.SetInt(0, Harga);
....
end;

You can also use Batch-scripts with a TIntegerDynArray.

You can find all definitions in the ZDbcIntfs.pas

best regards

[mdaems]

bgzretry,
The only way to improve the performance is checking if the update statements use indexes.
Also, when NamaRs is a number field you shouldn't use quotes around the parameter in your query as they may make it impossible for the database server to use indexes.

Mark