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

Fabio

Do you have any triggers associated with the table? That's what I found really slowed things down.

Roy Lambert

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...

Fabio,

I don't know neither Lazarus nor WinCE, but I can say, that transactions
speed up the whole stuff dramatically.

Uli

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

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;

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;

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]

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]

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