Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 10 of 10 total |
transaction.... slow the system? |
Mon, Jan 18 2010 6:09 AM | Permanent Link |
"Fabio Codebue @ GestionaleOpen" | I'm really near to the end of my apps with lazarus and WinCE I'm trying to improve speed... I'm importing from CSV file something like 15K record of 2 or 3 fields. It's need 2h to do it!!! Perhaps are there some transactions ? Fabio Codebue Gestionale Open srl |
Mon, Jan 18 2010 7:21 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Fabio
Do you have any triggers associated with the table? That's what I found really slowed things down. Roy Lambert |
Mon, Jan 18 2010 8:03 AM | Permanent Link |
"Fabio Codebue @ GestionaleOpen" | On Mon, 18 Jan 2010 12:21:35 +0000, Roy Lambert
<roy.lambert@skynet.co.uk> wrote: >Fabio > >Do you have any triggers associated with the table? That's what I found really slowed things down. > >Roy Lambert Noone triggers associated with the table. Only a primary key. Here the DDL 'CREATE TABLE "art"' '( "art_codice" VARCHAR(20) COLLATE "UNI", "tba_codice" VARCHAR(30) COLLATE "UNI", "descrizione1" VARCHAR(50) COLLATE "UNI", CONSTRAINT "art_pk" PRIMARY KEY ("art_codice", "tba_codice")); I read from a CSV (with readln) and put on the screen the record number... Probably if I put recno only after 30 or 40 or 50 recs... it's better... |
Mon, Jan 18 2010 8:31 AM | Permanent Link |
Uli Becker | Fabio,
I don't know neither Lazarus nor WinCE, but I can say, that transactions speed up the whole stuff dramatically. Uli |
Mon, Jan 18 2010 8:31 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Roy Lambert
ead from a CSV (with readln) and put on the screen the record >number... I guess from your other post that the built in IMPORT sql work for you. I'm using D2006 and Windows so I can't help much apart from generalisations. readln will have a chunk of overhead can you get the entire file into a stringlist and then just run through that? If you post the actual code someone here may be able to suggest speed ups. >Probably if I put recno only after 30 or 40 or 50 recs... it's >better... That will help |
Mon, Jan 18 2010 9:25 AM | Permanent Link |
"Fabio Codebue @ GestionaleOpen" | On Mon, 18 Jan 2010 13:31:35 +0000, Roy Lambert <roy.lambert@skynet.co.uk> wrote:
>Roy Lambert > >ead from a CSV (with readln) and put on the screen the record >>number... > >I guess from your other post that the built in IMPORT sql work for you. I'm using D2006 and Windows so I can't help much apart from generalisations. > >readln will have a chunk of overhead can you get the entire file into a stringlist and then just run through that? > >If you post the actual code someone here may be able to suggest speed ups. > >>Probably if I put recno only after 30 or 40 or 50 recs... it's >>better... > >That will help HERE YOU ARE: var i: integer; importfile: TextFile; testo: String; begin Cursor:= crHourGlass; // importa file scelto if FileExists(v_file_importazione.Text) then begin // azzera tabelle if v_Azzera.Checked and v_articoli.Checked then begin q.Close; q.SQL.Clear; q.SQL.Add('delete from art'); q.ExecSQL; end; if v_Azzera.Checked and v_magese.Checked then begin q.Close; q.SQL.Clear; q.SQL.Add('delete from magese'); q.ExecSQL; end; if v_Azzera.Checked and v_negozi.Checked then begin q.Close; q.SQL.Clear; q.SQL.Add('delete from tne'); q.ExecSQL; end; // imposta progress bar v_progressbar.Min:= 0; i:= 0; v_progressbar.Visible:= true; v_progressbar.Max:= 100; t.Close; t.TableName:= nometabella; t.Open; assignfile(importfile, v_file_importazione.Text); reset(importfile); while not EOF(importfile) do begin readln(importfile, testo); t.Append; // ARTICOLI if nometabella='art' then begin // FIXED LENGHT if v_file_fisso.Checked then begin t.FieldByName('art_codice').AsString:= Trim(Copy(UpperCase(testo), StrToInt(v_i1.Text), StrToInt(v_l1.Text))); t.FieldByName('tba_codice').AsString:= Trim(Copy(UpperCase(testo), StrToInt(v_i2.Text), StrToInt(v_l2.Text))); end; // file CSV if v_file_csv.Checked then begin end; end; // q saldi magazzino if nometabella='magese' then begin // FIXED LENGHT if v_file_fisso.Checked then begin t.FieldByName('art_codice').AsString:= Trim(Copy(UpperCase(testo), StrToInt(v_i1.Text), StrToInt(v_l1.Text))); t.FieldByName('tma_codice').AsString:= Trim(Copy(UpperCase(testo), StrToInt(v_i2.Text), StrToInt(v_l2.Text))); t.FieldByName('esistenza').AsFloat:= StrToFloat(Copy(UpperCase(testo), StrToInt(v_i3.Text), StrToInt(v_l3.Text))); end; // file CSV if v_file_csv.Checked then begin end; end; // NEGOZI if nometabella='tne' then begin // FIXED LENGHT if v_file_fisso.Checked then begin t.FieldByName('tne_codice').AsString:= Trim(Copy(UpperCase(testo), StrToInt(v_i1.Text), StrToInt(v_l1.Text))); t.FieldByName('descrizione').AsString:= Trim(Copy(UpperCase(testo), StrToInt(v_i2.Text), StrToInt(v_l2.Text))); end; // file CSV if v_file_csv.Checked then begin end; end; t.Post; // progress bar i:= i + 1; v_progressbar.Position:= i mod 100; v_recno.Caption:= IntToStr(i); Application.ProcessMessages; end; v_recno.Caption:= 'IMPORTAZIONE COMPLETATA!'; closefile(importfile); end; // fine chiudi tutto t.Close; Cursor:= crDefault; |
Mon, Jan 18 2010 9:28 AM | Permanent Link |
"Fabio Codebue @ GestionaleOpen" | On Mon, 18 Jan 2010 14:30:38 +0100, Uli Becker <johnmuller54@googlemail.com> wrote:
>Fabio, > >I don't know neither Lazarus nor WinCE, but I can say, that transactions >speed up the whole stuff dramatically. > >Uli So you believe that If I start to use transaction I will have a better performance? I have to do something like this: while have some rec to import do begin eDB.database.starttransaction; articoli.append articoli --> edit field articoli.post eDB.Commit; end; |
Mon, Jan 18 2010 10:08 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Roy Lambert
>I have to do something like this: Yes but you move the transaction outside the loop eDB.database.starttransaction; while have some rec to import do begin articoli.append articoli --> edit field articoli.post end; eDB.Commit; Otherwise you get no benefit from it. You may want/need to commit at intervals if you don't have enough to do the whole batch in one go. Roy Lambert [Team Elevate] |
Mon, Jan 18 2010 10:33 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Roy Lambert
Since I don't use Lazarus or WinCE these are just guesses / general comments 1. The empty table sql is going to take time. Because of the new features in ElevateDB its pretty much essential for safety. BUT I have an unsafe table empty system (just delete the data files from disk). It's not to be recommended for live systems since it may screw things up, but for my purposes (copy the live data to the development system for testing) I'm happy to take the risk. 2. You have some data conversion going on inside the loop eg StrToInt(v_i1.Text) and StrToInt(v_l1.Text) do this outside. I have no idea how powerful the WinCE processor is or how good the compiler is but doing a StrToInt 40,000 times could be a time waster. 3. Where possible do tests and set a flags outside the loop eg you have if nometabella='art' then begin if nometabella='magese' then if nometabella='tne' then You could set Conversion to 0, 1 or 2 then use a case statement which will run a bit faster. Even better. You have the code for fixed length and you've placeholders for the csv code so the above approach could be extended. Outside the loop have a construct if (nometabella='art') and v_file_fisso.Checked then Conversion := 0 else if (nometabella='art') and v_file_csv.Checked then Conversion := 1 else if (nometabella='magese') and v_file_fisso.Checked then Conversion := 2 else if (nometabella='magese') and v_file_csv.Checked then Conversion := 3 else if (nometabella='tne') and v_file_fisso.Checked then Conversion := 4 else if (nometabella='tne') and v_file_csv.Checked then Conversion := 5; and have a case statement inside the loop. 4. As I suggested in the other post try reading the entire file into a stringlist and looping round that. 5. Again a minor one Trim(Copy(UpperCase(testo), Move the Uppercase outside the Trim - it then has less characters to operate on. Probably won't make any difference but if its a low power cpu it might shave a second or four. Roy Lambert [Team Elevate] |
Mon, Jan 18 2010 4:30 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Fabio,
<< So you believe that If I start to use transaction I will have a better performance? >> Yes, Uli is correct - using a transaction around the whole block will improve the performance. However, as Roy said, you may need to call Commit at certain intervals if you start to exceed the allowable memory usage under Windows CE. This is because transactions are buffered in ElevateDB. -- Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Monday, May 6, 2024 at 01:09 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |