Speed problem

array81 · 2012-11-01 22:15:21

I must add about 250000 record on a SQLite table. My code works but it's very slow.

MyRecord := TSQLMyTable.Create;
try
  MyRecord .AAA:= StringToUTF8('test');
  MyRecord .BBB := StringToUTF8('test');
  MyRecord .CCC := StringToUTF8('test');

  if Database.Add(TAFRecord, True) > 0 then
    begin

    end;
finally
  TAFRecord.Free;
end;

I'm working on local computer (no client/server) and I use "Database: TSQLRestServerDB;" (I cannot use "Database: TSQLRest;" because I need EngineExecuteAll to run "VACUUM" sql).

Can you help me?

Last edited by array81 (2012-11-01 22:32:14)

ab · 2012-11-01 23:01:00

This is a known restriction of SQLite3, due to its ACID nature.

1) use a transaction (and a batch process)

2) set DB.Synchronous := smOff

See http://blog.synopse.info/post/2012/07/26/ACID-and-speed

array81 · 2012-11-01 23:14:52

OK, I have read the article.

But:
1) TSQLRestServerDB doesn't have a Synchronous property, where I find it?
2) I need use TSQLRestClientDB e TSQLRestServerDB also if I use a local database?

ab · 2012-11-02 10:34:16

You have the TSQLRestServerDB.DB.Synchronous property.

See the "15 - External DB performance" sample.

array81 · 2012-11-02 11:52:56

OK.

1. Is there a way to use SQL (for example call "VACUUM") with TSQLRest? Or si it right use TSQLRestServerDB.
2. "use a transaction (and a batch process)" but for this I need of TSQLRestClientDB and then of TSQLRestServerDB, right?

ab · 2012-11-02 12:43:09

1. You should better run such low-level commands on the Server side, using a dedicated interface-based service for instance, or a method-based service command.

2. You can use transactions on both sides.
Batch mode is designed for the client side.

mORMot Open Source

#1 2012-11-01 22:15:21

Speed problem

#2 2012-11-01 23:01:00

Re: Speed problem

#3 2012-11-01 23:14:52

Re: Speed problem

#4 2012-11-02 10:34:16

Re: Speed problem

#5 2012-11-02 11:52:56

Re: Speed problem

#6 2012-11-02 12:43:09

Re: Speed problem

Board footer