Python-oracledb Thin mode Object performance

Photo by Nicolas Hoizey on Unsplash

Accessing Oracle objects and collections can take a few “round-trips” between the application and the database to collect metadata and do other marshalling. So it’s not surprising to me that fetching objects is slower than getting simple scalar types. To others, the performance difference is noticeable and becomes a topic of conversation. The most common object performance questions I see are from users of Oracle Spatial who are fetching the MDSYS.SDO_GEOMETRY type.

Anthony Tuininga and our driver team just added support for Oracle objects and collections to the Thin mode of python-oracledb 1.2. I wanted to compare this new implementation with the traditional Thick mode (that uses Oracle Client libraries).

Thanks to already having a dataset with an SDO_GEOMETRY column from a previous investigation, I was quickly able to write a simple script that did "select geometry from t where rownum <= :n". I added some timing and round-trip counting code, and took it for a run with my ‘local’ Oracle Database 23c Beta instance running in VirtualBox on my laptop. Representative results are:

$ python objperf.py 
Thin mode: rows=190000 arraysize=1000 roundtrips=199 seconds=2.65

$ DRIVER_TYPE=thick python objperf.py 
Thick mode: rows=190000 arraysize=1000 roundtrips=216 seconds=6.02

This is great for the Thin mode.

The reduction in round-trips was constant regardless of the number of rows I fetched. This simply reflected the initial metadata gathering differences of the two modes. After data fetches had started over the network there were benefits from internal driver processing. The roughly doubled performance of Thin mode seemed to hold regardless of the number of rows. It was also apparent when I put the dataset on a very remote machine:

$ python objperf.py 
Thin mode: rows=3000 arraysize=1000 roundtrips=12 seconds=10.15

$ DRIVER_TYPE=thick python objperf.py 
Thick mode: rows=3000 arraysize=1000 roundtrips=29 seconds=19.66

Overall we’re really happy to be able to bring you this performance boost with python-oracledb Thin mode.

In closing, some other general performance tips when working with objects include retaining any explicitly retrieved metadata from connection.gettype() since this will take a few round trips to get the object metadata. Perhaps you could extract the object attributes in a SQL statement and return them as scalar types so there are no object fetches? Or perhaps you can do all your processing in the database, e.g. in PL/SQL? In some cases with slow networks, and using objects with large numbers of attributes, you might be able to stringify the objects in the query and then re-constitute them in the application? If you don’t need to optimize to the Nth degree (excuse the spatial pun), then ignore all this and simply fetch objects directly.