Hello, below is a theoretical query that becomes slow when the number
of rows increases. What it does is:
- scan input cases in table a
- for each input case:
-- determine the smallest value of attribute size of elements in table
ab
-- count the number of elements having this smallest size
With 3 rows in table a and 3*1000 in ab this takes already several
seconds.
I'm not so much interested in an alternative solution, though
interesting, and merely want to show an inefficient construction. That
is a sub-sub-query correlated directly to the main query.
Thanks, E. Pasma
.version
SQLite 3.19.3 2017-06-08 14:26:17 ...
create table a (a, primary key (a))
;
create table ab (a, b, size, primary key (a,b))
;
insert into a
with i as (select 1 as i union all select i+1 from i where i<3)
select i from i
;
insert into ab
with i as (select 1 as i union all select i+1 from i where i<1000)
select a, i as b, random()%10 as size from a, i
;
.eqp on
.timer on
select a,
(
select count(*)
from ab
where a=a.a
and size=(select min(size) from ab where a=a.a)
)
from a
;
--EQP-- 0,0,0,SCAN TABLE a
--EQP-- 0,0,0,EXECUTE CORRELATED SCALAR SUBQUERY 1
--EQP-- 1,0,0,SEARCH TABLE ab USING INDEX sqlite_autoindex_ab_1 (a=?)
--EQP-- 1,0,0,EXECUTE CORRELATED SCALAR SUBQUERY 2
--EQP-- 2,0,0,SEARCH TABLE ab USING INDEX sqlite_autoindex_ab_1 (a=?)
1|56
2|53
3|49
Run Time: real 2.678 user 2.597794 sys 0.008801
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
