Improve read performance

[jamblejoe]

Is your feature request related to a problem? Please describe.
We are a slightly disappointed by the read performance from DB2 databases via this python package. We use ibm_db_dbi and pandas read_sql to read in data. This calls into

python-ibmdb/ibm_db_dbi.py

Line 1764 in c3aaf02

def _fetch_helper(self, fetch_size=-1):

where the result is processed in a python loop and every fetched tuple is appended to a list. This is suboptimal performance wise.

There is a faster solution since the end of last year due to this PR #971, fetchall implemented in

python-ibmdb/ibm_db.c

Line 17042 in c3aaf02

static PyObject *ibm_db_fetchall(PyObject *self, PyObject *args)

Here, the loop is implemented in C but there are a lot of python-checks for every fetched tuple. The performance is 4x faster than the ibm_db_dbi interface.

Other packages are providing a much better performance. E.g., the rust package https://github.com/pacman82/arrow-odbc wrapped in https://github.com/pacman82/arrow-odbc-py calls the IBM driver via the ODBC interface and bulk reads in rust into the arrow format. The python wrapper of the rust crate has a very easy syntax:

reader = arrow_odbc.read_arrow_batches_from_odbc(
        query=f"""
                SELECT col1, col2, ...
                FROM TEST_PULL """,
        connection_string=connection_string,
        batch_size=10_000,
    )

batches = []
for batch in reader:
    batches.append(batch)

df = pl.from_arrow(batches) # polars df

A little test gave us the following performance chart (lower is better)

Note the logarithmic scale of the axis.

Summarized: arrow-odbc-py gives as ~15x performance boost over ibm_db_dbi interface and ~4x boost over fetchall from ibm_db.

Describe the solution you'd like
We would like to continue to use ibm_db_dbi, but the performance sacrifices compared to other packages are too large. Please enhance the performance of reading from DB2.

Describe alternatives you've considered
Other packages, like arrow-odbc-py, allow for significantly faster reads.

[jamblejoe]

Maybe related #406

[bchoudhary6415]

Hello @jamblejoe
Thank you for pointing this out.

From your description, I understand that the performance of the fetchone, fetchmany, and fetchall methods from the ibm_db_dbi module is slow because these methods internally call the _fetch_helper(self, fetch_size=-1) function. The fetched tuples are then processed in a Python loop, and each tuple is appended to a list, which impacts performance.

You are suggesting using the direct ibm_db fetch API support (as introduced in PR #971) instead of _fetch_helper(self, fetch_size=-1), which should improve performance, correct?

Let me know if I understood that correctly.

Thank you!

[jamblejoe]

Hello @bchoudhary6415
Thank you for reaching out! Let me clarify and separate my statements more carefully:

1. If the fetchall method of the ibm_db_dbi module would call into the fetchall method of the ibm_db module, this should result in an increase of performance. In the figure I provided in my initial post, this corresponds to improving from the blue line to the yellow line (~4x performance increase).

2. Possible by other packages (e.g. arrow-odbc) is a fetching speed like the green line in the figure. This is even ~4x times faster than the fetchall method of the ibm_db module. The speed difference is probably coming from pure rust code and pre-allocation in arrow-odbc as opposed to "pythonic C-code" and list-appends in the ibm_db implementation of fetchall.

I suggest two actions:

1. Let the fetchall method of the ibm_db_dbi module call into the fetchall method of the ibm_db module. This should be quite straight forward to implement.
2. Improve the performance of the fetchall method in the ibm_db module. I cannot exactly tell you how to do it. But a start would be to fetch all data in pure C with pre-allocated buffers and then return to python after fetching all data.

Addtional notes:

• The underlying driver for ALL 3 test cases is the same (DB2 Linux 64Bit libdb2.so).

[bchoudhary6415]

Hello @jamblejoe

Thank you for the suggestions!

Regarding the first action, we will implement the change to ensure that the fetchall method of the ibm_db_dbi module calls the fetchall method of the ibm_db module.

For the second action, I would like to clarify the implementation details of the ibm_db module. Both fetchall() and fetchmany() internally call the fetch() method in a loop. The fetch() method, in turn, invokes the SQLFetch() ODBC API to retrieve a single row of data from the Db2 server. Consequently, since the performance bottleneck is likely tied to the behavior of fetch() and its interaction with the ODBC API, there isn't much we can do to optimize it further at the fetch() level within the ibm_db module.

We understand the desire for improved performance, but optimizing fetch() itself would require fundamental changes to the underlying ODBC interaction, which is beyond the scope of the current implementation.

Let us know if you have further questions or suggestions!

[jamblejoe]

Hi @bchoudhary6415 !
Thank you for getting back to me.

Regarding the first action, we will implement the change to ensure that the fetchall method of the ibm_db_dbi module calls the fetchall method of the ibm_db module.

That is good news for us and the many other users who fetch data from DB2 with Python.

We understand the desire for improved performance, but optimizing fetch() itself would require fundamental changes to the underlying ODBC interaction, which is beyond the scope of the current implementation.

We understand that optimizing fetchall to utilize buffers instead of calling SQLFetch for single rows implies a lot of additional work; and that this is beyond the current capacities and, apparently, the need of the community.

For anyone stumbling on this issue in the future, there are good alternatives:

• https://github.com/pacman82/arrow-odbc/tree/41f3e6841e14072db0459591f610f9ebe4a77ee7, which has a Python wrapper for this Rust crate
• https://github.com/mkleehammer/pyodbc (?) Python bindings for C++ implementation

[bchoudhary6415]

Hello @jamblejoe
PR for this issue #1008 is merged now.

Regarding performance:

Bulk fetching using ibm_db.fetchall() or fetchmany() is generally efficient and recommended for scenarios
where the result set comfortably fits in memory. These functions simplify retrieval and reduce application-side
looping overhead. However, there are a few considerations:

Memory Usage: Since fetchall() loads all rows into memory at once,
it may not be suitable for extremely large result sets, especially in low-memory environments. For massive datasets,
row-by-row processing (fetch_assoc() or fetch_tuple()) may offer better control and memory efficiency.

Lack of Flexibility: With fetchall(), you lose the ability to process rows incrementally or stop early,
which can be useful in streaming or paginated scenarios.

Performance Tuning: When using bulk fetch functions like fetchall() or fetchmany(),
performance can be further improved by increasing the driver's fetch buffer size.

This can be done using:
ibm_db.set_option(conn, {ibm_db.SQL_ATTR_FET_BUF_SIZE: 128000}, 1)

Setting a larger buffer size (e.g., 128 KB) reduces the number of round-trips between the client and server
during data retrieval, which can significantly improve performance for large result sets.

Use fetchall() or fetchmany() when:
The full result set is reasonably sized
Simplicity and readability are preferred

For very large or streaming datasets, consider row-by-row fetch methods for finer control and better memory handling.
For more info refer the link - https://www.ibm.com/docs/en/db2/12.1.0?topic=keywords-fetchbuffersize

Please refer below example:

Using ibm_db.fetch_assoc() API:

sql = "select * from testfetchtable"
# Set a larger fetch buffer size (e.g., 64 KB)
success = ibm_db.set_option(conn, {ibm_db.SQL_ATTR_FET_BUF_SIZE: 65536}, 1)
if success:
    print("Fetch buffer size set successfully.")

stmt = ibm_db.prepare(conn, sql)
# Execute the statement
ibm_db.execute(stmt)

# Fetch all results
rows = []
row = ibm_db.fetch_assoc(stmt)
while row:
    rows.append(row)
    row = ibm_db.fetch_assoc(stmt)

print(f"Fetched {len(rows)} rows.")
print(rows)

Using ibm_db.fetchall() API

# Set a larger fetch buffer size (e.g., 64 KB)
success = ibm_db.set_option(conn, {ibm_db.SQL_ATTR_FET_BUF_SIZE: 65536}, 1)
if success:
    print("Fetch buffer size set successfully.")
	
stmt = ibm_db.prepare(conn, sql)
ibm_db.execute(stmt)

allRows = ibm_db.fetchall(stmt)
print(f"Fetched {len(allRows)} rows.")
print(allRows)

Closing this issue now.
Thank you

[jamblejoe]

@bchoudhary6415 Thank you for your great work! When can we expect a new release with these features? Thank you!

[bchoudhary6415]

@jamblejoe Thank you for your kind words!

Currently, we don't have an immediate plan for a new release. However, we're aiming to close a few more open issues and complete some internal enhancements first.

We'll discuss the timeline with the team and keep you updated once we have a clearer picture.
Thanks!