search

oracle / python-cx_Oracle

Python interface to Oracle Database now superseded by python-oracledb
https://oracle.github.io/python-cx_Oracle
Other
890 stars 361 forks source link

Setting defaultLobPrefetchSize in cx_oracle #577

Closed anilshanbhag closed 3 years ago

anilshanbhag commented 3 years ago
  1. What versions are you using?

python 3.7.9 cx_Oracle.version: 6.2.1 cx_Oracle.clientversion: (19, 3, 0, 0, 0)

  1. Describe the problem

defaultLobPrefetchSize parameter can be used when connecting via JDBC to prefetch LOBs. However, this parameter is not accessible via cx_oracle.

In the table being used, we have 5 LOBs which are small in most cases. With defaultLobPrefetchSize set to 4000, the time taken by the query is 230ms. An equivalent query on MySQL takes 200ms. Querying it from cx_oracle, it takes 7s :/

Is there a way to set this parameter when accessing the database via cx_oracle? https://docs.oracle.com/cd/E11882_01/java.112/e16548/oralob.htm#JJDBC15000

  1. Include a runnable Python script that shows the problem.

-

cjbj commented 3 years ago

Since they are small, you will find it faster (and easier) to fetch them as strings or bytes, as shown in https://github.com/oracle/python-cx_Oracle/blob/main/samples/return_lobs_as_strings.py

anilshanbhag commented 3 years ago

Thanks @cjbj, closing the issue.