dirty_select a limited number of records

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

dirty_select a limited number of records

Roberto Ostinelli-5
All,
I've got a mnesia table that I create with:

mnesia:create_table(my_table, [
    {type, set},
    {attributes, record_info(fields, my_table)},
    {index, [#my_table.pid]},
    {storage_properties, [{ets, [{read_concurrency, true}, {write_concurrency, true}]}]}
]).


If I want to get all records for the secondary index, I do:

mnesia:dirty_index_read(my_table, Pid, #my_table.pid).

This operation if very fast. Now, sometimes I just need the first matching record and if there are many entries with the secondary index, the operation becomes a bottleneck. So I'm trying:

S = fun() ->
    MatchHead = #my_table{pid = Pid, _ = '_'},
    Guards = [],
    Result = '$_',
    mnesia:select(my_table, [{MatchHead, Guards, [Result]}], 1, read)
end,
case mnesia:activity(sync_dirty, S) of
    {[Entry], _} -> Entry;
    _ -> undefined
end.


This works, however this operation does not seem to be using the secondary index as it becomes extremely slow. I also tried:

S = fun() ->
    MatchHead = #my_table{pid = '$1', _ = '_'},
    Guard = {'=:=', '$1', Pid},
    Result = '$_',
    mnesia:select(my_table, [{MatchHead, [Guard], [Result]}], 1, read)
end,
case mnesia:activity(sync_dirty, S) of
    '$end_of_table' -> undefined;
    {[Entry], _} -> Entry
end.


And this is also slow. What can I do to dirty_select a limited number of records from a mnesia table?

Thank you,
r.
Reply | Threaded
Open this post in threaded view
|

Re: dirty_select a limited number of records

Roberto Ostinelli-5
Here's another try, this one too is very slow:

QH = qlc:q([E || E <- mnesia:table(my_table), E#my_table.pid == Pid]),
mnesia:async_dirty(fun() ->
    QC = qlc:cursor(QH),
    R = qlc:next_answers(QC, N),
    qlc:delete_cursor(QC),
    R
end).

Any ideas?



On Tue, Nov 26, 2019 at 6:33 PM Roberto Ostinelli <[hidden email]> wrote:
All,
I've got a mnesia table that I create with:

mnesia:create_table(my_table, [
    {type, set},
    {attributes, record_info(fields, my_table)},
    {index, [#my_table.pid]},
    {storage_properties, [{ets, [{read_concurrency, true}, {write_concurrency, true}]}]}
]).


If I want to get all records for the secondary index, I do:

mnesia:dirty_index_read(my_table, Pid, #my_table.pid).

This operation if very fast. Now, sometimes I just need the first matching record and if there are many entries with the secondary index, the operation becomes a bottleneck. So I'm trying:

S = fun() ->
    MatchHead = #my_table{pid = Pid, _ = '_'},
    Guards = [],
    Result = '$_',
    mnesia:select(my_table, [{MatchHead, Guards, [Result]}], 1, read)
end,
case mnesia:activity(sync_dirty, S) of
    {[Entry], _} -> Entry;
    _ -> undefined
end.


This works, however this operation does not seem to be using the secondary index as it becomes extremely slow. I also tried:

S = fun() ->
    MatchHead = #my_table{pid = '$1', _ = '_'},
    Guard = {'=:=', '$1', Pid},
    Result = '$_',
    mnesia:select(my_table, [{MatchHead, [Guard], [Result]}], 1, read)
end,
case mnesia:activity(sync_dirty, S) of
    '$end_of_table' -> undefined;
    {[Entry], _} -> Entry
end.


And this is also slow. What can I do to dirty_select a limited number of records from a mnesia table?

Thank you,
r.
Reply | Threaded
Open this post in threaded view
|

Re: dirty_select a limited number of records

Aleksander Nycz-2

Hello,


Please check:

https://github.com/nyczol/otp/tree/mnesia_new_index2

for dirty_index_read/4 with limit in mnesia module.


Branch was created in 2013 but unfortunately PR was rejected by OTP.


Branch contains 2 features:

- new index type for mnesia

- set of functions that operate on secondary indexes and return limited number of records


Used on prod envs from 2013 untli now.


Maybe its good time to create PR again for 2nd part only?


Regards

Aleksander Nycz



W dniu 27.11.2019 o 10:03, Roberto Ostinelli pisze:
Here's another try, this one too is very slow:

QH = qlc:q([E || E <- mnesia:table(my_table), E#my_table.pid == Pid]),
mnesia:async_dirty(fun() ->
    QC = qlc:cursor(QH),
    R = qlc:next_answers(QC, N),
    qlc:delete_cursor(QC),
    R
end).

Any ideas?



On Tue, Nov 26, 2019 at 6:33 PM Roberto Ostinelli <[hidden email]> wrote:
All,
I've got a mnesia table that I create with:

mnesia:create_table(my_table, [
    {type, set},
    {attributes, record_info(fields, my_table)},
    {index, [#my_table.pid]},
    {storage_properties, [{ets, [{read_concurrency, true}, {write_concurrency, true}]}]}
]).


If I want to get all records for the secondary index, I do:

mnesia:dirty_index_read(my_table, Pid, #my_table.pid).

This operation if very fast. Now, sometimes I just need the first matching record and if there are many entries with the secondary index, the operation becomes a bottleneck. So I'm trying:

S = fun() ->
    MatchHead = #my_table{pid = Pid, _ = '_'},
    Guards = [],
    Result = '$_',
    mnesia:select(my_table, [{MatchHead, Guards, [Result]}], 1, read)
end,
case mnesia:activity(sync_dirty, S) of
    {[Entry], _} -> Entry;
    _ -> undefined
end.


This works, however this operation does not seem to be using the secondary index as it becomes extremely slow. I also tried:

S = fun() ->
    MatchHead = #my_table{pid = '$1', _ = '_'},
    Guard = {'=:=', '$1', Pid},
    Result = '$_',
    mnesia:select(my_table, [{MatchHead, [Guard], [Result]}], 1, read)
end,
case mnesia:activity(sync_dirty, S) of
    '$end_of_table' -> undefined;
    {[Entry], _} -> Entry
end.


And this is also slow. What can I do to dirty_select a limited number of records from a mnesia table?

Thank you,
r.

smime.p7s (6K) Download Attachment