mysqlite3 long load time

dansot · 2013-01-09 20:20:55

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.

ab · 2013-01-09 20:39:48

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.

dansot · 2013-01-09 20:46:11

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');

ab · 2013-01-09 22:52:26

And which operation does take a minute?

dansot · 2013-01-10 02:52:00

this is where it hits the delay

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

dansot · 2013-01-10 04:22:51

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

ab · 2013-01-10 09:33:02

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?

dansot · 2013-01-10 20:45:38

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?

dansot · 2013-01-10 20:51:16

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

ab · 2013-01-10 22:05:41

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.

dansot · 2013-01-10 22:40:38

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

ab · 2013-01-11 13:21:50

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?

mORMot Open Source

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

mysqlite3 long load time

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

Re: mysqlite3 long load time

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

Re: mysqlite3 long load time

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

Re: mysqlite3 long load time

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

Re: mysqlite3 long load time

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

Re: mysqlite3 long load time

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

Re: mysqlite3 long load time

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

Re: mysqlite3 long load time

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

Re: mysqlite3 long load time

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

Re: mysqlite3 long load time

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

Re: mysqlite3 long load time

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

Re: mysqlite3 long load time

Board footer