Database Synchronizing (Speed Problem)

[arnix]

Hi,
My Desktop application that is using PostgreSQL needs time to time synchronize data to Pocket PC application that uses SQLite. All is working perfectly, but I would like to increase the speed of synchronization. Currently I'm using something like this:

Code: Select all

procedure TRapiCopyForm.GenerateUpdateDBForDevice(fn: String);
const
  DBStrings: array[0..2] of AnsiString =
    (
      'SELECT f0, f1, f2, f3 FROM tbl0',
      'SELECT f0, f1, f2 FROM tbl1',
      'SELECT f0, f1 FROM tbl2'
    );
  LDBStrings: array[0..2] of AnsiString =
    (
      'INSERT INTO tbl1 (f0, f1, f2, f3) VALUES (:v0, :v1, :v2, :v3)',
      'INSERT INTO tbl1 (f0, f1, f2) VALUES (:v0, :v1, :v2)',
      'INSERT INTO tbl2 (f0, f1) VALUES (:v0, :v1)'
    );
var
  i, j, pc, cnt: Integer;
begin
  cnt := Length(DBStrings);

  try
    LDBConnection.Disconnect;
  except
  end;
  LDBConnection.Catalog := fn;
  LDBConnection.Database := fn;
  LDBConnection.Connect;

// Q is a TZQuery Component that is connected to PostgreSQL DBConnection
// LQ is a TZQuery Component that is connected to SQLite LDBConnection

  for i := 0 to cnt - 1 do begin
    Q.Active := false;
    Q.SQL.Clear;
    Q.SQL.Add(DBStrings[i]);
    Q.Active := true;

    LQ.Active := false;
    LQ.SQL.Clear;
    LQ.SQL.Add(LDBStrings[i]);

    while not Q.Eof do begin
      pc := LQ.Params.Count;
      for j := 0 to pc - 1 do begin
        LQ.Params[j].Value := Q.Fields[j].Value;
      end;
      LQ.ExecSQL;
      Q.Next;
    end;

    Q.Active := false;
  end;

  LDBConnection.Disconnect;
end;

Is there any other way of copying data from one dataset to another, I think this loop is very slow, I need something like:

Code: Select all

INSERT INTO tbl1 (f0, f1, f2) SELECT ff0, ff1, ff2 FROM tbl2

but for the 2 tables are in 2 separte databases (PostgreSQL and SQLite).

Thank you.

[trupka]

Use COPY instead INSERT (see Postgres docs for details). Also, try to wrap all inserts inside one transaction.

[mdaems]

arnix,

You may want to use the automatic update statement generation from zeoslib. Not sure it would be quicker, but you can try. It's rougly the same, but maybe you skip some internal ExecSQL processing.

How to do it :
- Use 2 identical select statements in your ZQuery objects. (Eventually use where 1=2 at the LQ side to avod retrieval of existing ows)
- Open both queries
- LQ.Append; copy field values from Q->LQ; post; --> for every record of Q
- ApplyUpdates and commit at the end of the loop.
This way query LQ does al query generation for the inserting.

Mark

[arnix]

Sorry for late response.

trupka,

Use COPY instead INSERT (see Postgres docs for details)

I'm copying _from_ postgresql, not _to_.

Also, try to wrap all inserts inside one transaction

That one was a good advice, I also removed the progerss bar update routine after every ExecSQL, and now I do that after every 50 ExecSQL-s, so I don't know what was the main reason (not using transactions or much GUI updates), but after that the speed has icreased very much, thak you.

mdaems,
Thanx, I will try this way too.