We have an application that transfers .bmp images as BLOBs to an Oracle table. One of the users of the app has complained that it is taking 2-3 seconds to transfer each image. Each image is ~1.2Mb. I've run some tests on our database and there is some lag, but more like 0.8 seconds. The user has an Oracle 9i R2 database. I've run tests on 11g, 10g and 9i and there doesn't seem to be any performance difference between them for me, so I'm guessing it's not because they're running an older database.
Here is the query that's being used:
UPDATE RESULTS_DATA SET IMAGE_BLOB=:myBlob WHERE MEASUREMENT_INDEX=:mIndex AND BATCH_ID=:batchId AND SYSTEM_ID=:sysId
There are indexes on SYSTEM_ID and BATCH_ID but not MEASUREMENT_INDEX. The problem doesn't appear to get worse when you have large amounts of data in the table.
Can anyone think of a reason that BLOB transfers would be so slow? Is there any way of optimising the table structure to make writing to BLOBs more efficient? Could it just be a case of a slow network? If so, how could I diagnose this?
Here's the table structure:
CREATE TABLE RESULTS_DATA ( SYSTEM_ID VARCHAR2(15), BATCH_ID VARCHAR2(15), USER_ID VARCHAR2(40), MEASUREMENT_INDEX NUMBER, IMAGE_BLOB BLOB, CONSTRAINT DATA_PKEY PRIMARY KEY (MEASUREMENT_INDEX,SYSTEM_ID,BATCH_ID)) TABLESPACE MEASUREMENT_DATA LOB (IMAGE_BLOB) STORE AS (TABLESPACE IMAGE_DATA); CREATE INDEX RESULTS_INDEX ON RESULTS_DATA(SYSTEM_ID,BATCH_ID) TABLESPACE MEASUREMENT_DATA_INDEX;
Here's the output of EXPLAIN PLAN from Oracle SQL Developer: