Re: [darktable-user] darktable 2.6 on Windows, performance issues with large database

[Marc Cousin]

On 10/03/2019 09:31, Pascal Obry wrote:

Ok, I may have an idea.

Can you do that:

- backup your library.db
- run

$ sqlite3 library.db

And enter the following command at the "sqlite >" prompt.

CREATE INDEX tagged_images_index ON tagged_images (imgid, tagid);

And test again. If still slow you may try:

CREATE INDEX tagged_images_imgid_index ON tagged_images (imgid);

Thanks.


Sorry for intruding, but as I'm a DBA guy… :)

That's a bit weird if these indexes improve anything:

(imgid,tagid) is the primary key, so it's auto-indexed.

And a composed index on (imgid,tagid) is perfectly capable of answering 
questions on predicates on imgid alone. In this 2-column tables case, 
it's theoretically even more efficient than having an index on only 
imgid, as it saves a trip to the table to fetch tagid (as the index 
contains all required columns).


So in adding these indexes improved anything, wouldn't it mean that 
there was something fishy on the table ? (missing primary key for instance…)


Regards


darktable user mailing list
to unsubscribe send a mail to darktable-user+unsubscr...@lists.darktable.org

Re: [darktable-user] darktable 2.6 on Windows, performance issues with large database

[Ed Gaillard]

Hi Pascal,

I'm pretty sure it's just the tagged_images_index that helps.

Ed


On Tue, Mar 12, 2019 at 4:27 AM Pascal Obry  wrote:

>
> Hello Ed,
>
> Can you give more information about which index has helped? Or both?
>
> I'd like to fix that for everyone, so I need more information. Thanks.
>
> --
>   Pascal Obry /  Magny Les Hameaux (78)
>
>   The best way to travel is by means of imagination
>
>   http://photos.obry.net
>   http://www.obry.net
>
>   gpg --keyserver keys.gnupg.net --recv-key F949BD3B
>


darktable user mailing list
to unsubscribe send a mail to darktable-user+unsubscr...@lists.darktable.org

Re: [darktable-user] darktable 2.6 on Windows, performance issues with large database

[Igor Zverev]

Hi,

I am new to Darktable, but I also have issues with large folders. For
example, Darktable cannot open a folder with one roll of 120 film scanned
at 2600 dpi. These are large files, so I have to split them up into two
folders. Otherwise I get a message about memory (or referencing memory,
can't remember) and it crashes. Can these fixes you guys have been
discussing help?

thank you,
i.

On Tue, Mar 12, 2019 at 9:04 AM Pascal Obry  wrote:

>
> Le mar. 12 mars 2019 à 13:06, Patrick Shanahan  a
> écrit :
>
>> disregard, I am NOT on windows.
>>
>
> And furthermore this is heavily related to the number of tags used.
>
> --
>   Pascal Obry /  Magny Les Hameaux (78)
>
>   The best way to travel is by means of imagination
>
>   http://photos.obry.net
>   http://www.obry.net
>
>   gpg --keyserver keys.gnupg.net --recv-key F949BD3B
>
> 
> darktable user mailing list to unsubscribe send a mail to
> darktable-user+unsubscr...@lists.darktable.org
>


darktable user mailing list
to unsubscribe send a mail to darktable-user+unsubscr...@lists.darktable.org

Re: [darktable-user] darktable 2.6 on Windows, performance issues with large database

[Pascal Obry]

Le mar. 12 mars 2019 à 13:06, Patrick Shanahan  a écrit :

> disregard, I am NOT on windows.
>

And furthermore this is heavily related to the number of tags used.

-- 
  Pascal Obry /  Magny Les Hameaux (78)

  The best way to travel is by means of imagination

  http://photos.obry.net
  http://www.obry.net

  gpg --keyserver keys.gnupg.net --recv-key F949BD3B


darktable user mailing list
to unsubscribe send a mail to darktable-user+unsubscr...@lists.darktable.org

Re: [darktable-user] darktable 2.6 on Windows, performance issues with large database

[Patrick Shanahan]

* Patrick Shanahan  [03-12-19 08:02]:
> * Pascal Obry  [03-12-19 04:30]:
> > Hello Ed,
> > 
> > Can you give more information about which index has helped? Or both?
> > 
> > I'd like to fix that for everyone, so I need more information. Thanks.
> 
> jftr: my db holds ~127000 images and isn't particularly slow and I work on
> a >10 year old i9 12-core.

disregard, I am NOT on windows.

-- 
(paka)Patrick Shanahan   Plainfield, Indiana, USA  @ptilopteri
http://en.opensuse.orgopenSUSE Community Memberfacebook/ptilopteri
Registered Linux User #207535@ http://linuxcounter.net
Photos: http://wahoo.no-ip.org/piwigo   paka @ IRCnet freenode

darktable user mailing list
to unsubscribe send a mail to darktable-user+unsubscr...@lists.darktable.org

Re: [darktable-user] darktable 2.6 on Windows, performance issues with large database

[Patrick Shanahan]

* Pascal Obry  [03-12-19 04:30]:
> Hello Ed,
> 
> Can you give more information about which index has helped? Or both?
> 
> I'd like to fix that for everyone, so I need more information. Thanks.

jftr: my db holds ~127000 images and isn't particularly slow and I work on
a >10 year old i9 12-core.

-- 
(paka)Patrick Shanahan   Plainfield, Indiana, USA  @ptilopteri
http://en.opensuse.orgopenSUSE Community Memberfacebook/ptilopteri
Registered Linux User #207535@ http://linuxcounter.net
Photos: http://wahoo.no-ip.org/piwigo   paka @ IRCnet freenode

darktable user mailing list
to unsubscribe send a mail to darktable-user+unsubscr...@lists.darktable.org

Re: [darktable-user] darktable 2.6 on Windows, performance issues with large database

[Pascal Obry]

Hello Ed,

Can you give more information about which index has helped? Or both?

I'd like to fix that for everyone, so I need more information. Thanks.

-- 
  Pascal Obry /  Magny Les Hameaux (78)

  The best way to travel is by means of imagination

  http://photos.obry.net
  http://www.obry.net

  gpg --keyserver keys.gnupg.net --recv-key F949BD3B


darktable user mailing list
to unsubscribe send a mail to darktable-user+unsubscr...@lists.darktable.org

Re: [darktable-user] darktable 2.6 on Windows, performance issues with large database

[Pascal Obry]

Le dimanche 10 mars 2019 à 19:27 -0400, Ed Gaillard a écrit :
> Thanks! That seems to have helped considerably.
>  (The tagged_images_imgid_index already existed, by the way).

I don't have it on my side! I have only tagged_images_tagid_index. So
we have to sort this out and know which index is really needed.

Thanks,

-- 
  Pascal Obry /  Magny Les Hameaux (78)

  The best way to travel is by means of imagination

  http://www.obry.net

  gpg --keyserver keys.gnupg.net --recv-key F949BD3B


darktable user mailing list
to unsubscribe send a mail to darktable-user+unsubscr...@lists.darktable.org

Re: [darktable-user] darktable 2.6 on Windows, performance issues with large database

[Pascal Obry]

Hi Ed,

> Yes, Pascal's suggestion does seem to have helped, especially with
> collections by tag, which now work as fast as by folder as far as I
> can tell. 

Great! Which of the two index has helped? I would have expected only
the first one would be needed.

-- 
  Pascal Obry /  Magny Les Hameaux (78)

  The best way to travel is by means of imagination

  http://www.obry.net

  gpg --keyserver keys.gnupg.net --recv-key F949BD3B


darktable user mailing list
to unsubscribe send a mail to darktable-user+unsubscr...@lists.darktable.org

Re: [darktable-user] darktable 2.6 on Windows, performance issues with large database

[Ed Gaillard]

Yes, Pascal's suggestion does seem to have helped, especially with
collections by tag, which now work as fast as by folder as far as I can
tell.

Ed



On Sun, Mar 10, 2019 at 7:30 PM August Schwerdfeger <
aug...@schwerdfeger.name> wrote:

> Yes (although there is still a substantial speed-up, especially for
> ratings).
>
> Have you tried Pascal's suggestion of adding new indices to the
> 'tagged_images' table? I am going to try that when I get the time.
> --
> August Schwerdfeger
> aug...@schwerdfeger.name
>
> On 3/10/19 3:29 PM, Ed Gaillard wrote:
>
> Thank you for the information. I'm glad to know that it isn't an issue
> with 2.6 or Windows.
>
> I see that collecting by film roll as you suggest (or by folder ) makes
> darktable much faster going to and from the darkroom, but it is still quite
> sluggish adding tags and ratings. Is that what you experience as well?
>
> Thanks,
> Ed
>
>
> On Sat, Mar 9, 2019 at 11:59 PM August Schwerdfeger <
> aug...@schwerdfeger.name> wrote:
>
>> I strongly doubt that your issues have anything to do with either
>> version 2.6 or Windows specifically. I have been using Darktable with
>> large databases for quite a while, and have encountered these same
>> issues on every operating system (except Windows) and every Darktable
>> version from 1.4 onward, despite continuous improvements (in early
>> versions, when the number of tags in the database increased, it took
>> minutes rather than seconds to tag an image).
>>
>> One thing that helps for me is to collect by film roll or color label
>> rather than by tag whenever possible -- for some reason, even image
>> editing in the darkroom mode is slower when collect-by-tag is being used.
>>
>> --
>> August Schwerdfeger
>> aug...@schwerdfeger.name
>>
>>
>> On 3/9/19 9:31 PM, Ed Gaillard wrote:
>> > Hi,
>> >
>> > I'm a new darktable user, using the Windows port. I have a database of
>> > about 20 photos, which I had tagged using AfterShot, and used a
>> > script found online to convert those XMP files to darktable's format,
>> > then began importing them into darktable a few folders at a time while
>> > also importing my new photos straight into  darktable. This all went
>> > well, so around the time I had 4 images in the database, I
>> > upgraded to 2.6 and then imported the rest of my photos all at once.
>> > Probably it was a mistake to do both of those at the same time..
>> >
>> > Anyway, some aspects of performance with this large database are very
>> > slow. Selecting multiple photos in the lighttable file manager mode
>> > (which I normally use) takes several seconds, applying a star rating
>> > to them takes many seconds (30 seconds or more); typing a tag in the
>> > metadata pane takes several seconds. Going from lighttable to darkroom
>> > takes a few seconds, but going back to light table takes nearly a
>> minute.
>> >
>> > On the other hand,collecting images using a tag is very very fast,
>> > which seems inconsistent with the other problems being down the size
>> > of the database. Also, when I switch to the zoomable light table, the
>> > slow operations (except for typing tags) are somewhat to much faster,
>> > but still too slow to be really usable.
>> >
>> > All this behavior persists on version 2.6.1, which I just installed.
>> > So, is anyone else using the Windows version with such a large
>> > database and having problems? Is there anything I can configure in
>> > darktable that might help? I suppose I could reimport the photos into
>> > several smaller databases, but that seems painful.
>> >
>> > Thanks,
>> > Ed Gaillard
>> >
>> >
>> >
>> >
>> >
>> 
>> > darktable user mailing list to unsubscribe send a mail to
>> > darktable-user+unsubscr...@lists.darktable.org
>>
>> 
>> darktable user mailing list
>> to unsubscribe send a mail to
>> darktable-user+unsubscr...@lists.darktable.org
>>
>>
> 
> darktable user mailing list to unsubscribe send a mail to
> darktable-user+unsubscr...@lists.darktable.org
>
>
> 
> darktable user mailing list to unsubscribe send a mail to
> darktable-user+unsubscr...@lists.darktable.org
>


darktable user mailing list
to unsubscribe send a mail to darktable-user+unsubscr...@lists.darktable.org

Re: [darktable-user] darktable 2.6 on Windows, performance issues with large database

[Ed Gaillard]

Thanks! That seems to have helped considerably.
(The tagged_images_imgid_index already existed, by the way).

It is still oddly slow applying star ratings (a few seconds for 2-12
selected images, seemingly not dependent on how many, but just one selected
is faster). Also, typing tags into the tagging text box is quite slow, I
guess because it's doing a lot of string compares to show the matching tag
list (about 1550 tags in my database). Is there a way to turn that off?

Anyway,  I think I can work with this without having to break up my
database.

Thanks,
Ed


On Sun, Mar 10, 2019 at 4:31 AM Pascal Obry  wrote:

>
> Ok, I may have an idea.
>
> Can you do that:
>
> - backup your library.db
> - run
>
> $ sqlite3 library.db
>
> And enter the following command at the "sqlite >" prompt.
>
> CREATE INDEX tagged_images_index ON tagged_images (imgid, tagid);
>
> And test again. If still slow you may try:
>
> CREATE INDEX tagged_images_imgid_index ON tagged_images (imgid);
>
> Thanks.
>
> --
>   Pascal Obry /  Magny Les Hameaux (78)
>
>   The best way to travel is by means of imagination
>
>   http://www.obry.net
>
>   gpg --keyserver keys.gnupg.net --recv-key F949BD3B
>
>


darktable user mailing list
to unsubscribe send a mail to darktable-user+unsubscr...@lists.darktable.org

Re: [darktable-user] darktable 2.6 on Windows, performance issues with large database

[August Schwerdfeger]

Yes (although there is still a substantial speed-up, especially for
ratings).

Have you tried Pascal's suggestion of adding new indices to the
'tagged_images' table? I am going to try that when I get the time.

--
August Schwerdfeger
aug...@schwerdfeger.name

On 3/10/19 3:29 PM, Ed Gaillard wrote:
> Thank you for the information. I'm glad to know that it isn't an issue
> with 2.6 or Windows. 
>
> I see that collecting by film roll as you suggest (or by folder )
> makes darktable much faster going to and from the darkroom, but it is
> still quite sluggish adding tags and ratings. Is that what you
> experience as well? 
>
> Thanks,
> Ed
>
>
> On Sat, Mar 9, 2019 at 11:59 PM August Schwerdfeger
> mailto:aug...@schwerdfeger.name>> wrote:
>
> I strongly doubt that your issues have anything to do with either
> version 2.6 or Windows specifically. I have been using Darktable with
> large databases for quite a while, and have encountered these same
> issues on every operating system (except Windows) and every Darktable
> version from 1.4 onward, despite continuous improvements (in early
> versions, when the number of tags in the database increased, it took
> minutes rather than seconds to tag an image).
>
> One thing that helps for me is to collect by film roll or color label
> rather than by tag whenever possible -- for some reason, even image
> editing in the darkroom mode is slower when collect-by-tag is
> being used.
>
> --
> August Schwerdfeger
> aug...@schwerdfeger.name 
>
>
> On 3/9/19 9:31 PM, Ed Gaillard wrote:
> > Hi,
> >
> > I'm a new darktable user, using the Windows port. I have a
> database of
> > about 20 photos, which I had tagged using AfterShot, and used a
> > script found online to convert those XMP files to darktable's
> format,
> > then began importing them into darktable a few folders at a time
> while
> > also importing my new photos straight into  darktable. This all went
> > well, so around the time I had 4 images in the database, I
> > upgraded to 2.6 and then imported the rest of my photos all at once.
> > Probably it was a mistake to do both of those at the same time..
> >
> > Anyway, some aspects of performance with this large database are
> very
> > slow. Selecting multiple photos in the lighttable file manager mode
> > (which I normally use) takes several seconds, applying a star rating
> > to them takes many seconds (30 seconds or more); typing a tag in the
> > metadata pane takes several seconds. Going from lighttable to
> darkroom
> > takes a few seconds, but going back to light table takes nearly
> a minute.
> >
> > On the other hand,collecting images using a tag is very very fast,
> > which seems inconsistent with the other problems being down the size
> > of the database. Also, when I switch to the zoomable light
> table, the
> > slow operations (except for typing tags) are somewhat to much
> faster,
> > but still too slow to be really usable. 
> >
> > All this behavior persists on version 2.6.1, which I just installed.
> > So, is anyone else using the Windows version with such a large
> > database and having problems? Is there anything I can configure in
> > darktable that might help? I suppose I could reimport the photos
> into
> > several smaller databases, but that seems painful.
> >
> > Thanks,
> > Ed Gaillard
> >
> >
> >
> >
> >
> 
> 
> > darktable user mailing list to unsubscribe send a mail to
> > darktable-user+unsubscr...@lists.darktable.org
> 
> 
> 
> darktable user mailing list
> to unsubscribe send a mail to
> darktable-user+unsubscr...@lists.darktable.org
> 
>
>
> 
> darktable user mailing list to unsubscribe send a mail to
> darktable-user+unsubscr...@lists.darktable.org


darktable user mailing list
to unsubscribe send a mail to darktable-user+unsubscr...@lists.darktable.org

Re: [darktable-user] darktable 2.6 on Windows, performance issues with large database

[Ed Gaillard]

Thank you for the information. I'm glad to know that it isn't an issue with
2.6 or Windows.

I see that collecting by film roll as you suggest (or by folder ) makes
darktable much faster going to and from the darkroom, but it is still quite
sluggish adding tags and ratings. Is that what you experience as well?

Thanks,
Ed


On Sat, Mar 9, 2019 at 11:59 PM August Schwerdfeger <
aug...@schwerdfeger.name> wrote:

> I strongly doubt that your issues have anything to do with either
> version 2.6 or Windows specifically. I have been using Darktable with
> large databases for quite a while, and have encountered these same
> issues on every operating system (except Windows) and every Darktable
> version from 1.4 onward, despite continuous improvements (in early
> versions, when the number of tags in the database increased, it took
> minutes rather than seconds to tag an image).
>
> One thing that helps for me is to collect by film roll or color label
> rather than by tag whenever possible -- for some reason, even image
> editing in the darkroom mode is slower when collect-by-tag is being used.
>
> --
> August Schwerdfeger
> aug...@schwerdfeger.name
>
>
> On 3/9/19 9:31 PM, Ed Gaillard wrote:
> > Hi,
> >
> > I'm a new darktable user, using the Windows port. I have a database of
> > about 20 photos, which I had tagged using AfterShot, and used a
> > script found online to convert those XMP files to darktable's format,
> > then began importing them into darktable a few folders at a time while
> > also importing my new photos straight into  darktable. This all went
> > well, so around the time I had 4 images in the database, I
> > upgraded to 2.6 and then imported the rest of my photos all at once.
> > Probably it was a mistake to do both of those at the same time..
> >
> > Anyway, some aspects of performance with this large database are very
> > slow. Selecting multiple photos in the lighttable file manager mode
> > (which I normally use) takes several seconds, applying a star rating
> > to them takes many seconds (30 seconds or more); typing a tag in the
> > metadata pane takes several seconds. Going from lighttable to darkroom
> > takes a few seconds, but going back to light table takes nearly a minute.
> >
> > On the other hand,collecting images using a tag is very very fast,
> > which seems inconsistent with the other problems being down the size
> > of the database. Also, when I switch to the zoomable light table, the
> > slow operations (except for typing tags) are somewhat to much faster,
> > but still too slow to be really usable.
> >
> > All this behavior persists on version 2.6.1, which I just installed.
> > So, is anyone else using the Windows version with such a large
> > database and having problems? Is there anything I can configure in
> > darktable that might help? I suppose I could reimport the photos into
> > several smaller databases, but that seems painful.
> >
> > Thanks,
> > Ed Gaillard
> >
> >
> >
> >
> >
> 
> > darktable user mailing list to unsubscribe send a mail to
> > darktable-user+unsubscr...@lists.darktable.org
>
> 
> darktable user mailing list
> to unsubscribe send a mail to
> darktable-user+unsubscr...@lists.darktable.org
>
>


darktable user mailing list
to unsubscribe send a mail to darktable-user+unsubscr...@lists.darktable.org

Re: [darktable-user] darktable 2.6 on Windows, performance issues with large database

[Pascal Obry]

Ok, I may have an idea.

Can you do that:

- backup your library.db
- run 

$ sqlite3 library.db

And enter the following command at the "sqlite >" prompt.

CREATE INDEX tagged_images_index ON tagged_images (imgid, tagid);

And test again. If still slow you may try:

CREATE INDEX tagged_images_imgid_index ON tagged_images (imgid);

Thanks.

-- 
  Pascal Obry /  Magny Les Hameaux (78)

  The best way to travel is by means of imagination

  http://www.obry.net

  gpg --keyserver keys.gnupg.net --recv-key F949BD3B


darktable user mailing list
to unsubscribe send a mail to darktable-user+unsubscr...@lists.darktable.org

Re: [darktable-user] darktable 2.6 on Windows, performance issues with large database

[August Schwerdfeger]

I strongly doubt that your issues have anything to do with either
version 2.6 or Windows specifically. I have been using Darktable with
large databases for quite a while, and have encountered these same
issues on every operating system (except Windows) and every Darktable
version from 1.4 onward, despite continuous improvements (in early
versions, when the number of tags in the database increased, it took
minutes rather than seconds to tag an image).

One thing that helps for me is to collect by film roll or color label
rather than by tag whenever possible -- for some reason, even image
editing in the darkroom mode is slower when collect-by-tag is being used.

--
August Schwerdfeger
aug...@schwerdfeger.name


On 3/9/19 9:31 PM, Ed Gaillard wrote:
> Hi,
>
> I'm a new darktable user, using the Windows port. I have a database of
> about 20 photos, which I had tagged using AfterShot, and used a
> script found online to convert those XMP files to darktable's format,
> then began importing them into darktable a few folders at a time while
> also importing my new photos straight into  darktable. This all went
> well, so around the time I had 4 images in the database, I
> upgraded to 2.6 and then imported the rest of my photos all at once.
> Probably it was a mistake to do both of those at the same time..
>
> Anyway, some aspects of performance with this large database are very
> slow. Selecting multiple photos in the lighttable file manager mode
> (which I normally use) takes several seconds, applying a star rating
> to them takes many seconds (30 seconds or more); typing a tag in the
> metadata pane takes several seconds. Going from lighttable to darkroom
> takes a few seconds, but going back to light table takes nearly a minute.
>
> On the other hand,collecting images using a tag is very very fast,
> which seems inconsistent with the other problems being down the size
> of the database. Also, when I switch to the zoomable light table, the
> slow operations (except for typing tags) are somewhat to much faster,
> but still too slow to be really usable. 
>
> All this behavior persists on version 2.6.1, which I just installed.
> So, is anyone else using the Windows version with such a large
> database and having problems? Is there anything I can configure in
> darktable that might help? I suppose I could reimport the photos into
> several smaller databases, but that seems painful.
>
> Thanks,
> Ed Gaillard
>
>
>
>
> 
> darktable user mailing list to unsubscribe send a mail to
> darktable-user+unsubscr...@lists.darktable.org

darktable user mailing list
to unsubscribe send a mail to darktable-user+unsubscr...@lists.darktable.org

[darktable-user] darktable 2.6 on Windows, performance issues with large database

[Ed Gaillard]

Hi,

I'm a new darktable user, using the Windows port. I have a database of
about 20 photos, which I had tagged using AfterShot, and used a script
found online to convert those XMP files to darktable's format, then began
importing them into darktable a few folders at a time while also importing
my new photos straight into  darktable. This all went well, so around the
time I had 4 images in the database, I upgraded to 2.6 and then
imported the rest of my photos all at once. Probably it was a mistake to do
both of those at the same time..

Anyway, some aspects of performance with this large database are very slow.
Selecting multiple photos in the lighttable file manager mode (which I
normally use) takes several seconds, applying a star rating to them takes
many seconds (30 seconds or more); typing a tag in the metadata pane takes
several seconds. Going from lighttable to darkroom takes a few seconds, but
going back to light table takes nearly a minute.

On the other hand,collecting images using a tag is very very fast, which
seems inconsistent with the other problems being down the size of the
database. Also, when I switch to the zoomable light table, the slow
operations (except for typing tags) are somewhat to much faster, but still
too slow to be really usable.

All this behavior persists on version 2.6.1, which I just installed. So, is
anyone else using the Windows version with such a large database and having
problems? Is there anything I can configure in darktable that might help? I
suppose I could reimport the photos into several smaller databases, but
that seems painful.

Thanks,
Ed Gaillard


darktable user mailing list
to unsubscribe send a mail to darktable-user+unsubscr...@lists.darktable.org