Very slow writing BLOB to Oracle DB using Python cx_Oracle

Question

I have a very simple Python application (using cx_Oracle) that is writing a BLOB to an Oracle database, but it is painfully slow when writing large files. My application first downloads an image file from another system using a a SOAP API, and it downloads in a matter of a second or two. However, writing files to the DB of say 100k in size, takes a second or two, but a file of 1.5MB takes just over 1 minute.

My SQL is :-

INSERT INTO my_data (doc_id,doc_content) VALUES (:blobid, :blobdata), blobid=id_variable, blobdata=blob_variable

Is there a reason it's taking so long? Can I speed it up? It oughtn't take more than a few seconds I'd have thought?

(Disclaimer! I have never used cx_Oracle or indeed Oracle itself ever before, I am comparing performance to Postgres which I have used for this kind of thing, and whose performance is infinitely better.)

The full code block is here :-

def upload_docs(doc,blob,conn):
    sql="INSERT INTO my_data (doc_id,doc_content) VALUES (:dociddata, :blobdata)"
    cursor = conn.cursor()
    cursor.execute(sql, dociddata=doc, blobdata=blob)
    conn.commit()
    cursor.close()

conn = cx_Oracle.connect(user="Myuser", password="MyPassword",dsn="ocm.server.here.com:1527/some_name",encoding="UTF-8")
doc_csv = "/tmp/document_list.csv"
csv_file=open(doc_csv, 'r')

for line in csv_file:
splitLineArray = line.split(',')
documentId = splitLineArray[17]

#Pull document down from SOAP API
    documentData = (client.service.getDocument(int(documentId)))        
    upload_docs(documentId, documentData, conn)

This is most likely going to require tuning of the database infrastructure (storage, memory, etc.). You won't be able to do anything from within the application code. Get with your DBA to do a performance analysis and work out a solution. — pmdba
– pmdba, Commented Mar 8, 2022 at 16:43
The cx_Oracle documentation has a section on tuning, using CLOBs and BLOBs, and LOBs in general. You might find something helpful there. But yes, you will probably need to tune the DB side - among other things maybe look at whether the BLOB column is cached. Your DBA will be in a better position to advise for your specific circumstances. — Alex Poole
– Alex Poole, Commented Mar 8, 2022 at 16:52
Thanks. It seems odd because I can write a 500KB file in 1 second, yet a 1.5MB takes 1 minute. i.e. the increase in time is not linear, so I doubt it's a tuning thing. It's more likely there is a threshold over which Oracle handles the data differently? — Simon
– Simon, Commented Mar 8, 2022 at 16:58
Share the full code block, in particular the code that populates the values for id_variable and blob_variable. If you are using temporary LOBs you will notice SIGNIFICANT slowdowns. I ams suspecting that is the issue but need confirmation. — Anthony Tuininga
– Anthony Tuininga, Commented Mar 8, 2022 at 17:51
Hi, I've edited the original post to include all the relevant lines. — Simon
– Simon, Commented Mar 8, 2022 at 18:59

Simon · Accepted Answer · 2022-03-15 09:18:00Z

1

Although I haven't quite resolved this, I can now confirm the issue is not with cx_Oracle nor is it an issue with the database itself. I am (or rather, was) connecting to the DB through Oracle Connection Manager, which was causing the delays. When I connect direct to the DB I can write large blobs in around a second. so the issue is definitely with OCM.

answered Mar 15, 2022 at 9:18

Simon

1353 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Very slow writing BLOB to Oracle DB using Python cx_Oracle

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related