1 Reply Latest reply: Jun 24, 2013 4:19 PM by KnightOfBlueArmor RSS

    JDBC XMLType Insert/Update Performance

    KnightOfBlueArmor

      I am writing a backend Java EE application that inserts or updates XML documents into a table backed by XMLType.  I'm using Oracle 11.2.0.3 with associated drivers and XDK, with connections obtained from an oracle.jdbc.poo.OracleDataSource.  Unfortunately, I can't get my application to perform faster than 7 transactions a second, with or without JDBC batching.  I've tried several different table structures; here are the two that have shown the most promise:

       

      CREATE TABLE mrbook_raw
      (
        id  RAW(16),
        created_date TIMESTAMP,
        last_modified_ts TIMESTAMP,
        bk_typ_cd VARCHAR2(16),
        bk_invt XMLType,
        PRIMARY KEY(id),
        FOREIGN KEY (id) REFERENCES mrbook(id)
      ) XMLTYPE COLUMN bk_invt ELEMENT "http://www.mrbook.com/BookData/Vers1#BK_INVT";
      
      --Also tried the below...
      
      CREATE TABLE book_master OF XMLTYPE
      XMLTYPE STORE AS SECUREFILE BINARY XML 
      VIRTUAL COLUMNS
      (
        isbn_nbr AS ( XmlCast(
                      XmlQuery('declare namespace plh="http://www.mrbook.com/BookData/Vers1/Header";
                                declare namespace bk_invt="www.mrbook.com/BookData/Vers1/InventoryData";
                                /bk_invt:BK_INVT/plh:HDR/plh:ISBN_NBR' 
                                PASSING object_value RETURNING CONTENT) AS VARCHAR2(64)) ),
        book_id AS ( XmlCast(
                      XmlQuery('declare namespace plh="http://www.mrbook.com/BookData/Vers1/Header";
                                declare namespace invtdata="http://www.mrbook.com/BookData/Vers1/InventoryData";
                                /bk_invt:BK_INVT/plh:HDR/plh:BOOK_ID' 
                                PASSING object_value RETURNING CONTENT) AS VARCHAR2(64)) )
      );
      

      Here is the code that inserts into the first table:

       

          private static final String INS_MRBOOK_RAW = 
                  "INSERT INTO MRBOOK_RAW " +
                          "(id, bk_invt, last_modified_ts, created_date) " +
                  "VALUES (?, ?, ?, ?)";
          
          private static final String UPD_MRBOOK_RAW = 
                  "UPDATE MRBOOK_RAW " +
                  "SET " +
                      "bk_invt = ?, " +
                      "last_modified_ts = ? " +
                  "WHERE id = ?";
                  
      protected void updateBookRaw(BookRecord record, Connection con)
          {
              PreparedStatement stmt = null;
              SQLXML sqlxml = null;
              Timestamp now = new Timestamp(System.currentTimeMillis());
              
              try
              {
                  stmt = con.prepareStatement(UPD_MRBOOK_RAW);
                  
                  sqlxml = con.createSQLXML();
                  
                  DOMResult result = sqlxml.setResult(DOMResult.class);
                  
                  result.setNode(record.getExistingInvtData());
                  
                  stmt.setSQLXML(1, sqlxml);
                  stmt.setTimestamp(2, now);
                  stmt.setBytes(3, record.getId());
                  
                  stmt.executeUpdate();
              }
              catch(SQLException e)
              {
                  throw new RuntimeException(e);
              }
              finally
              {
                  if(sqlxml != null)
                  {
                      try
                      {
                          sqlxml.free();
                      }
                      catch(SQLException e)
                      {
                          log.error("Unable to free SQLXML!", e);
                      }
                  }
                  
                  if(stmt != null)
                  {
                      try
                      {
                          stmt.close();
                      }
                      catch(SQLException e)
                      {
                          log.error("Unable to close statement!", e);
                      }
                  }
              }
          }
      
          protected void insertBookRaw(BookRecord record, Connection con)
          {
              PreparedStatement stmt = null;
              SQLXML sqlxml = null;
              Timestamp now = new Timestamp(System.currentTimeMillis());
              
              XMLDocument bookDoc = parser.getInvtDataDoc(record.getNewBook());
              
              try
              {
                  stmt = con.prepareStatement(INS_MRBOOK_RAW);
                  
                  sqlxml = con.createSQLXML();
                  
                  DOMResult domResult = sqlxml.setResult(DOMResult.class);
                  
                  domResult.setNode(bookDoc);
                  
                  stmt.setBytes(1, record.getId());
                  stmt.setSQLXML(2, sqlxml);
                  
                  stmt.setTimestamp(3, now);
                  stmt.setTimestamp(4, now);
                  
                  stmt.executeUpdate();
              }
              catch(SQLException e)
              {
                  throw new RuntimeException(e);
              }
              finally
              {
                  if(sqlxml != null)
                  {
                      try
                      {
                          sqlxml.free();
                      }
                      catch(SQLException e)
                      {
                          log.error("Unable to free SQLXML object!", e);
                      }
                  }
                  
                  if(stmt != null)
                  {
                      try
                      {
                          stmt.close();
                      }
                      catch(SQLException e)
                      {
                          log.error("Unable to close statement!", e);
                      }
                  }
              }
          }
      

      I've tried every storage method possible; CLOBs, Binary XMLType, and structured storage, but I just cannot get the application to go faster than 7 records/second.

       

      I understand that this may or may not be an XMLType question, but I don't know where to start.  From everything above, it looks like I should be getting good performance inserting and updating XMLType records; and I do indeed get pretty good performance from retrieval, but not from insert or update.  Does anyone have any suggestions on what I might try or a reference I might look at to start?