I need help with msAccess database

[magleft]

Hi. In my application, my databases  must be update from msAccess.
I try to do this with ODBC connection but is very slow.
Is any other way to do this?

Thanks
 

[BigChimp]

Are you sure it is the ODBC connection that is causing the slowness and not your code?
Is your code fast with other databases?

Perhaps you could fiddle with generating ADO bindings using the Lazarus ActiveX tools; IIRC there are some threads on the forum here.
Haven't tried that.

[magleft]

When tried with part (100 - 200) of records all is ok. if tried to work with all records (about 55000 records), my program need very long time to read the database.
 

[BigChimp]

Thank you but I don't thank that answers my questions?

Why don't you post the relevant code (please format it using [ code ] tags (remove the spaces after [ and before ]) like this

Code: [Select]

writeln('hello world');

Thanks.

[Jkey]

If I try to read 55000 records (for example with 10 columns) from an MS Access DB table using ODBC, it takes usually 690-700ms  to perform it, so it's quite fast. Doesn't your source code slow down the reading?

[magleft]

The Access Database have the follwing structure:

   CODE_FYTA INTEGER,
   Crops VARCHAR(255),
   KOD_FARMAK INTEGER,
   EDR_COD_KAT VARCHAR(2),
   EIDOS VARCHAR(30),
   EMPORIKHON VARCHAR(50),
   GBONOMA VARCHAR(50),
   APOSYRSH VARCHAR(1),
   TELOS_EGRI DATE,
   UN_DELETE BOOLEAN,
   UN_DATE DATE, 
   SOIL BOOLEAN

the code who write is

Code: [Select]

Begin
   QAccessTable.SQL.Text := 'select * from FARMAKO_CROP_SKEYASMATA';
   QAccessTable.PacketRecords :=50;
   QAccessTable.Open;
   While not(QAccessTable.EOF) do
   begin
      QFarmaka.SQL.Text :='INSERT INTO FARMAKA_EGRISEIS ('
                  +'EMPORICO'
                  +', ID_KWDKAL'
                  +', INFO_KAL'
                  +') VALUES ('
                    + QAccessTable.FieldByName('KOD_FARMAK').AsString
                  +','+QAccessTable.FieldByName('CODE_FYTA').AsString
                  +','+QuotedStr(QAccessTable.FieldByName('Crops').AsString)
                  +')';
      QFarmaka.ExecSQL ;
      TransFarmaka.CommitRetaining  ;
       Panel1.Caption :=formatFloat('0',QAccessTable.RecNo)+' / '+   
                                   formatFloat('0',QAccessTable.RecordCount);
      QAccessTable.Next ;
  end;
   QAccessTable.close ;
End;

Any idea to do this more faster?

[BigChimp]

Yes,

Code: [Select]

QAccessTable.UniDirectional:=True;

see
http://wiki.lazarus.freepascal.org/Working_With_TSQLQuery#Out_of_memory_errors

Not sure about the packetrecords; perhaps tweaking that may help as well..

[magleft]

Thanks. I wil try.

[BigChimp]

Oh, and moving the insert query SQL definition out of the loop and making it a parameterized query could help as well.
This way, Access can prepare the query once and run it within the loop instead of having to evaluate the SQL again each loop iteration.

See e.g.
http://wiki.lazarus.freepascal.org/SQLdb_Tutorial3#Parameterized_queries

[Jkey]

I would make two separate transactions: one for the SELECT, and one for the mass of INSERTs. Now it takes me 11,7 seconds to copy 3 columns of 55000 records from one table to another. Is it better than your speed?

Code: [Select]

const c = ''''; 
var StrLists: array of TStringList;
    i: integer;
begin
  SetLength(StrLists, 3);
  for i := 0 to Length(StrLists) - 1 do StrLists[i] := TStringList.Create;
  SQLTransaction.StartTransaction;
  SQLQuery.SQL.Text := 'SELECT KOD_FARMAK, CODE_FYTA, Crops  FROM FARMAKO_CROP_SKEYASMATA;';  // Open only as many columns as necessary
  SQLQuery.Open;
  SQLQuery.First;
  while not SQLQuery.EOF do
  begin
    for i := 0 to Length(StrLists) - 1 do  StrLists[i].Add(SQLQuery.Fields[i].AsString);
    SQLQuery.Next;
  end;
  SQLQuery.Close;
  SQLTransaction.Commit;
  SQLTransaction.StartTransaction;
  for i := 0 to StrLists[0].Count - 1 do
  begin
    SQLQuery.SQL.Text :='INSERT INTO FARMAKA_EGRISEIS (EMPORICO, ID_KWDKAL, INFO_KAL) VALUES ('+c+StrLists[0].Strings[i]+c+','+c+StrLists[1].Strings[i]+c+','+c+StrLists[2].Strings[i]+c+');';
    SQLQuery.ExecSQL;
  end;
  SQLTransaction.Commit;
  for i := 0 to Length(StrLists) - 1 do StrLists[i].Free;
end;

[taazz]

Thanks. I wil try.

Can I ask a simple question? are those 2 tables in the same database? Using the same connection and all? If yes then I would suggest to cut of the middle man (ee the network and your program) altogether use the

Code: [Select]

insert into <DestTable> select (<nil or desttable.field1>, <sourcetable.field2>, ..... from SourceTable where source conditions required; sql command to instruct the database to move data from one table the other internally.

[Jkey]

I would suggest to cut of the middle man (ee the network and your program) altogether use the

Code: [Select]

insert into <DestTable> select (<nil or desttable.field1>, <sourcetable.field2>, ..... from SourceTable where source conditions required; sql command to instruct the database to move data from one table the other internally.

Your suggestion results less than 300ms... This can be the quickest method, if appropriate for magleft.

[magleft]

Jkey:
     It really is what I was looking for. It is much faster.

taazz:
    No. The tables are in different database.


Thanks to all for help!!!