I might be severely off the mark here but I believe they were referring to something like FastGlobal. It produces a compiled module for you that would contain code lines like these:

def get("column1-key1"), do: "value1"
def get("column2-key1"), do: "value1"
def get("column1-key2"), do: "value2"

(This is the code generated by the library. You don’t write that. You do FastGlobal.put(:key, "value") and that’s it.)

…Which is the fastest ever access you can get with Erlang / Elixir. However, every changing of value has a heavy runtime cost (make sure to go through the README file). So only use FastGlobal for very rare writes and a ton of reads.

As Ben said, 15K records is nothing. Caching every value several times (on as many keys as you need) is a perfectly fine strategy at that small scale.

OK. I’m not even close to finish the little system I’m building, but I thought I could share some thoughts already.

I did some very rudimentary bench marking with a vanilla-type table ( 1 key - 1 value) per entry, configure each solution to perform 10M reads, and measure the throughput. I didn’t measure the load time (and the writes) because as I said before even several minutes is acceptable in my case. All these were done against only one process.

The ETS based solution clocked at: 1,079,098 reads/sec

I thought that was quite impressive until I saw the FastGlobal based results: 128,205,128 reads/sec

That’s crazy fast. And, the simple macro-based solution suggested by Kabie was even faster: 212,765,957 reads/sec

So, you’re absolutely right. If speed is the main factor, macros are the best choice.

However, having to recompile and redeploy every time a value changes in the data is something that will carry a cost. I’ll keep these solutions in my back pocket, and suggest them as an option, but for the moment I’ll continue with ETS. I also played with a larger table and saw the performance decreasing, but I’m sure is still going to be more than enough for my app.

One last thought, at the beginning of this task (and thread), I dismissed CacheX because I have to maintain two indexes in the main table. However, all the solutions considered (except Mnesia) require me to handle the additional index directly. So, I reconsidered CacheX, and I now think it is the right solution for most scenarios. I’m in a unique situation because my data changes between 3 and 6 times a year.

Thanks again to all of you for your help. You guys rock,

Cruz

Yes, that’s precisely my use case. Thank you so much. You’ve been very patient and helpful

Yes, it’s super simple if you don’t need to add things at runtime.

defmodule Data do
  # load external data
  @data [
    %{id: 1, other_key: :a},
    %{id: 2, other_key: :b},
    %{id: 3, other_key: :c},
  ]


  for key <- [:id, :other_key] do
    for row <- @data do
      def unquote(:"by_#{key}")(unquote(row[key])) do
        unquote(Macro.escape(row))
      end
    end

    def unquote(:"by_#{key}")(_) do
      nil
    end
  end

end

Can you make this a bit more concrete? What does match mean? equality? pattern matching? are there multiple keys for the same value?