Thank you very much! Your query is exactly what I was looking for. If I understand you correctly, the main bottleneck is the count in the inner select - is that correct? (disregarding the overall loop for all groups)
Am 05.10.2010 um 12:24 schrieb Drake Wilson: > Quoth Mail <sv...@gmx.net>, on 2010-10-05 11:43:29 +0200: >> SELECT COUNT(DISTINCT t0.Z_PK) FROM ZFFFOOD t0 WHERE (t0.ZFOODGROUP >> = ? AND (SELECT COUNT(*) FROM ZFFWEIGHT t1 WHERE (t0.Z_PK = >> t1.ZFOOD) ) > ?) > > There should not need to be a DISTINCT when talking about a primary > key. They will already be distinct by definition, no? (I'm assuming > your primary keys will not be null, even though you don't have a NOT > NULL constraint on the column.) > > Aside from that, if you want "at least one", EXISTS is the obvious > operator to use rather than first requesting the count (and having to > iterate all the rows even though only the first one is needed to > demonstrate existence.) > >> My current task is to get the number of foods that belong to each >> group and have at least one weight data related to them. > > That suggests something like: > > SELECT g.Z_PK AS "group", COUNT(f.Z_PK) AS "count" > FROM ZFFFOODGROUP g > INNER JOIN ZFFFOOD f ON f.ZFOODGROUP = g.Z_PK > WHERE EXISTS (SELECT w.Z_PK FROM ZFFWEIGHT w WHERE w.ZFOOD = f.Z_PK) > GROUP BY g.Z_PK; > > (I think the WHERE EXISTS could be done with another join, but it > would probably be more convoluted.) > >> When inserting 12 for ZFOODGROUP and 0 for count (I never check for >> another count value here, it's always 0), I get a result of 761 >> which takes 0.0591 seconds. As I have to fetch the counts of all >> available groups, this query is sent in a loop which easily takes >> several seconds to finish. > > Do the loop in the query itself using GROUP BY instead, if you can, > rather than issuing several queries. > > My local, unrigorous measurements suggest that on my machine, with the > provided example database, your original query takes 40 ms of CPU per > run and the above takes 16 ms total; I imagine the use of GROUP BY and > EXISTS and the lack of the extra DISTINCT are the primary factors, but > I haven't checked thoroughly enough to say so confidently. I'm using > SQLite 3.7.2 on Debian GNU/Linux sid AMD64. > > ---> Drake Wilson > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
