Perhaps Oracle XE 18.3 support · Issue #25 · cbandy/travis-oracle · GitHub can help you if you would like do some quick automated test in a CI like Travis. Currently I am using 11g for my driver tests I mentioned above but sometime in future I will also like to upgrade (or have in parallel) to18 etc.
c-bik
dimitarvp
I am more interested in a home lab that can also host an Oracle XE 18c container or a VM. A CI/CD-oriented solution is for the moment not a priority since I am not working with clients that require Oracle. It’s more of a data integration and cross-DB transfer hobby pursuit.
0
c-bik
A final update before I move on from performance tests. Added C (ODPI-C) to comparison and results are quite intersting!
JAMDBO INSERT 22186 rows in 28.821038 seconds (769.7849050405471 rows/sec)
JAMDBO SELECT 22185 rows in 28.516178 seconds (777.9794332887108 rows/sec)
ORANIF INSERT 14000000 rows in 68.944727 seconds (203061.21452914012 rows/sec)
ORANIF SELECT 13946410 rows in 114.605359 seconds (121690.73175714235 rows/sec)
ODPI-C INSERT 13950000 rows in 6.853266 seconds (2035525.835419 rows/sec)
ODPI-C SELECT 13946375 rows in 13.352952 seconds (1044441.334021 rows/sec)
native ODPI-C is ~10 times faster than oranif
oranif is ~260 times faster than jamdb_oracle.
I will however come back to oranif/jamdb comparison once I get some help form jamdb authors to fix/improve my jamdb part of the testing
3
c-bik
@Cruz Thanks for the mysql tutorial links made quite a good progress following them with DBConnection implementation for Oracle. I don’t seem to understand the following though:
Following MyXSQL-III design it seems like MyXQL.prepare_execute prepares, executes and also fetches all rows (which is three different API calls in Oracle - prepareStmt, execute and fetch).
iex> {:ok, pid} = MyXQL.start_link([])
iex> MyXQL.prepare_execute(pid, "SELECT ?", [42], [])
{:ok, %MyXQL.Query{statement: "SELECT ? + ?", statement_id: 1},
%MyXQL.Result{columns: ["? + ?"], rows: [[5]]}}
The fetch from Oracle is sort of buffered as on every call it returns a few rows and a boolean indicating if EOT. Is that some how differently handled from ecto? This get-everything prepare_execute can’t be it, right?
TIA
0
Cruz
Yes, that function suppose to work the way you’ve described:
but, as you can see on the same page, there are other functions (and ways) to retrieve data. Perhaps, what you’re wondering is how the current drivers implement that particular function. Maybe someone else with more experienced on this can comment.
1
wojtekmach
Hex Core Team
Yup, that’s exactly what happens under the hood. Worth mentioning that an Ecto adapter is a pretty high level abstraction so it abstracts out these steps. Otherwise the Repo, which uses an Ecto adapter, would have to fetch until EOF, and that step might vary between data stores.
3
when you’re ready, feel free to create a dedicated thread for it in our
c-bik
@AstonJ Thanks for splitting the thread. I hope, eventually to come to address the original topic : “billion of recods from Oracle” but for now I am far from it. About the thread name should it rather be “Ecto Oracle Adapter”?
@wojtekmach and @Cruz thanks a lot for the inputs they lead me to prepare_stream/4.
Here is my understding about how prepare_execute should work (pseudo code) with oranif/Oracle-DPIC:
def prepare_execute(conn, query, params, opts) do
stmt = dpiConn_prepareStmt(query)
dpiStmt_execute(stmt)
rows = []
do
{rowsFetched, isEOT} = dpiStmt_fetch(stmt)
rows = rowsFetched ++ rows
while !isEOT
{:ok, query, rows}
end
To have that effect I need to implement the DBConnection callbacks handle_prepare/3 and handle_execute/4 correctly.
This API will prepare-execute-fetch all rows from table (if SQL is like so) so perhaps app developers are adviced not to call this for large tables.
The large number of rows are handled through the DBConnection stream interface APIs - related callbacks are handle_declare (instead of handle_execute/4 as next call after handle_prepare/3), handle_fetch/4 etc.
Is this understanding correct so far?
TIA
2
wojtekmach
Hex Core Team