[erlang-questions] QLC doesn't use lookup joins on bare values

[Bernard Duggan]

What Sam gave is just a minimal example to demonstrate the behaviour in
question.  Obviously we're not actually using a whole qlc query just to
produce the results of a simple table generator.

Cheers,

B

Hynek Vychodil wrote:
> I don't understand why you do it. You should write simple:
> 
> three(TA, TB) ->
>        QLC = qlc:q([B || {A} <- ets:table(TA), {_,B} <- ets:table(TB), A =:=
> B]),
>        do("two", QLC).
> 
> With N = 10000:
> $ erl -noshell -run question
> Query Info for query "one": qlc:q([element(2, B) ||
>            A <-
>                ets:table(8204,
>                          [{traverse,{select,[{{'$1'},[true],['$1']}]}}]),
>            B <- ets:table(12301),
>            A =:= element(2, B)])
> Query time: 28.2231s
> Results: 10000
> Query Info for query "two": begin
>     V1 =
>         qlc:q([P0 ||
>                    P0 = {A} <- qlc:keysort(1, ets:table(8204), [])]),
>     V2 =
>         qlc:q([[G1|B] ||
>                    G1 <- V1,
>                    B <- qlc:keysort(2, ets:table(12301), []),
>                    element(1, G1) == element(2, B)],
>               [{join,merge}]),
>     qlc:q([element(2, B) ||
>                [{A}|B] <- V2,
>                A =:= element(2, B)])
> end
> Query time: 0.036186s
> Results: 10000
> Query Info for query "three": begin
>     V1 =
>         qlc:q([P0 ||
>                    P0 = {_,B} <- ets:table(12301)]),
>     V2 =
>         qlc:q([[G1|G2] ||
>                    G2 <- V1,
>                    G1 <- ets:table(8204),
>                    element(2, G1) =:= element(1, G2)],
>               [{join,lookup}]),
>     qlc:q([B ||
>                [{A}|{_,B}] <- V2,
>                A =:= B])
> end
> Query time: 0.016635s
> Results: 10000
> 
> On Fri, Apr 17, 2009 at 4:00 AM, Sam Bobroff <samb@REDACTED> wrote:
> 
> Hi Erlangers,
> 
> I've discovered that when performing a QLC query, QLC is unable to use
> a fast join unless the values being returned by both generators are
> wrapped as tuples.
> 
> Does anyone know if this is caused just because that pattern isn't
> recognised by QLC? Would it be worth adding this as a pattern?
> 
> Here's an example (notice that "one" is done by traversal and is slow,
> while "two" is done by a merge join and is fast):
> 
> -module(question).
> -export([start/0]).
> 
> -include_lib("stdlib/include/qlc.hrl").
> 
> -define(N, 1000).
> 
> start() ->
>        TA = ets:new(ta, []),
>        TB = ets:new(tb, []),
>        [ ets:insert(TA, {X}) || X <- lists:seq(1, ?N) ],
>        [ ets:insert(TB, {X, $a - 1 + (X rem 26)}) || X <- lists:seq(1, ?N)
> ],
>        one(TA, TB),
>        two(TA, TB),
>        init:stop().
> 
> one(TA, TB) ->
>        TMP = qlc:q([A || {A} <- ets:table(TA)]),
>        QLC = qlc:q([element(2, B) || A <- TMP, B <- ets:table(TB), A =:=
> element(2, B)]),
>        do("one", QLC).
> 
> two(TA, TB) ->
>        TMP = qlc:q([A || A <- ets:table(TA)]),
>        QLC = qlc:q([element(2, B) || {A} <- TMP, B <- ets:table(TB), A =:=
> element(2, B)]),
>        do("two", QLC).
> 
> do(Name, QLC) ->
>        io:fwrite("Query Info for query ~p: ~s\n", [Name, qlc:info(QLC)]),
>        ST = now(),
>        Result = qlc:e(QLC),
>        ET = now(),
>        io:fwrite("Query time: ~ps\nResults: ~p\n", [timer:now_diff(ET, ST)
> / 1000000, length(Result)]).
> 
> Running this on my machine (running Erlang R12B):
> 
> $ erlc question.erl && erl -noshell -run question
> Query Info for query "one": qlc:q([element(2, B) ||
>           A <-
>               ets:table(8204,
>                         [{traverse,{select,[{{'$1'},[true],['$1']}]}}]),
>           B <- ets:table(12301),
>           A =:= element(2, B)])
> Query time: 0.908338s
> Results: 1000
> Query Info for query "two": begin
>    V1 =
>        qlc:q([P0 ||
>                   P0 = {A} <- qlc:keysort(1, ets:table(8204), [])]),
>    V2 =
>        qlc:q([[G1|B] ||
>                   G1 <- V1,
>                   B <- qlc:keysort(2, ets:table(12301), []),
>                   element(1, G1) == element(2, B)],
>              [{join,merge}]),
>    qlc:q([element(2, B) ||
>               [{A}|B] <- V2,
>               A =:= element(2, B)])
> end
> Query time: 0.010174s
> Results: 1000
> 
> Cheers,
> Sam.
_______________________________________________
erlang-questions mailing list
erlang-questions@REDACTED
http://www.erlang.org/mailman/listinfo/erlang-questions
>>

> ------------------------------------------------------------------------

> _______________________________________________
> erlang-questions mailing list
> erlang-questions@REDACTED
> http://www.erlang.org/mailman/listinfo/erlang-questions