Why records added so slowly

Question

I'm reading records from SQL Server 2005 and writing returned recordset to SQLite with following piece of code.

My compiler is Lazarus 1.0.12 and qt1 is "sqlquery" also "qrystkmas" is Ztable from Zeos dbo...

but the operation is quite slow. the test time is

start time : 15:47:11 finish time : 16:19:04 Record count is : 19500

So in SQL Server and SQL Server CE pair it is less than 2-3 minute on Delphi project.

How can I speed up this process?

Code:

  Label2.Caption:=TimeToStr(Time);
  if Dm.Qt1.Active then Dm.Qt1.Close;
    Dm.Qt1.SQL.Clear;
    Dm.Qt1.SQL.Add('  select ');
    Dm.Qt1.SQL.Add('   st.sto_kod, st.sto_isim,st.sto_birim1_ad,  ');
    Dm.Qt1.SQL.Add('   st.sto_toptan_vergi,st.sto_perakende_vergi,');
    Dm.Qt1.SQL.Add('   st.sto_max_stok,st.sto_min_stok, ');
    Dm.Qt1.SQL.Add('   sba.bar_kodu,   ');
    Dm.Qt1.SQL.Add('   stf.sfiyat_fiyati  ');
    Dm.Qt1.SQL.Add('  from MikroDB_V14_DEKOR2011.dbo.STOKLAR st ');
    Dm.Qt1.SQL.Add('  left JOIN MikroDB_V14_DEKOR2011.dbo.BARKOD_TANIMLARI sba on sba.bar_stokkodu=st.sto_kod ');
    Dm.Qt1.SQL.Add('  left JOIN MikroDB_V14_DEKOR2011.dbo.STOK_SATIS_FIYAT_LISTELERI stf on stf.sfiyat_stokkod=st.sto_kod  ');
    Dm.Qt1.SQL.Add('  where LEFT(st.sto_kod,1)=''5'' --and stf.sfiyat_listesirano=1 ');
    Dm.Qt1.Open;
    Dm.qryStkMas.Open;
    Dm.qrystkmas.First;

    While not Dm.Qt1.EOF do
    begin
      Dm.qryStkMas.Append;
      Dm.qryStkMas.FieldByName('StkKod').AsString :=Dm.Qt1.FieldByName('sto_kod').AsString;
      Dm.qryStkMas.FieldByName('StkAd').AsString  :=Dm.Qt1.FieldByName('sto_isim').AsString;
      Dm.qryStkMas.FieldByName('StkBrm').AsString :=Dm.Qt1.FieldByName('sto_birim1_ad').AsString;
      Dm.qryStkMas.FieldByName('StkBar').AsString :=Dm.Qt1.FieldByName('bar_kodu').AsString;
      Dm.qryStkMas.FieldByName('StkKdv1').AsFloat :=Dm.Qt1.FieldByName('sto_toptan_vergi').AsFloat;
      Dm.qryStkMas.FieldByName('StkKdv2').AsFloat :=Dm.Qt1.FieldByName('sto_perakende_vergi').AsFloat;
      Dm.qryStkMas.FieldByName('StkGir').AsFloat  :=0;
      Dm.qryStkMas.FieldByName('StkCik').AsFloat  :=0;
      Dm.qryStkMas.FieldByName('YeniStk').AsBoolean :=False;
      Dm.qryStkMas.FieldByName('MinStk').AsFloat  :=Dm.Qt1.FieldByName('sto_min_stok').AsFloat;
      Dm.qryStkMas.FieldByName('MaxStk').AsFloat  :=Dm.Qt1.FieldByName('sto_max_stok').AsFloat;
      Dm.qryStkMas.FieldByName('StkGrp1').AsString:='';
      Dm.qryStkMas.FieldByName('StkGrp2').AsString:='';
      Dm.qryStkMas.FieldByName('StkGrp3').AsString:='';
      Dm.qryStkMas.FieldByName('StkGrp4').AsString:='';
      Dm.qryStkMas.FieldByName('StkFytno').AsInteger:=1;
      Label1.Caption:=Dm.Qt1.FieldByName('sto_isim').AsString;
      Dm.qryStkMas.Post;
      Dm.Qt1.Next;
    end;
    Dm.qryStkMas.Close;
   label3.Caption:=timetostr(time);

freetds and lazarus standard mssqlconnection+sqlite driver and zeos dbo... — ikutluay, Oct 11 '13 at 13:47
RBA; The qrystkmas is empty, so opening is no problem and it is not the botlleneck point. Also there is no dataaware components connecto to it. It is just a datapump code. — ikutluay, Oct 11 '13 at 14:45
I would suggest step through your code line by line in debug, and look for key statements which take time. — Jerry Dodge, Oct 11 '13 at 14:55
If you figure out what part's slow, show us the db schema, and tell us how large the datasets are, we can help more. — Marcus Adams, Oct 11 '13 at 17:10
@JerryDodge - ' where LEFT(st.sto_kod,1)=''5'' --and stf.sfiyat_listesirano=1 ' there the parameters can be used. ikutluay - I asked you how much time it takes, because from my point of view, the bottleneck is not in the Delphi code(except a bug in component/component optimization etc). — RBA, Oct 11 '13 at 17:28

score 4 · Accepted Answer · edited May 23 '17 at 11:50

The first step in speeding things up is diagnosis.

MEASURING
You can measure by splitting the select and insert up obviously, but you can also get some diagnostics out SQL itself.

If you prefix out query with the keyword EXPLAIN in SQLite it will tell you what indexes are use and how the statement is handled internally, see here: http://www.sqlite.org/eqp.html
This is invaluable info for optimizing.
IN MS SQL Server you go into the gui, put in the query and click on the estimated query plan button, see: what is the equivalent of EXPLAIN form SQLite in SQL Server?.

What's taking the most time? Is the select slow or is the insert.

SELECT
Selects are usually speed up by putting indexes on those fields that are evaluated.
In your case the fields involved in the join criteria.
The field in the where clause uses a function and you cannot put an index on a function in MSSQL (you can in PostgreSQL and Oracle).

INSERT
Inserts are speed up by disabling indexes.
One common trick is to disable all indexing prior to the insert batch and reenable them after the insert batch is done.
This is usually more efficient because its faster (per item) to sort the whole in one go that to keep resorting after each individual insert.

You can also disable transaction safeguards.
This will corrupt your data in case or power/disk etc failure, so consider yourself warned, see here: Improve large data import performance into SQLite with C#

Comments on the code
You select data using an SQL select statement, however you insert using the datasets append and fieldbyname() methods. FieldByName is notoriously slow because it does a name lookup every time.
FieldByName should never be used in a loop
Construct an insert SQL statement instead.
Remember that you can use parameters, or even hard paste the values in there.
Do some experimentation to see which is faster.

About.com has a nice article on how to speed up database access by eliminating FieldByName: http://delphi.about.com/od/database/ss/faster-fieldbyname-delphi-database.htm

Johan ; ill check it out. But im scaring now performance issues. I just want to develop an app for wince and since the Sqlite is the single option for DBMS on that os (for lazarus) im really confused for performance on wince Terminal. But i know this from my previous test: Select is return full records on 2-3 seconds. but the writing record is more than half hour. I'll read your advised links and have look what we can do. — ikutluay, Oct 11 '13 at 14:41
You are comparing performance between a win32 Delphi app and a Lazarus WINCE app? Isn't that slightly unfair, and shouldn't that fact be mentioned in the original post ? — Marco van de Voort, Oct 12 '13 at 11:10
@MarcovandeVoort i'm not comparing they like a pair. I know they are two different compiler and profile. But i know that Lazarus program cant be slow like this and i must definitely made a mistake in this project. But where ? – ikutluay 13 mins ago — ikutluay, Oct 12 '13 at 14:15

Ivan Voras · Answer 2 · 2013-10-11T14:40:48.537

3

Did you try wrapping your insertions in a transaction? You would need to BEGIN a transaction before your While... and COMMIT it after the ...End. Try it, it might help.

Edit: If you get an improvement, that would be because your database connection to SQLite is set up in the "autocommit" mode, where every operation (such as your .Append) is done independantly with all the others, and SQLite is smart enough to ensure ACID properties of the database. This means that for every write operation you make, the database will make one or more writes to your hard drive, which is slow. By explicitly creating a transaction (which turns off autocommit...), you group write operations in a transaction, and the database can issue a much smaller number of writes to the hard drive when you explicit commit the transaction.

edited Oct 11 '13 at 14:40

answered Oct 11 '13 at 14:16

Ivan Voras

1,895
1
13
20

This should be a comment. It's not an answer because it does not explain anything. – Johan Oct 11 '13 at 14:36
1

I say, don't worry about auto-commit and do all the inserts in a single SQL statement. – Marcus Adams Oct 11 '13 at 17:13
I suggest that grouping inserts and performing an explicit commit every n inserts may give better performance then holding it all to the end. Having an uncommitted transaction can hold server resources, lock rows or pages on the database, slow other users, and v possibly cause other contention. Best advice is to try regular commits (and all the other suggestions) and see what works! – DavidG Oct 12 '13 at 01:28
@IvanVoras I have disabled autocommit and the time is now much more bigger and 44min... – ikutluay Oct 12 '13 at 13:57
That is completely unexpected. Did you just disable autocommit, or did you wrap everything in a transaction? – Ivan Voras Oct 12 '13 at 17:27

Why records added so slowly

2 Answers2