User forums now online! Please see the latest post in the Announcements forum for more information.

Author Topic: Thumbnail View Scrolling performance and caching  (Read 2491 times)

0 Members and 1 Guest are viewing this topic.

haniobaid

  • Member
  • **
  • Posts: 15
    • View Profile
Thumbnail View Scrolling performance and caching
« on: 2015-05-03 05:30:55 »
version: Thumbsplus 9 Sp2 build 3946 (Feb2015)
Windows: 8.1 Pro MCE 64-bit running on Samsung 850 Pro 512GB SSD
DB: MS SQL Server 2012
HD: WD Red 6TB SATA (6Gb/sec 64MB Cache) (both thumbpls install and database on it)
Mobo: Asus Rampage V Extreme
CPU: Core i7-5960X 3.00GHz, 8 Core(s), 16 Logical Processor(s)
RAM: 64GB Corsair Dominator DDR4 PC2300
VID: ASUS MATRIX GeForce GTX 980 4GB GDDR5
RES: 1920x1080 Landscape
DISP: Samsung LED HDMI port
thumbnails: 240x240 Tru Color Lzh Lossless
View: Custom View (thumbsize 100%, no stetching)
Items displayed: Name, thumbnail, file size (accurate/formatted)
Params: No child folders, no watch changes, no color management, no grid, no mask, include hidden
Sorting: File Size Descending, Thumbnails last
Windows displayed: thumbnail view, location, tasks, one toolbar with 24 buttons, menu bar
DB Connection: System DSN local db (named pipes), note: tried tcp but it did not affect this issue at all


The specific performance issue is related to scrolling down the pages of the thumbnail view.

With thumbnails at 100% size, on my 40" Samsung LED, the thumbnail table will display 7 images horizantal, and 3 vertical (3.2 to be exact) for a total of 21 fully visible images per page.

For a given folder with only 1276 images with all thumbnails already created, no db tasks running, once the thumbnail view is loaded after switching to the folder, when i scroll down even 1 page, either by pressing PGDWN or clicking once on bottom of vertical scroll bar, I see a white screen in the thumbnail view briefly then the 21 images appear all at once. This happens every time I scrol down one page. If after scrolling down 10 pages, I scroll back to the first page and then scroll down the pages load instantly meaning the images are now cached and load quickly. It is obvious this caching has a preset programming limit. When i tested with my aforementioned settings I found Thumbsplus would retain about 50 pages scroling down and at that point the last scrolled page will start replacing the first in the cache.

Moreover if I decrease thumbnail size to 10% thereby displaying 464 images (29x16) per page instead of 21 (7x3), thumbsplus would still cache all displayed images on these "big" pages as I scrolled down upto about 1000 images before the last loaded image rows start displaying the first in the cache.

Not surprisingly I checked the advanced options and lo and behold there was an option for caching thumbnails set to exactly 1000. Next to the 1000 thumbsplus displayed the memory used by the setting as 231627KB (231MB). The label on the option says (Thumbnail cache count (10-10000). However if you set it to anything higher than 1000 (even 1001), thumbsplus automatically changes it back to 1000 without telling you.

So I have the following three questions:

A. Why are you limiting this to 1000 and preventing changing this option

B. As a feature request is it possible to add an option checkbox that allows always pre-caching 5 pages up, and 5 pages down in the thumbnail view so the user does not have to wait for the page to load (disabled by default with the 5 figure adjustable in advanced options). This makes much more sense from a usability perspective than supporting caching the last 50 pages (or 1000 images) but requiring the user to scroll the page and wait for the caching.

C. Your documentation on the website mentions you are wroking on a 64 bit version of thumbsplus, will this in fact be part of thumbsplus 10 and what is the current provisional estimated release quarter for thumbsplus 10?

Pete Wieland

  • Full Member
  • ***
  • Posts: 86
    • View Profile
Thumbnail View Scrolling performance and caching
« Reply #1 on: 2015-05-03 08:21:33 »
I have been reporting this issue for years now (since the TP9 betas), but nothing seems to be done about it.

I have found that renaming thumbs9.ini and letting TP recreate it solves the problem, but that will of course put a lot of other settings back to default, and the problem will re-appear eventually.

I have compared a 'faulty' thumbs9.ini with a default copy and there are a whole load of extra lines in the faulty one. As yet I haven't managed to identify which entry is causing the problem, and it could even be more than one working in conjunction with each other.

haniobaid

  • Member
  • **
  • Posts: 15
    • View Profile
Thumbnail View Scrolling performance and caching
« Reply #2 on: 2015-05-03 09:15:52 »
Thanks for answering.

Interesting. I didn't even get temporary relief on deleting the ini file. I had to of course connect to the database after startup. Also I installed using the option all users, and (store settings in one folder) so there may be another file affected in that case or maybe something is getting stored in the database.

I also tried switching from lzh to jpeg, and even to no compression. Neither made a difference. I am pretty sure there is an option affecting it since my previous installation with the same db and a 4 year older machine did not have this problem. Also everything else I do with thumbsplus is lightning fast. So I dont think it's my system hardware specs or software environment.

As a matter of fact a simpler implementation of my suggestion about caching 5 pages up and down would be to cache the number of files specified in the caching parameter and at least to remove the hardcoded 1000 maximum value and allow us to use the announced maximum of 10,000. That would be an improvement.

Since right now I'm having to switch zoom to 10%, pre cache the pages manually (bottom to top) and then scroll up to the top then switch back to 100%. This workaround is rediculously cumbersome.

Also my previous TP install was on C drive with the db on its current drive. I shall try reinstalling thumbsplus on the c drive ssd. That may yeield some improvement since TP io and db io will have separate hardware and because the ssd is faster anyway but I don't think it will solve this particular issue since the limits make it almost certainly a code bug.

The only good thing about it is that it happens every time so I will know if it is fixed or not.

haniobaid

  • Member
  • **
  • Posts: 15
    • View Profile
Thumbnail View Scrolling performance and caching
« Reply #3 on: 2015-05-03 09:30:07 »
Interesting. No difference after uninstall, reinstall and connect to database.

I'm going to try to create a new database.

haniobaid

  • Member
  • **
  • Posts: 15
    • View Profile
Thumbnail View Scrolling performance and caching
« Reply #4 on: 2015-05-03 10:26:35 »
no good, even on a fresh install with a brand new sql 2012 database (simple recovery and pre allocated data file) and 240 by 240 jpeg thumbs with 75 quality and thumbnailing only 1 folder with 240 jpegs and nothing else, with TP running on SSD and DB on a separate drive, the issue will happen. Seems to be the code scales poorly when run over the SQL driver because all things equal when I do the same on the default thumbsplus access database the issue doesn't happen. Only 1 option was changed from default (disabled create thumbnails automatically).

I think the only solution for us is for cerious to auto cache the thumbnails as soon as you select the folder and not wait for the page to be displayed as it is doing now and to remove the hardcoded 1000 cached thumbnails nonesense, and switch to 64 bit like compareable apps. Here's hoping they do this in 10.0 soon :)

Daan van Rooijen

  • Global Moderator
  • Sr. Member
  • *****
  • Posts: 831
    • View Profile
Thumbnail View Scrolling performance and caching
« Reply #5 on: 2015-05-03 18:15:51 »
If you're showing any user fields in the thumbnail listing, I'd be curious if the problem goes away when you turn those off. Another general cause of slowdowns in thumbnail listings is color management.

However, in your case it seems much more likely that the problem is in how the database is driven or even in its structure. There have been various threads about thumbnail browsing speed with non-Access databases in the old newsgroups as well as here (eg. [msg=2800]this topic[/msg]), but I don't recall ever seeing a single solution that helped everyone.

One thing that you could do is turning on SQL tracing in Windows' ODBC settings to see what command(s) ThumbsPlus is issuing to the database when you browse to the next page of uncached thumbnails. Apparently there's a delay there, and hopefully that will give you some ideas as to how it might be addressed. Don't forget to turn tracing off afterwards.
I'm volunteering as a moderator - I do not work for Cerious Software, Inc.

haniobaid

  • Member
  • **
  • Posts: 15
    • View Profile
Thumbnail View Scrolling performance and caching
« Reply #6 on: 2015-05-03 21:18:29 »
Thanks for replying Dan.

I'm not using user fields at all (actually, the only field I need that tp doesn't have is a 5 star rating field).

Issue happens even with all color management off.

Regarding database structure, the structure is created using the default script provided by cerious (after all thumbsplus wouldn't be able to work with it otherwise). The only thing I changed is decreasing thumbnail size from 320 to 240 and quality from 88 to 75 but the issue happened with the 320 / 88 also so that is not related. The script comments say the parameters at the bottom can be customized.


insert into "ThumbsPlusDatabase" values ('thumbnail_width', '320');      -- 320x320 pixel thumbnails
insert into "ThumbsPlusDatabase" values ('thumbnail_height', '320');
insert into "ThumbsPlusDatabase" values ('thumbnail_type', '6');      -- Truecolor 24-bit
insert into "ThumbsPlusDatabase" values ('thumbnail_compress', '88');   -- Jpeg @ Q88
insert into "ThumbsPlusDatabase" values ('thumbnail_files', '0');      -- Thumbnails in separate files
insert into "ThumbsPlusDatabase" values ('volume_match', '3');         -- Network name | No CD serial numbers
insert into "ThumbsPlusDatabase" values ('like_style', '1');         -- SQL92
insert into "ThumbsPlusDatabase" values ('transactions', '0');         -- SQL Server can do transactions
insert into "ThumbsPlusDatabase" values ('background_conn', '1');      -- Use additional connections for background tasks
insert into "ThumbsPlusDatabase" values ('quote_style', '2');         -- ANSI quotes
go

However it does not provide the different options you can use for each one. I am particularly interested in (like_style) and (volume_match) but I didn't touch them since I have no way of knowing what the other options are. I even checked the pdf with instructions on how to do this provided by cerious (sqlserver admin guide.pdf). It mentions only a few of these params.

Regarding how the db is driven. The odbc driver was created with the cerious recommended options as documented in the pdf. As for the db itself, it is an MS SQL 2012 db (ms sq 2012 mode also) with default charset, log file limited to 1GB, data file is at 24GB. Simple recovery model (which makes it faster if anything). The simple recovery model means it is none transactional which matches the parameters above (transactions 0). Auto statistics is on (default).

I will try tracing and update the thread shortly.

haniobaid

  • Member
  • **
  • Posts: 15
    • View Profile
Thumbnail View Scrolling performance and caching
« Reply #7 on: 2015-05-03 21:54:44 »
ok I have the trace. It is running a select ont he thumbnail database for a specifc path id and name, if I remove the where clause the select runs just fine on ms sql.

------------------ On every scroll down it runs these 2 selects:

Thumbs9         171654-171618   ENTER SQLPrepareW
      HSTMT               0x03C9AEA0
      WCHAR *             0x0827F518 [      -3] "Select "idThumb", "Thumbnail"."idPath", "idFiletype", "file_time_iso", "thumbnail_time_iso", "filelength", "checksum", "width", "height", "horiz_res", "vert_res", "colortype", "colordepth", "gamma", "thumbnail_width", "thumbnail_height", "thumbnail_type", "thumbnail_size", "Thumbnail"."name", "metric1", "metric2", "md5", "taken_time_iso", "rating", "idHost", "idUser", "thumbnail", "annotation", "xmp" From "Thumbnail" where "idPath"=? and "name"=?\ 0"
      SDWORD                    -3

Thumbs9         171654-171618   EXIT  SQLPrepareW  with return code 0 (SQL_SUCCESS)
      HSTMT               0x03C9AEA0
      WCHAR *             0x0827F518 [      -3] "Select "idThumb", "Thumbnail"."idPath", "idFiletype", "file_time_iso", "thumbnail_time_iso", "filelength", "checksum", "width", "height", "horiz_res", "vert_res", "colortype", "colordepth", "gamma", "thumbnail_width", "thumbnail_height", "thumbnail_type", "thumbnail_size", "Thumbnail"."name", "metric1", "metric2", "md5", "taken_time_iso", "rating", "idHost", "idUser", "thumbnail", "annotation", "xmp" From "Thumbnail" where "idPath"=? and "name"=?\ 0"
      SDWORD                    -3


---------------------- Then immediately after  Boom:

Thumbs9         171654-171618   ENTER SQLRowCount
      HSTMT               0x03C9AEA0
      SQLLEN *            0x08281818

Thumbs9         171654-171618   EXIT  SQLRowCount  with return code -1 (SQL_ERROR)
      HSTMT               0x03C9AEA0
      SQLLEN *            0x08281818

      DIAG [HY010] [Microsoft][ODBC Driver Manager] Function sequence error (0)

Thumbs9         171654-171618   ENTER SQLBindParameter
      HSTMT               0x03C9AEA0
      UWORD                        1
      SWORD                        1
      SWORD                      -27
      SWORD                        2
      SQLULEN                   15
      SWORD                        0
      PTR                0x010AD7B0
      SQLLEN                     0
      SQLLEN *            0x0828171C

Thumbs9         171654-171618   EXIT  SQLBindParameter  with return code 0 (SQL_SUCCESS)
      HSTMT               0x03C9AEA0
      UWORD                        1
      SWORD                        1
      SWORD                      -27
      SWORD                        2
      SQLULEN                   15
      SWORD                        0
      PTR                0x010AD7B0
      SQLLEN                     0
      SQLLEN *            0x0828171C (8)

--------------- After that the Enter sql row count error followed by function sequence error appears a few times. No other error appears through the whole log.  I noticed the 2 sql statements were executed a total of 3 times, in all cases the enter sql row count error appeared shortly thereafter. I am not sure what thumbsplus did to get the information correctly after the 3rd time since obviously the thumbnails were displayed, yet there are no other sql statements in the trace. I am googling the errors.

Daan van Rooijen

  • Global Moderator
  • Sr. Member
  • *****
  • Posts: 831
    • View Profile
Thumbnail View Scrolling performance and caching
« Reply #8 on: 2015-05-03 23:03:23 »
Hi Haniobaid,

Well, I mentioned the database structure because in the distant past, some users found that they could speed up some operations (e.g. deleting many files) by adding a primary index to some field(s) in their SQL databases. I know very little about SQL myself, though, and I have no idea if any such optimisations would make sense for a database created from Cerious' current script. Likewise, I don't consider myself qualified to comment on your trace log - I just hope that it will provide you (or a knowledgeable reader) with some further pointers that you could look into. I don't think that ThumbsPlus internal caching system makes that much of a difference here - using the default Access database, I can scroll through listings of over 50,000 thumbnails without any long delays.

Petew1959 said that he is having the same problem as you, and that he found that it depended on the contents of thumbs9.ini. In my previous reply, I linked to a message in which another user, Rainer Kasten, reported that he could speed things up by changing the 'InitialDatabase' line in thumbs9.ini. Rainer was using a different database type than you and Pete though, but maybe that's something that you could look into as well. ThumbsPlus will automatically revert any changes to that line during its next session, but apparently that can be worked around by launching TP from a Powershell script that first makes the desired edit to thumbs9.ini, as suggested in another topic by [msg=4387]Sean Nelson[/msg].
I'm volunteering as a moderator - I do not work for Cerious Software, Inc.

haniobaid

  • Member
  • **
  • Posts: 15
    • View Profile
Thumbnail View Scrolling performance and caching
« Reply #9 on: 2015-05-05 01:49:57 »
You are correct about indexes speeding up searches and accessing table columns in regards to db structure. However as the issue happens even with only thumbnail and file name displayed and file name is already indexed, while the thumbnail is of type image which cannot be indexed.

About the trace log, it was a really helpful suggestion since I now know the root cause and that I really can't do anything about it. Basically thumbsplus is incorrectly querying the driver. Now the fact that I did not have the issue with the same db version, and same photos and same thumbsplus version means that there is another factor I am unaware of influencing the issue. The only one I can think of is my previous working setup was on windows 7, whereas this one is on windows 8.1. There is actually nothing wrong with the statement being executed or it would have returned an error. It is the subsequent requests (rowcount) that are failing. I think at this stage only the developer of TP can comment, and I am guessing they do not have time to read the forums.

The fact that the issue doesn't happen on internl ms access db has no baring as to whether caching will help. I would gladly use the internal db but it is very limited in the number of photos it can index. I realize TP offers a lot of flexibility in that it lets you use whatever database platform you prefer, but the down side is they only do minimal testig on each of these platforms and cannot take ownership when things go wrong with the driver. Whereas comparable products do not have the limitation on their internal database to start with (I think that is the way forward in the future). and yes caching would make a difference since:
A. TP does not have to do a db request when the user accesses the cached thumbnail
B. As explained earlier, the issue does not happen once I manually pre-cache the thumbnails by scrolling down from the top 1000 files. At that point I can go back to the beginning and scroll up and down within that 1000 file limit without delay.
C. It is still unclear why the caching parameter has an on interface max of 10,000 but is hard coded to auto return to 1000. Again we need developer input on this
D. The caching is currently done only when the pages are loaded. Auto Pre caching means expecting the user will scroll up and down and getting the request ahead of time saving the user the wait. Since the caching feature is already there, adding pre-caching should be fairly easy. Probably the simplest way is as soon as you open the folder (or switch views or items displayed), dynamically pre cache the number of thumbnails specified in the caching parameter (removing the hard coded 1000 limit). I am guessing they put that 1000 hard-coded limit in there because of 32 bit limitations which is why I asked about moving to 64 bit in the opening post. ideally this smart caching would instead load a set number of pages up and down so instead of limiting to X thumbnails it can save the user from the issue permanently since every page scroll they do is already pre-cached.

The above is of course still a workaround based on expectations that a driver error will occur and fixing the underlying root cause would be a better if more difficult solution (since the develper may not be able to reproduce the issue as Pete and I have been able to very easily).

Regarding changing the intiial db line. TP keeps a history of the db connections you make. I tried setting it to directly use a specific db (which is the odbc driver connection) so it will ignore the history (as opposed to the default option of using the last connected db) but it did not help. Keeping in mind the only other previous connection i had as seen in the ini was the internal ms access db.

Daan van Rooijen

  • Global Moderator
  • Sr. Member
  • *****
  • Posts: 831
    • View Profile
Thumbnail View Scrolling performance and caching
« Reply #10 on: 2015-05-05 14:35:51 »
> I did not have the issue with the same db version, and same photos and same thumbsplus version means that there is another factor I am unaware of influencing the issue. The only one I can think of is my previous working setup was on windows 7, whereas this one is on windows 8.1.

In that case I think TP is issuing the correct query after all, but the driver isn't executing it as quickly as it could or something in Windows is interfering. Or maybe an error has crept into the database (have you tried Compact & Repair in Database | Advanced?)

> I think at this stage only the developer of TP can comment, and I am guessing they do not have time to read the forums.

Yes.. sadly. Well, you could give pcrews@cerious.com a try.

> The fact that the issue doesn't happen on internl ms access db has no baring as to whether caching will help.

Well, it does insofar that it shows that non-cached thumbnails can be browsed rapidly and that the primary cause of the delays that you are seeing must lie elsewhere. Better caching may well improve your experience, but it won't solve those initial delays when the thumbnails are first read.

> Regarding changing the intiial db line. TP keeps a history of the db connections you make. I tried setting it to directly use a specific db (which is the odbc driver connection) so it will ignore the history (as opposed to the default option of using the last connected db) but it did not help. Keeping in mind the only other previous connection i had as seen in the ini was the internal ms access db.

Well, the point of Rainer Kasten's message was that those knowlegeable about SQL may be able to edit that InitialDatabase line to improve performance (yes, you would have to configure a specific startup database. The history of previous connections is not relevant). In his case, he added options "SyncPragma=OFF; PRAGMA journal_mode=OFF" that helped, but that was with an SQLite3 database.

I don't know if there are any performance analysis or enhancement tools for MS SQL that you could throw at this problem, or if there's anything specific to Win 8.1 that might cause delays.. have you looked into that? As a general purpose tool, Sysinternal's ProcMon (free from Microsoft.com) might also provide some clues as to the nature of the delays.
I'm volunteering as a moderator - I do not work for Cerious Software, Inc.

haniobaid

  • Member
  • **
  • Posts: 15
    • View Profile
Thumbnail View Scrolling performance and caching
« Reply #11 on: 2015-05-05 17:03:56 »
In that case I think TP is issuing the correct query after all, but the driver isn't executing it as quickly as it could or something in Windows is interfering.

>>>>> Very possible of course. I have plenty of games that stopped working on windows 8.1 that used to work on windows 7.  I have picked up many windows 8.1 bugs since I switched to it too. I do have UAC turned off and I run TP as administrator. I can't think of anything else off hand but it is possible the driver functions differently on windows 8.1.


Dan:  Or maybe an error has crept into the database (have you tried Compact & Repair in Database | Advanced?)

>>>>>>>>>>>> I did try compact and repair, and strangely when I click the option nothing happens at all. I thought maybe this was meant only for the internal db. However the issue is not due to db corruption, I did after all try to recreate the db at least 3 times and the issue would occur even on a fresh db with only a couple dozen thumbnails created.

Dan: Better caching may well improve your experience, but it won't solve those initial delays when the thumbnails are first read.

>>>> Agreed.


Dan: In his case, he added options "SyncPragma=OFF; PRAGMA journal_mode=OFF" that helped, but that was with an SQLite3 database.

>>>> Interesting, I am not knowledgeable enough about the SQL driver parameters but hopefully someone on the forum may read this some day.

Dan: I don't know if there are any performance analysis or enhancement tools for MS SQL that you could throw at this problem, or if there's anything specific to Win 8.1 that might cause delays.. have you looked into that? As a general purpose tool, Sysinternal's ProcMon (free from Microsoft.com) might also provide some clues as to the nature of the delays.

>>>> There are indeed but the issue isn't happening on the db side since the query itself was lightning fast (milliseconds) when I ran it on the db directly, it is only happening when thumbsplus runs the query through the driver on the db. I do not think db optimization will help. If we could find the cause of the driver error, then a db parameter may help. There are many that could affect this such as ansi options, quote options, localization options, but it would be hard to adjust them without knowing more about what is failing. I hadn't heard of procMon but will look into it.

Daan van Rooijen

  • Global Moderator
  • Sr. Member
  • *****
  • Posts: 831
    • View Profile
Thumbnail View Scrolling performance and caching
« Reply #12 on: 2015-05-05 22:35:05 »
>I can't think of anything else off hand but it is possible the driver functions differently on windows 8.1.

Does Windows 8.1 offer an XP or Win 7 compatibility mode that might make a difference? (through the properties of the program's shortcut).

> If we could find the cause of the driver error, then a db parameter may help. There are many that could affect this such as ansi options, quote options, localization options, but it would be hard to adjust them without knowing more about what is failing.

I know that MySQL drivers require that 'ANSI quotes' mode is used. User pfisterhamj [msg=2444]recommended[/msg] putting this line in my.ini:
 
sql-mode = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ANSI_QUOTES".

Does that include something that might also apply to MS SQL? Pfisterhamj also noted that (again, for MySQL) the Unicode driver flavor should be used, not the ANSI version.
I'm volunteering as a moderator - I do not work for Cerious Software, Inc.

jwhyche

  • Guest
Thumbnail View Scrolling performance and caching
« Reply #13 on: 2015-11-10 13:41:12 »
Has there been any new updates with this issue?   I see this issue when I'm loading thumbnails from a remote database.  This issues isn't present when I move the database over to the local machine.