Query with results of 800k data taking longer time compared to other tools

[vasumur]

@vstavskyi

Not sure if this is something that has been tested with this driver, but wanted to bring it to your attention.

I am running a query using jamdb driver and it is taking at times longer than 2 hours to bring the data to Elixir.

Where as running the same query in SQL*Plus client or in Java gets the data under 2-5 minutes. I understand this query

In Elixir side it brings the first set of data 100k records under 2 minutes and after that the runtimes are way too slow.

My config is as below.

config :karma, Karma.InsiteRepo, username: "****", password: "*****", hostname: "****", database: "****", port: 1522, timeout: :timer.minutes(120), pool_size: 3, queue_target: :timer.minutes(120), idle_interval: :timer.seconds(10), parameters: [ read_timeout: :timer.minutes(20), autocommit: 0, fetch: 1000, sdu: 65535 ] Appreciate if you could look into this.

[vasumur]

Based on further analysis I see that this is because we are using Enum to process the results instead processing them as stream/concurrent processes should make it faster.

Let me know your thoughts, so we can close this one. Thanks

[vstavskyi]

Try DBConnection.stream. Don't change default value of sdu (8192). If row size of resultset is ~1000 bytes, then set fetch to 8 ( 1000 * 8 < 8192). If ~100 bytes, then set fetch to 80 ( 100 * 80 < 8192).

DBConnection.transaction(conn, fn conn ->
      query = %Jamdb.Oracle.Query{statement: "select KEYWORD from V$RESERVED_WORDS"}
      query = DBConnection.prepare!(conn, query)
      try do
        stream = DBConnection.stream(conn, query, [])
        Enum.to_list(stream)
      after
        DBConnection.close(conn, query)
      end
    end)