Yes, sure.
This is what we have in mind.
The problem is that now:
- "Thumbs.DB" is inside of Xnview.db
- 'Thumbs.DB' (ok, the 'Datas' table) doesn't have an optimal structure because the Thumbnail is tied with other useful data. (Thumbnail size etc.)
We can refactor this, but it will take a looooOOOOoooOOOoooOOOooOOOOOng time for you to wait till the program will move and split around the thumbnails from...
- ...the old database
- ...the fields with which it is together
I think that point 1 above is obvious, let me explain point 2:
Technical info begin:
Unfortunately, SQLite stores the BLOBs in-line - IOW, together with the record. (Allmost all the other DB engines store it outside of the record, in special pages).
As we saw here
http://newsgroup.xnview.com/viewtopic.php?f=60&t=28232 it is important to minimize the IO operations and the access to disk and this is done through Pages and Cache Size (
also, be sure to vote here http://newsgroup.xnview.com/viewtopic.php?f=60&t=28263)
Let's say that we have a page size of 8192 bytes (8k).
And now, let's do some math:
If we have Datas like this:
Code: Select all
CREATE TABLE Datas(ImageID INTEGER PRIMARY KEY, Created INTEGER, Size INTEGER, Data BLOB);
...then our thumbnail stored in the Data field will eat all the page (in fact it will eat
two pages because usually thumbs are bigger than 8k). Hence with one IO operation will fetch just one record (!).
So, any query on this table (except fetching the thumbs, of course) trying to compute the sum of sizes or check the Created field or for any other futher purpose will be very slow because it will trash the disk badly only and only to discard 99% (yes, 99%! ) of the data.
If we refactor like this...
Code: Select all
CREATE TABLE Datas(ImageID INTEGER PRIMARY KEY, Data BLOB); -- leave just the thumb
Code: Select all
CREATE TABLE DatasExtra(ImageID INTEGER PRIMARY KEY, Created INTEGER, Size INTEGER); -- and any future field
...we'll have for the second table in the most common case (it can vary, of course): 4 bytes (PK) + 4 bytes ('Created' field) + 4 bytes ('Size') = 12 bytes / record. (!!)
Hence in one page (8192 bytes) we'll have approx. 680 records!! in the new model... ...HUGE difference from one record / two pages (!) in the old model
So, any query not fetching the thumbs will be hundredths times faster than now! (especially if we know to exploit this - JOINs, GROUP BY... etc.)
-----------
Also, because our blessed SQLite doesn't have direct access to fields, only sequential, with the old design
if we want to add any field in the future, we must add it after the BLOB, hence we'll read (scan) that big chunk of data even if we need it or not. More slowness... yech!
Technical info end.
SO, we need (IMHO) to refactor the old 'Datas' table. BUT in order to refactor, my question from OP arises:
Do you want to keep the generated thumbs? If yes, then the upgrade process will be very slow (
because we'll need to process (move, split) many, many records - explanation above). If you do not want to keep the thumbs (I don't want since there are NO disatvantages) the the upgrade process will be simpler and quicker because the program will empty first the Datas table (the Thumbs database will be empty) and hence no record to move around.
This was my question: Do you want to keep the generated thumbs?
m. Th.
- Dark Themed XnViewMP 1.7.1 64bit on Win11 x64 -
