I need help with msAccess database lazarus / 16-Databases ---------------------------------------------------------------------- magleft · 2014-09-15 09:01 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 · 2014-09-15 09:03 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 · 2014-09-15 09:14 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 · 2014-09-15 09:34 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 · 2014-09-15 11:33 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 · 2014-09-15 11:58 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 · 2014-09-15 12:04 Yes, Code: [Select]QAccessTable.UniDirectional:=True; see http://wiki.lazarus.freepascal.org/Working_With_TSQLQuery#Out_of_memor y_errors Not sure about the packetrecords; perhaps tweaking that may help as well.. ---------------------------------------------------------------------- magleft · 2014-09-15 12:31 Thanks. I wil try. ---------------------------------------------------------------------- BigChimp · 2014-09-15 12:31 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_queri es ---------------------------------------------------------------------- Jkey · 2014-09-15 13:46 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+StrLis ts[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 · 2014-09-15 14:10 Quote from: magleft on September 15, 2014, 02:31:06 pmThanks. 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 select (, , ..... from SourceTable where source conditions required; sql command to instruct the database to move data from one table the other internally. ---------------------------------------------------------------------- Jkey · 2014-09-15 14:35 Quote from: taazz on September 15, 2014, 04:10:30 pmI would suggest to cut of the middle man (ee the network and your program) altogether use the Code: [Select]insert into select (, , ..... 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 · 2014-09-15 19:16 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!!!