Help - performance of writing BLOBs from JDBC
Date: Thu, 01 Nov 2001 22:18:24 GMT
Message-ID: <QSjE7.1920$4Q3.605637_at_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:
- 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.
- 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 - 23:18:24 CET