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

[Hynek Vychodil]

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:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> 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.
> - --
> Sam Bobroff | sam@REDACTED | M5 Networks
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.9 (GNU/Linux)
>
> iEYEARECAAYFAknn4sMACgkQm97/UHSa/AQJ1wCfa5OxpvF+RngKfFniuXQxWZ8i
> BxMAnifufDoqp0ZrRYdd4oHILmPyBCez
> =250i
> -----END PGP SIGNATURE-----
> _______________________________________________
> erlang-questions mailing list
> erlang-questions@REDACTED
> http://www.erlang.org/mailman/listinfo/erlang-questions
>



-- 
--Hynek (Pichi) Vychodil

Analyze your data in minutes. Share your insights instantly. Thrill your
boss.  Be a data hero!
Try Good Data now for free: www.gooddata.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://erlang.org/pipermail/erlang-questions/attachments/20090417/37dab83b/attachment.htm>