extragear/graphics/digikam/libs/database

SVN commit 973925 by aclemens:

Only fetch statistics for active elements.

Marcel, Gilles,

there is some query in the function that doesn't seem to do anything. Is
this old code? I have commented it for now.

Statistics seems to work fine for images now, but somehow I get 12
movies more in another test database, as when I recursively display the
movie folder.

Need to check if it is a problem with my DB.

Andi

CCMAIL::[hidden email]

 M  +10 -3     albumdb.cpp  


--- trunk/extragear/graphics/digikam/libs/database/albumdb.cpp #973924:973925
@@ -2043,13 +2043,20 @@
     QList<QVariant> values, allFormats;
     QMap<QString, int>  map;
 
-    d->db->execSql("SELECT DISTINCT format FROM ImageInformation;", &allFormats);
+    //    d->db->execSql("SELECT DISTINCT format FROM ImageInformation;", &allFormats);
+    d->db->execSql("SELECT DISTINCT II.format "
+                   "    FROM ImageInformation AS II, Images "
+                   "    WHERE Images.id == II.imageid AND Images.status == 1;", &allFormats);
 
-    QSqlQuery query = d->db->prepareQuery( QString("SELECT count() FROM ImageInformation WHERE format=?;") );
+    // FIXME: What is this for? Old code?
+    //        I disabled it for now because it seems to do nothing.
+    //    QSqlQuery query = d->db->prepareQuery( QString("SELECT count() FROM ImageInformation WHERE format=?;") );
 
     foreach (const QVariant &format, allFormats)
     {
-        d->db->execSql("SELECT count() FROM ImageInformation WHERE format=?;", format, &values);
+        d->db->execSql("SELECT count() FROM ImageInformation AS II, Images "
+                       "    WHERE II.imageid == Images.id AND Images.status== 1 "
+                       "        AND format=?;", format, &values);
         map[format.toString()] = values.isEmpty() ? 0 : values.first().toInt();
     }
 
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

No, everything is fine, I just overlooked that I have some videos in the
"images" testfolder :D

Andi

On Thursday 28 May 2009 10:28:05 Andi Clemens wrote:

> SVN commit 973925 by aclemens:
>
> Only fetch statistics for active elements.
>
> Marcel, Gilles,
>
> there is some query in the function that doesn't seem to do anything. Is
> this old code? I have commented it for now.
>
> Statistics seems to work fine for images now, but somehow I get 12
> movies more in another test database, as when I recursively display the
> movie folder.
>
> Need to check if it is a problem with my DB.
>
> Andi
>
> CCMAIL::[hidden email]
>
>  M  +10 -3     albumdb.cpp
>
>
> --- trunk/extragear/graphics/digikam/libs/database/albumdb.cpp
> #973924:973925 @@ -2043,13 +2043,20 @@
>      QList<QVariant> values, allFormats;
>      QMap<QString, int>  map;
>
> -    d->db->execSql("SELECT DISTINCT format FROM ImageInformation;",
> &allFormats); +    //    d->db->execSql("SELECT DISTINCT format FROM
> ImageInformation;", &allFormats); +    d->db->execSql("SELECT DISTINCT
> II.format "
> +                   "    FROM ImageInformation AS II, Images "
> +                   "    WHERE Images.id == II.imageid AND Images.status ==
> 1;", &allFormats);
>
> -    QSqlQuery query = d->db->prepareQuery( QString("SELECT count() FROM
> ImageInformation WHERE format=?;") ); +    // FIXME: What is this for? Old
> code?
> +    //        I disabled it for now because it seems to do nothing.
> +    //    QSqlQuery query = d->db->prepareQuery( QString("SELECT count()
> FROM ImageInformation WHERE format=?;") );
>
>      foreach (const QVariant &format, allFormats)
>      {
> -        d->db->execSql("SELECT count() FROM ImageInformation WHERE
> format=?;", format, &values); +        d->db->execSql("SELECT count() FROM
> ImageInformation AS II, Images " +                       "    WHERE
> II.imageid == Images.id AND Images.status== 1 " +                       "  
>      AND format=?;", format, &values); map[format.toString()] =
> values.isEmpty() ? 0 : values.first().toInt(); }
>
> _______________________________________________
> Digikam-devel mailing list
> [hidden email]
> https://mail.kde.org/mailman/listinfo/digikam-devel

_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Marcel Wiesweg]

Thanks Andi. I am out of exercise with the database code ;-)
The query their is not old but just left over, I will remove it.
Btw I would have coded the statement like this:

SELECT count() FROM Images
LEFT JOIN ImageInformation ON imageid == id
WHERE Images.status== 1 AND ImageInformation.format='JPG';

Do you know pros and cons of either variant?

Marcel

_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

All I know is that LEFT JOIN seems to generate more internal SQL code, which
might be a little bit slower.
There is also some statement to see what SQL is doing internally, but I forgot
it.

I think I have a bookmark on this topic, I will attach it to this thread when
I find it.

I learned it this way, never used LEFT JOIN. But I don't think that it will
make any difference in our case.

Andi

On Thursday 28 May 2009 20:47:08 Marcel Wiesweg wrote:

> Thanks Andi. I am out of exercise with the database code ;-)
> The query their is not old but just left over, I will remove it.
> Btw I would have coded the statement like this:
>
> SELECT count() FROM Images
> LEFT JOIN ImageInformation ON imageid == id
> WHERE Images.status== 1  AND ImageInformation.format='JPG';
>
> Do you know pros and cons of either variant?
>
> Marcel

_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

I found only a quote now:

"So when you use LEFT JOIN, that forces a particular ordering  
of tables, and greatly restricts the query engines opportunities to  
optimize.  So you should avoid using LEFT JOIN if you don't really  
need it."

Seems that LEFT JOIN will not be optimized like the normal table connection by
WHERE.

Andi

On Thursday 28 May 2009 20:54:24 Andi Clemens wrote:

> All I know is that LEFT JOIN seems to generate more internal SQL code,
> which might be a little bit slower.
> There is also some statement to see what SQL is doing internally, but I
> forgot it.
>
> I think I have a bookmark on this topic, I will attach it to this thread
> when I find it.
>
> I learned it this way, never used LEFT JOIN. But I don't think that it will
> make any difference in our case.
>
> Andi
>
> On Thursday 28 May 2009 20:47:08 Marcel Wiesweg wrote:
> > Thanks Andi. I am out of exercise with the database code ;-)
> > The query their is not old but just left over, I will remove it.
> > Btw I would have coded the statement like this:
> >
> > SELECT count() FROM Images
> > LEFT JOIN ImageInformation ON imageid == id
> > WHERE Images.status== 1  AND ImageInformation.format='JPG';
> >
> > Do you know pros and cons of either variant?
> >
> > Marcel
>
> _______________________________________________
> Digikam-devel mailing list
> [hidden email]
> https://mail.kde.org/mailman/listinfo/digikam-devel

_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

Ok here is some link, not the one I thought of, but it also says that LEFT
JOIN can restrict ordering of the tables and therefore restrict optimization:

http://www.mail-archive.com/sqlite-users@.../msg39455.html

The

a JOIN b ON a.x = b.x

should be the same as

a,b WHERE a.x = b.x

in our case.

Andi

On Thursday 28 May 2009 20:58:02 Andi Clemens wrote:

> I found only a quote now:
>
> "So when you use LEFT JOIN, that forces a particular ordering
> of tables, and greatly restricts the query engines opportunities to
> optimize.  So you should avoid using LEFT JOIN if you don't really
> need it."
>
> Seems that LEFT JOIN will not be optimized like the normal table connection
> by WHERE.
>
> Andi
>
> On Thursday 28 May 2009 20:54:24 Andi Clemens wrote:
> > All I know is that LEFT JOIN seems to generate more internal SQL code,
> > which might be a little bit slower.
> > There is also some statement to see what SQL is doing internally, but I
> > forgot it.
> >
> > I think I have a bookmark on this topic, I will attach it to this thread
> > when I find it.
> >
> > I learned it this way, never used LEFT JOIN. But I don't think that it
> > will make any difference in our case.
> >
> > Andi
> >
> > On Thursday 28 May 2009 20:47:08 Marcel Wiesweg wrote:
> > > Thanks Andi. I am out of exercise with the database code ;-)
> > > The query their is not old but just left over, I will remove it.
> > > Btw I would have coded the statement like this:
> > >
> > > SELECT count() FROM Images
> > > LEFT JOIN ImageInformation ON imageid == id
> > > WHERE Images.status== 1  AND ImageInformation.format='JPG';
> > >
> > > Do you know pros and cons of either variant?
> > >
> > > Marcel
> >
> > _______________________________________________
> > Digikam-devel mailing list
> > [hidden email]
> > https://mail.kde.org/mailman/listinfo/digikam-devel
>
> _______________________________________________
> Digikam-devel mailing list
> [hidden email]
> https://mail.kde.org/mailman/listinfo/digikam-devel

_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

Ok one last note here: In the link above it is explained quite well I think.
So LEFT JOIN can generate performance issues, especially when used nested.
The thread also is talking about the EXPLAIN command, which I was talking
about before.
It is used to see which instructions SQL is doing behind the scenes.
LEFT JOINs will mostly generate more instructions, and sometimes performance
loss is really visible (someone mentioned in the link that he dropped
execution time from 600ms to 20ms or 30ms, which is quite good).

Andi

On Thursday 28 May 2009 21:05:51 Andi Clemens wrote:

> Ok here is some link, not the one I thought of, but it also says that LEFT
> JOIN can restrict ordering of the tables and therefore restrict
> optimization:
>
> http://www.mail-archive.com/sqlite-users@.../msg39455.html
>
> The
>
> a JOIN b ON a.x = b.x
>
> should be the same as
>
> a,b WHERE a.x = b.x
>
> in our case.
>
> Andi
>
> On Thursday 28 May 2009 20:58:02 Andi Clemens wrote:
> > I found only a quote now:
> >
> > "So when you use LEFT JOIN, that forces a particular ordering
> > of tables, and greatly restricts the query engines opportunities to
> > optimize.  So you should avoid using LEFT JOIN if you don't really
> > need it."
> >
> > Seems that LEFT JOIN will not be optimized like the normal table
> > connection by WHERE.
> >
> > Andi
> >
> > On Thursday 28 May 2009 20:54:24 Andi Clemens wrote:
> > > All I know is that LEFT JOIN seems to generate more internal SQL code,
> > > which might be a little bit slower.
> > > There is also some statement to see what SQL is doing internally, but I
> > > forgot it.
> > >
> > > I think I have a bookmark on this topic, I will attach it to this
> > > thread when I find it.
> > >
> > > I learned it this way, never used LEFT JOIN. But I don't think that it
> > > will make any difference in our case.
> > >
> > > Andi
> > >
> > > On Thursday 28 May 2009 20:47:08 Marcel Wiesweg wrote:
> > > > Thanks Andi. I am out of exercise with the database code ;-)
> > > > The query their is not old but just left over, I will remove it.
> > > > Btw I would have coded the statement like this:
> > > >
> > > > SELECT count() FROM Images
> > > > LEFT JOIN ImageInformation ON imageid == id
> > > > WHERE Images.status== 1  AND ImageInformation.format='JPG';
> > > >
> > > > Do you know pros and cons of either variant?
> > > >
> > > > Marcel
> > >
> > > _______________________________________________
> > > Digikam-devel mailing list
> > > [hidden email]
> > > https://mail.kde.org/mailman/listinfo/digikam-devel
> >
> > _______________________________________________
> > Digikam-devel mailing list
> > [hidden email]
> > https://mail.kde.org/mailman/listinfo/digikam-devel
>
> _______________________________________________
> Digikam-devel mailing list
> [hidden email]
> https://mail.kde.org/mailman/listinfo/digikam-devel

_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Marcel Wiesweg]

> Ok one last note here: In the link above it is explained quite well I
> think. So LEFT JOIN can generate performance issues, especially when used
> nested. The thread also is talking about the EXPLAIN command, which I was
> talking about before.
> It is used to see which instructions SQL is doing behind the scenes.
> LEFT JOINs will mostly generate more instructions, and sometimes
> performance loss is really visible (someone mentioned in the link that he
> dropped execution time from 600ms to 20ms or 30ms, which is quite good).

Ok following this we can probably change quite a few "LEFT JOIN"s in our SQL into "JOIN"s.

_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel