Oracle has array interface well over two decades. The purpose of the array interface is to reduce the number of network trips needed for performing batch operations like fetching multiple row at once or inserting multiple rows at once. ODBC interface, however, is another story. ODBC interface is meant to be portable and database agnostic. In other words, ODBC operates with the smallest common denominator of the available features in various programmatic interfaces for relational databases.
Being an Oracle DBA, I use ODBC primarily to load data from an Oracle database into another type of database, like MySQL, PostgreSQL or SQL Server. However, some scripts are just performing general functions, like loading a CSV file into a database, so it’s nice to have one script to rule them all. These days, I mostly script in Python, due to the market demand, so Pyodbc library will be used to demonstrate the script.
This article is about the “fast_executemany” property of a Pyodbc connection. The script I used is called load_csv.py and it looks like this:
#!/usr/bin/env python3
""" This script loads the content of a CSV file into a table
This script uses ODBC connection and can be used to load table in
any database that supports ODBC. The connection syntax is Oracle
like, with the ODBC DSN instead of an Oracle TNS descriptor.
Author: Mladen Gogala
Version: 1.0
Date : 11/08/2020
"""
import argparse
import csv
import sys
import re
import pyodbc
parser = argparse.ArgumentParser(description='LOAD_CSV load a CSV into table')
parser.add_argument("-u", "--username", dest="username", required=True,
help='ODBC connection using <usr>/<pwd>@<DSN syntax')
parser.add_argument("-t", "--table", dest="table", required=True,
help='Table to be loaded')
parser.add_argument("-d", "--delimiter", dest="delimiter", default=',',
help="CSV delimiter character. The default is ','")
parser.add_argument("--noquote", dest="noquote", action="store_true",
help="Fields in CSV file are not quoted")
parser.add_argument("-F", "--fast-execmany", dest="fast_exec", action="store_true",
help="Use fast_executemany flag")
parser.add_argument("-i", dest="file", required=True,
help="File to load into the database")
parser.add_argument("-n", "--arraysize", dest="nrows", default=1024, type=int,
help="Batch size for bulk insert")
args = parser.parse_args()
(uid, pwd, dsn) = (None, None, None)
# Rows are read from the CSV file and inserted into the "batch_ins" list.
# The resulting list is then inserted into the database using "executemany"
# Pyodbc function. However, Pyodbc cheats and internally runs a loop which sends
# the rows to the database one by one instead of sending the rows in batches,
# unless the driver supports "fast_executemany" flag.
batch_ins = []
# ODBC has some strange philosophy when it comes to counting rows, it's better
# to do it programmatically.
rows = 0
# The insert statement needs to be constructed for the table. The table name is
# a script argument. The number of the bind variables is determined by counting
# the fields in the CSV file.
insert_str = f"insert into {args.table} values(?"
str_complete = False
parse_usr = re.search(r"(\w+)/(.*)@(.*)", args.username)
if parse_usr:
uid = parse_usr.group(1)
pwd = parse_usr.group(2)
dsn = parse_usr.group(3)
if (uid is None) or (pwd is None) or (dsn is None):
print("Incorrect connect string syntax. <username>/<password>@DSN expected")
print("DSN is the name of the ODBC connection")
sys.exit(-1)
try:
conn = pyodbc.connect(f"DSN={dsn};UID={uid};PWD={pwd}")
conn.autocommit = False
cursor = conn.cursor()
# If the driver supports "fast_executemany" flag, use it. So far, only the original
# Microsoft ODBC SQL Server drivers, both on Windows and Linux support that flag.
if args.fast_exec:
cursor.fast_executemany = True
with open(args.file, "r") as f:
if args.noquote:
reader = csv.reader(f, delimiter=args.delimiter,
quoting=csv.QUOTE_NONE)
else:
reader = csv.reader(f, delimiter=args.delimiter,
quoting=csv.QUOTE_ALL)
for row in reader:
if not str_complete:
for i in range(1, len(row)):
insert_str += ",?"
insert_str += ")"
str_complete = True
batch_ins.append(row)
rows += 1
if not len(batch_ins) % args.nrows:
cursor.executemany(insert_str, batch_ins)
conn.commit()
batch_ins = []
if len(batch_ins) > 0:
cursor.executemany(insert_str, batch_ins)
conn.commit()
except pyodbc.Error as odbc_exc:
sqlstate = odbc_exc.args[0]
print("Error: SQLSTATE =", sqlstate)
sys.exit(255)
except OSError as oserr:
os_err = oserr.args
print("Cannot open file:", args.file)
print(f"Error:{os_err}")
print(rows, "rows loaded")
The “fast_executemany” flag activates the array interface that saves a lot of time and network trips. The cursor.executemany() function internally performs a loop and sends rows one by one, unless “fast_executemany” flag is specified. If the flag is specified and if the drivers supports it, the database will perform the operation (insert or update) on the entire array in a single operation. Without that flag, the array size, specified by the “-n” argument doesn’t have too much impact on the performance:
mgogala@umajor:~/work/python$ time ./load_csv.py -u scott/tiger@Ora-19 -t test_tab -i /tmp/test_tab.csv
5000000 rows loaded
real 18m30.113s
user 1m7.887s
sys 0m49.030s
mgogala@umajor:~/work/python$ time ./load_csv.py -u scott/tiger@Ora-19 -t test_tab -i /tmp/test_tab.csv -n 16384
5000000 rows loaded
real 16m22.102s
user 1m7.751s
sys 0m45.439s
Loading 5,000,000 rows into a table takes 18.5 minutes with the default 1024 rows batch and 16 minutes 22 seconds with 16,384 rows batch. Nothing to write home about. MyODBC DSN is Ora-19 and the driver is Instant Client 19.9 ODBC driver. Interestingly, this wasn’t working with the Instant Client 19.8 driver. My /etc/odbcinst.ini looks like this:
[Ora19c]
Description=Oracle ODBC driver for Oracle 19
Driver=/usr/lib/instantclient_19_9/libsqora.so.19.1
Setup=
FileUsage=
CPTimeout=
CPReuse=
Now, let’s truncate the table and repeat the test with “fast_executemany”, controlled by the “-F” argument:
gogala@umajor:~/work/python$ time ./load_csv.py -u scott/tiger@Ora-19 -t test_tab -i /tmp/test_tab.csv -F
5000000 rows loaded
real 1m1.061s
user 0m10.475s
sys 0m0.523s
mgogala@umajor:~/work/python$ time ./load_csv.py -u scott/tiger@Ora-19 -t test_tab -i /tmp/test_tab.csv -F -n 16384
5000000 rows loaded
real 0m49.057s
user 0m11.165s
sys 0m6.441s
mgogala@umajor:~/work/python
Now, that is much better.Please note that increasing the array size from 1024 rows to 16384 rows has increased performance for about 15%. Performance would be better still if I haven’t kept Golden Gate on, but I wanted to see how fast will GG replicate the load. Golden Gate did that practically in real time:
2020-11-21T15:29:04.201-0500 INFO OGG-25222 Oracle GoldenGate Delivery for Oracle, rtgt1.prm: Large transaction completed. Total records: 16,384.
2020-11-21T15:29:04.404-0500 INFO OGG-25221 Oracle GoldenGate Delivery for Oracle, rtgt1.prm: Processing transaction (XID: 6.33.6307, Seqno: 54, RBA: 447591827) larger than eager size (15,100).
2020-11-21T15:29:04.405-0500 INFO OGG-25222 Oracle GoldenGate Delivery for Oracle, rtgt1.prm: Large transaction completed. Total records: 16,384.
2020-11-21T15:29:04.577-0500 INFO OGG-25221 Oracle GoldenGate Delivery for Oracle, rtgt1.prm: Processing transaction (XID: 4.2.6255, Seqno: 54, RBA: 449607101) larger than eager size (15,100).
2020-11-21T15:29:04.577-0500 INFO OGG-25222 Oracle GoldenGate Delivery for Oracle, rtgt1.prm: Large transaction completed. Total records: 16,384.
2020-11-21T15:29:11.240-0500 INFO OGG-25221 Oracle GoldenGate Delivery for Oracle, rtgt1.prm: Processing transaction (XID: 7.32.6253, Seqno: 54, RBA: 451622374) larger than eager size (15,100).
2020-11-21T15:29:12.157-0500 INFO OGG-25222 Oracle GoldenGate Delivery for Oracle, rtgt1.prm: Large transaction completed. Total records: 16,384.
2020-11-21T15:29:12.256-0500 INFO OGG-25221 Oracle GoldenGate Delivery for Oracle, rtgt1.prm: Processing transaction (XID: 10.29.6255, Seqno: 54, RBA: 453637648) larger than eager size (15,100).
2020-11-21T15:29:12.256-0500 INFO OGG-25222 Oracle GoldenGate Delivery for Oracle, rtgt1.prm: Large transaction completed. Total records: 16,384.
The GG log clearly shows 16K rows transactions being processed. However, GG is not the subject of this story, the “fast_executemany” flag is. Unfortunately, the “fast_executemany” is not compatible with all drivers. The wiki for Pyodbc fast_executemany flag support is here
https://github.com/mkleehammer/pyodbc/wiki/Driver-support-for-fast_executemany
Some drivers, like FreeTDS driver for SQL Server will even crash the Python interpreter. For the drivers that do work, the benefits are immense.
