You are not logged in.
Pages: 1
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
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.
Online
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
this is where it hits the delay
database:=tsqlrestserverdb.Create(model, extractfilepath(paramstr(0))+'database.dat', true, 'password');
Offline
I just noticed that in some cases it is taking more than 5 minutes. And sometimes it is nearly instant.
Offline
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
I should note, this delay only seems to happen after a reboot when the disk cache is fresh.
Offline
I can just do a static row counter that gets updated when I update the database
Offline
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?
Online
Pages: 1