From 5.03 seconds to 306 miliseconds (!)

[m.Th.]

Steps:

0. Close the program
1. Copy the catalog in a new file. ("XnView - copy.db" on my PC)
2. Open the original file in you SQLite tool - it has the page size of 1024.
3. Run the following: "Select * from Datas" limit 2000; (it brings 2000 records from thumbnails table - the most heavy table which we have). On my machine, it finishes in 5,03 secs (5030 milisecs) from a table of 22,5 kiloRecords.
4. Close the original file. Open the copy.
5. Change the page size of the database:

Code: Select all

pragma page_size=4096;
vacuum;

6. Run the same query. It finishes on my machine in 306 miliseconds. 16 times faster!

...in fact I knew it. ...and if I would change to 16384 I suppose that it would be even faster. See here:

http://www.sqlite.org/intern-v-extern-blob.html

Also, nowadays (almost) all SQL servers have variable page sizes from you can choose (except MS SQL which has a fixed page size of 8k) but most databases are there: page sizes of 8k and 16k. More rare to be found 4k and 32k (DB2 for example).

You can put in the DB Manager a drop down box labeled Page Size with some values in it. The classical values are 1024, 2048, 4096, 8192, 16384, 32768. In order to display the actual value of Db you use pragma page_size). Next to drop down list put a button called "Change Page Size". When the user presses it, you can throw a message "It will take some time and if isn't used correctly, it can affect performance. Continue?" and if the user chooses "yes" you issue the two comands: "pragma page_size=<new value>" and "vacuum;" like above. When it finishes, perhaps is better to re-read the new page size to see if everything is ok.

[xnview]

Which tool do you use?

When i use

Code: Select all

pragma page_size=4096;
vacuum;

on a big DB, there is no delay. Can i change it for old database?

[m.Th.]

Which tool do you use?

When i use

Code: Select all

pragma page_size=4096;
vacuum;

on a big DB, there is no delay. Can i change it for old database?

Yes, you can. But unfortunatelly some tools doesn't accept this pragma (I don't know why).
For ex. SQLiteSpy does (it works) which SharpPlus SQLite developer does not (it doesn't work).

Beware: When I mean 'delay' I mean fetching the records. SQLiteSpy fetches (some of) them. The thing which makes all the difference.
"Executing" a simple query like Select * from MyTable doesn't have any delay, of course.

[xnview]

Sorry but not with a tools, in XnView, when i open the database ?

[m.Th.]

Sorry but not with a tools, in XnView, when i open the database ?

A-HA!

First, I think that you've read about Pages here: http://newsgroup.xnview.com/viewtopic.php?f=60&t=28232

Now, how do we use pragma page_size=4096; (or any other value)

First step: We must ensure that on new installations of XnViewMP the newly created DB has a page size of eg. 16384. Hence you need to have somewhere the following code (not tested):

Code: Select all

crtPageSize=SQLite3_exec('pragma page_size'); read the actual page size
cDBFile='C:\Foo\XnView.DB'
if not FileExists(cDBFile) then
{
  SQLite3_exec('pragma page_size=16384'); //<-----add this
  CreateDB(cDBFile);
}

If it doesn't work, then you need to create it with 1024 and change it to 16384 (or whatever).

How?

First, we must know that we "cannot" "change" the page size of an existing db. But we can do a trick for this. Usually, this is done on other DB systems through a backup/restore cycle, but for SQLite is done through the VACUUM command.
VACUUM command, in fact, creates a new db with the same schema and copies all the data from the old one, deletes the old DB and renames the new DB with the old name. Hence, you must do

Code: Select all

SQLite3_exec(pragma page_size=16384')
nPageSize:=SQLite3_exec(pragma page_size); //read it
if nPageSize<>16384 then 
{
 ShowMessage('Error!'); //it shouldn't reach here
}
else
{
 SQLite3_Exec('VACUUM'); //perhaps is better to inform the user that it is a lenglty process for a big DB...
}

For the existing instalations, perhaps you can compare the actual page size and offer the possiblity to upgrade:

Code: Select all

if crtPageSIze=1024 then
{
  if MessageQuery('The page size is suboptimal. Optimize?')=mrYes then
  {
    SQLite3_Exec('pragma page_size=16384');
    SQLite3_Exec('Vacuum');
  }
}

Something like that. Of course you can optimize this (make a method for changing page size etc.)