This discussion is archived
1 Reply Latest reply: Jun 24, 2013 2:19 PM by KnightOfBlueArmor RSS

JDBC XMLType Insert/Update Performance

KnightOfBlueArmor Newbie
Currently Being Moderated

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?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points