Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Help - performance of writing BLOBs from JDBC

Help - performance of writing BLOBs from JDBC

From: The Nomad <nobody_at_nowhere.com>
Date: Thu, 01 Nov 2001 22:18:24 GMT
Message-ID: <QSjE7.1920$4Q3.605637@typhoon.tampabay.rr.com> 





Hey all,



I use stored procedures for all my updates except of course for updating a
BLOB column. For updating a blob column, I have to use standard JDBC code
(to avoid the 32k limitation in Stored Procedures). Anyway, storing blobs in
the table is extremely slow. MS SQL Server 2000 can insert/update blobs (and
all the other columns) at a rate of 3 seconds/100. For Oracle 8.1.7 on the
same machine, it runs at a rate of 26 seconds/100.



I'd like to get the update/insert rates at least in the same ball park if
possible. Here is the code:




  private  static final String insSql = "begin INSERT INTO
ENTRYATTR(TREEENTRYID, ATTRCATEGORY, ATTRID, ATTRBINARYVALUE)

VALUES(?,?,?,empty_blob())  return ATTRBINARYVALUE into ?; end;";



  private static final String updSql = "begin UPDATE ENTRYATTR SET
ATTRBINARYVALUE = empty_blob() WHERE TREEENTRYID = ? AND ATTRCATEGORY = ?
AND ATTRID = ? RETURN ATTRBINARYVALUE INTO ?; end;";



  private static final String CNTSQL = "{call ENTRYATTREXIST(?,?,?,?)}";




  public int save(Connection con) throws SQLException, RepositoryException {
    int rtn = -1;


    int isThere = 0;


    Connection con2;


    try {

      CallableStatement iitSt = con.prepareCall(CNTSQL);
      try {
        con2 = iitSt.getConnection();
        iitSt.setInt(1, (int)getTreeId());
        iitSt.setInt(2, (int)getAttrCategory());
        iitSt.setInt(3, (int)getAttrId());
        iitSt.registerOutParameter(4, java.sql.Types.INTEGER);
        iitSt.executeUpdate();
        isThere = iitSt.getInt(4);
        if ( iitSt.wasNull() ) {
          isThere = 0;


        }

      } finally {
        iitSt.close();
      }
      // The connection object in Orion doesn't pass through the autoCommit
setting.
      // so, I have to get the OracleConnection from the statement above.
      con = con2;
      boolean autoComm = con.getAutoCommit();
      con.setAutoCommit(false);
      try {
        if (isThere == 0) {
          CallableStatement stmt = con.prepareCall(insSql);
          try {
            stmt.setInt(1, (int)getTreeId());
            stmt.setInt(2, (int)getAttrCategory());
            stmt.setInt(3, (int)getAttrId());
            stmt.registerOutParameter(4, OracleTypes.BLOB);
            stmt.executeUpdate();
            BLOB blob = (BLOB) ((OracleCallableStatement)stmt).getBLOB(4);
            InputStream is = new ByteArrayInputStream(getBinaryValue());
            OutputStream os = blob.getBinaryOutputStream();
            byte[] buf = new byte[blob.getChunkSize()];
            int length = 0;
            while ((length = is.read(buf)) != -1) {
              os.write(buf, 0, length);
            }
            os.flush();
            os.close();
            rtn = 0;
            isDirty = false;
          } finally {
            stmt.close();
          }


        } else {

          CallableStatement stmt = con.prepareCall(updSql);
          try {
            stmt.setInt(1, (int)getTreeId());
            stmt.setInt(2, (int)getAttrCategory());
            stmt.setInt(3, (int)getAttrId());
            stmt.registerOutParameter(4, OracleTypes.BLOB);
            stmt.executeUpdate();
            BLOB blob = (BLOB) ((OracleCallableStatement)stmt).getBLOB(4);
            InputStream is = new ByteArrayInputStream(getBinaryValue());
            OutputStream os = blob.getBinaryOutputStream();
            byte[] buf = new byte[blob.getChunkSize()];
            int length = 0;
            while ((length = is.read(buf)) != -1) {
              os.write(buf, 0, length);
            }
            os.flush();
            os.close();
            rtn = 0;
            isDirty = false;
          } finally {
            stmt.close();
          }


        }

        if (rtn == 0) {
          // Explicitly commit work.
          // con.createStatement().execute("commit");
          con.commit();


        }

      } finally {
        con.setAutoCommit(autoComm);
      }
    } catch (IOException ex) {
      ex.printStackTrace();
      throw new RepositoryIOException(ex.toString());


    }


    return rtn;


  }




So, first things first:



    
  
  1.   Is this the quickest way to update/insert the blob into the table. I've
tried other methods including trying update first, and if that fails, do the
insert - and vice-versa. Those actually ran longer.

  
  2.   Is there some kind of setting that should be set in the server that may
improve this?






Machine Configuration Details




1.4 GHZ Pentium with 512mb ram


Windows 2000


10gb free disk space on the logical drive containing Oracle
Orion Application Server 1.5.2



Sample Load Times




Below, I've listed the load times for generating sample content. For each
user, 10 items of content is added, each item of content will have at least
2 blobs. All times are in milliseconds. Notice that the MSSQL load times
stay fairly even throughout the process, but the Oracle load times escalate
from around 17 seconds/100 to around 27 seconds/100. After the loading test,
there will be around 10,000 items of content created. In the table in
question, there will be around 199,000 rows - of these rows, around 101,350
will be blobs. The maximum length of blobs is around 45k with the average
running only 28 bytes. The total size of all the blobs after loading is
around 2.9mb.



When I stub out the blob adding (so no blobs are added), Oracle load times
are around 5 sec/100. So, it is definately the blobs slowing things down.




Load Times - MSSQL




Users: 10


time/10:3785


Users: 20


time/10:3986


Users: 30


time/10:4056


Users: 40


time/10:3735


Users: 50


time/10:3996


Users: 60


time/10:3785


Users: 70


time/10:4276


Users: 80


time/10:3946


Users: 90


time/10:3695


Users: 100


time/10:3766



...snip...



Users: 800


time/10:3645


Users: 810


time/10:3645


Users: 820


time/10:3676


Users: 830


time/10:3635


Users: 840


time/10:4056


Users: 850


time/10:3635


Users: 860


time/10:3635


Users: 870


time/10:3625


Users: 880


time/10:3625




Load Times - Oracle




Users: 10


time/10:13349


Users: 20


time/10:14892


Users: 30


time/10:15202


Users: 40


time/10:15222


Users: 50


time/10:15242


Users: 60


time/10:15452


Users: 70


time/10:15452


Users: 80


time/10:15863


Users: 90


time/10:16423


Users: 100


time/10:16254



...snip...



Users: 800


time/10:25627


Users: 810


time/10:26037


Users: 820


time/10:25837


Users: 830


time/10:25958


Users: 840


time/10:26087


Users: 850


time/10:26689


Users: 860


time/10:26407


Users: 870


time/10:26769


Users: 880


time/10:26588




Here is the table definition






CREATE TABLE ENTRYATTR



(TREEENTRYID INTEGER NOT NULL,



 ATTRCATEGORY INTEGER NOT NULL,



 ATTRID INTEGER NOT NULL,



 ATTRSTRINGVALUE VARCHAR(850) NULL,



 ATTRINTVALUE INTEGER NULL,



 ATTRFLOATVALUE NUMBER NULL,



 ATTRDATEVALUE DATE NULL,



 ATTRBINARYVALUE BLOB NULL,



 CONSTRAINT PK_ENTATTR_TEATTRID PRIMARY KEY(TREEENTRYID, ATTRCATEGORY,

ATTRID),



 CONSTRAINT FK_ENTRYATTR_ATTRLIST


     FOREIGN KEY(ATTRCATEGORY, ATTRID)
     REFERENCES ATTRLIST(ATTRCATEGORY, ATTRID) ON DELETE CASCADE,
 CONSTRAINT FK_ENTRYATTR_BATREE
     FOREIGN KEY(TREEENTRYID)
     REFERENCES BATREE(TREEENTRYID) ON DELETE CASCADE


)


STORAGE ( INITIAL 100m MAXEXTENTS UNLIMITED PCTINCREASE 10)
/





Does anybody have any suggestions?
Received on Thu Nov 01 2001 - 16:18:24 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US