Some updates on oracle ecto adapter dev activity (using oranif)

I made some progress in implementing a basic DBConnect interface for ecto and managed to connect to an test oracle DB succesfully. It seems I can follow jamdb_oracle ecto adapter design for an oranif ecto adapter. It is, of course, not as simple as just replacing all :jamdb_oracle*.* calls with oranif but I am making progress slowly (while also learning elixir).

In the mean time, out of curiosity and in parallel, I thought I would just try to compare some basic performance between jamdb_oracle and oranif so wrote ora_bench. A priliminary result shows that:

JAMDB inserted 22186 rows in 29.338234 seconds # ~750 rows / second
JAMDB selected 22185 rows in 29.619194 seconds # ~750 rows / second

ORANIF inserted 14000000 rows in 67.489112 seconds  # ~210000 rows / second
ORANIF selected 13946410 rows in 115.466077 seconds # ~121000 rows / second

In each of the tests, I tried to insert into a varchar2(4000) column 10 decimal digits of an incremental counter till DB limits are reached.

In case of oranif I could successfully get ORA-01653: unable to extend table SCOTT.TEST by 1024 in tablespace SYSTEM out of DB so my test stopped due to system limits (as I hoped for).

However, for jamdb I got ORA-01000: maximum open cursors exceeded around 22K rows insert/select (what I wasn’t expecting)! Furthermore, to make the jamdb test better I wanted to use COMOFF; (auto commit off) but that made cursor overflow happen even much earlier (at barely around ~10K rows).

@Cruz when you used jamdb (with ecto) did you ran into any cursor overflow? Do you know what is the right way to close cursors with jamdb so I can avoid this overflow. All I am doing in my test is :jamdb_oracle.sql_query(ConnRef, "select ITEM from test"). Googling didn’t help to know what I might be missing!

@kodepett did you ever got around trying jamdb with your 18m rows use case? How did that perform for you?

This config will sort of show the basic setup I need to run these test.

I would like bring jabdb test to its best possible performance so the comparison with oranif happens on fair ground. I look forword to any advices to improve this test.

TIA for your comments.

Here is the first working version (very much alpha) of the driver GitHub - KonnexionsGmbH/OraLixir: Oracle driver for Elixir · GitHub

This is still work-in-progress. Not all DBConnection callbacls are (correctly)implemented.
But connect and query is possible.

Some examples

iex> {:ok, pid} = OraLixir.start_link([])
{:ok, #PID<0.69.0>}
iex> OraLixir.prepare_execute(pid, "name", "SELECT 'string', 1, sysdate FROM DUAL", [], [])
{:ok, %OraLixir.Query{}, %OraLixir.Result{}}

I will very much appreciate any comment / verification / feedback / code review and general guidence etc for me to improve on this.

TIA

Streaming must happen inside a transaction, see: DBConnection.transaction/3. The fun passed to transaction/3 gets the t().