Re: Oracle ODBC support for fast_executemany

Please ignore the question. Today, I got some time to test Oracle ODBC 19.9 driver and it supports fast_executemany flag:

mgogala_at_umajor:~/work/python$ time ./load_csv.py -u scott/tiger_at_Ora-19 -t test_tab1 -i /tmp/test_tab.csv
5000000 rows loaded

real    13m13.095s
user    1m3.680s
sys    0m39.647s

mgogala_at_umajor:~/work/python$ time ./load_csv.py -u scott/tiger_at_Ora-19 -t test_tab1 -i /tmp/test_tab.csv -m
5000000 rows loaded

real    0m32.056s
user    0m8.776s
sys    0m0.330s

The flag is controlled by the "-m" argument. Without the flag, loading 5 million rows takes more than 13 minutes. With the flag, it's 32 seconds.

The driver is the instant client 19.9 ODBC driver:

[Ora19c]
Description=Oracle ODBC driver for Oracle 19 Driver=/usr/lib/instantclient_19_9/libsqora.so.19.1 Setup=
FileUsage=
CPTimeout=
CPReuse=

That did not work with the instant client 19.8. I'm happy as a clam, I don't have to write separate script for each database. This is actually big news. Now, if only MySQL ODBC driver did the same....

On 11/19/20 12:53 AM, Mladen Gogala wrote:
> Oracle Corp. has generously provided cx_Oracle Python package for
> Oracle and an Oracle ODBC driver, free of charge. There is a very new
> and and a very interesting feature called "fast_executemany" which is
> known to us Oraclites for the last 2 decades as "array interface". The
> description of that very new feature is here:
>
> https://github.com/mkleehammer/pyodbc/wiki/Features-beyond-the-DB-API
>
> Essentially, the whole array is inserted at once, thereby saving many
> trips over the network. Sounds familiar? Well, there is a little
> problem: Oracle ODBC driver doesn't support that feature, at least not
> the one from the instant client 19.8. As this feature is very new,
> very few drivers support it. Here are some drivers that were tested
> with "fast_executemany":
>
> https://github.com/mkleehammer/pyodbc/wiki/Driver-support-for-fast_executemany
>
>
> The description saying that it "crashes Python" sounds particularly
> amusing, but it isn't. I tried with FreeTDS and got an extremely quick
> core dump. Here is a question for the friends from Oracle Corp. on
> this list: Is Oracle Corp. going to support fast_executemany in the
> ODBC driver provided with the instant client? When can that support be
> expected, if the answer to the previous question is a positive one? I
> really don't want to write a separate script to insert into an Oracle
> database. The MS ODBC no. 17 works. The other drivers usually result
> in the use of the good, old no. 7.
>
>

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217

--
http://www.freelists.org/webpage/oracle-l