#1 2013-01-09 20:20:55

dansot
Member
Registered: 2012-11-12
Posts: 25

mysqlite3 long load time

My database with around 7 million records seem to take a very long time to load when my program first starts up.  It has taken as long as a minute.  Is it loading the index or something?  Can I speed it up somehow?

The indexed column contains a list of 32 character strings.

Offline

#2 2013-01-09 20:39:48

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,603
Website

Re: mysqlite3 long load time

What is the database file size?
How do you load it?
Are you using the ORM classes?
In order to speed all things, you need to use FillPrepare method, then load and add the records (for the ORM).

With direct access of SQLite3, I reach easily 40MB of data / second.

You do not "load the index", the index is stored within the file, and won't slow down read, just writing.

We need more information to understand what is happening here.

Offline

#3 2013-01-09 20:46:11

dansot
Member
Registered: 2012-11-12
Posts: 25

Re: mysqlite3 long load time

The database is 556MB

I am doing

Type
tinfo = class(TSQLRecord)
  private
    fsig: RawUTF8;
  published
    property sig: RawUTF8 read fsig write fsig;
  end;

tver = class(TSQLRecord)
  private
    fver: integer;
    ftimestamp: tdatetime;
  published
    property version: integer read fver write fver;
    property timestamp: tdatetime read ftimestamp write ftimestamp;
  end;

  database: TSQLRestServerDB;
  model: TSQLModel;

  model:=tsqlmodel.Create([tver,tinfo]);
  database:=tsqlrestserverdb.Create(model, extractfilepath(paramstr(0))+'database.dat', true, 'password');

Offline

#4 2013-01-09 22:52:26

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,603
Website

Re: mysqlite3 long load time

And which operation does take a minute?

Offline

#5 2013-01-10 02:52:00

dansot
Member
Registered: 2012-11-12
Posts: 25

Re: mysqlite3 long load time

this is where it hits the delay

database:=tsqlrestserverdb.Create(model, extractfilepath(paramstr(0))+'database.dat', true, 'password');

Offline

#6 2013-01-10 04:22:51

dansot
Member
Registered: 2012-11-12
Posts: 25

Re: mysqlite3 long load time

I just noticed that in some cases it is taking more than 5 minutes.  And sometimes it is nearly instant.

Offline

#7 2013-01-10 09:33:02

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,603
Website

Re: mysqlite3 long load time

This should not happen.
I've never seen that.
Opening a SQLite3 database is instant.

Could you try on another computer?
Could you disable AntiVirus?
What if you disable encryption?

Offline

#8 2013-01-10 20:45:38

dansot
Member
Registered: 2012-11-12
Posts: 25

Re: mysqlite3 long load time

Upon further research and instrumentation it appears to be the database.TableRowCount(tinfo) function that is causing the delay:

Got row count in 22.18200 seconds.

Am I doing that wrong?

Offline

#9 2013-01-10 20:51:16

dansot
Member
Registered: 2012-11-12
Posts: 25

Re: mysqlite3 long load time

I should note, this delay only seems to happen after a reboot when the disk cache is fresh.

Offline

#10 2013-01-10 22:05:41

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,603
Website

Re: mysqlite3 long load time

It runs "select count(*) from table" which seems to be slow in your case.
I'll check for making TableRowCount() not called in such case.

Offline

#11 2013-01-10 22:40:38

dansot
Member
Registered: 2012-11-12
Posts: 25

Re: mysqlite3 long load time

I can just do a static row counter that gets updated when I update the database

Offline

#12 2013-01-11 13:21:50

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,603
Website

Re: mysqlite3 long load time

SELECT COUNT(*) can be slow.
See  http://stackoverflow.com/questions/8988915

I've added a new dedicated method, named TSQLRest.TableHasRows, which will execute the much faster SQL statement instead:
"SELECT RowID FROM TableName LIMIT 1"

See http://synopse.info/fossil/info/b1ac94f7d1

What I do not understand is how you may have a TableRowCount() call, with pure rSQLite3 kind of table.
Do you have any external or virtual table in your code?

Offline

Board footer

Powered by FluxBB