Today's Smalltalk Daily looks at using LOB data in Oracle with ObjectStudio. If you're looking for a particular topic, you can find it with the Media Search application on our site.

Here's the script used in the screencast; or skip to the video:

"LOB buffer size example."

"When dealing with LOBs, setting the right size of buffers used to 
transfer data between the server and client is important.  For 
example, if  the LOBs are large, allocating a big buffer will 
reduce the network round trips when inserting or fetching 
LOB values."

"The following are examples to demonstrate performance 
improvement when setting the right size of LOB buffers."

"Logon to the Oracle Server."
ret := ObjectStudio.OracleDatabase 
	logOnServer: #OracleDB 
	user: #useid 
	password: #pwd 
	alias: #OracleDB.

"Get the Oracle database instance."
db := ObjectStudio.Database accessName: #OracleDB.

"Drop the test table if existed."
db execSql: 'DROP TABLE TestLob'.

"Create the test table."
res := db execSql: 'CREATE TABLE TestLob (a CLOB, b BLOB, c INT)'.

"Input 2MB CLOB and BLOB."
ClobLength := 2097152. 
BlobLength := 2097152. 
ClobInput := String new: ClobLength withAll: $a.
BlobInput := ByteArray new: ClobLength withAll: 1.

db beginTran.
"Insert the test data."
insertSQL := 'INSERT INTO TestLob (a, b, c) VALUES ( ?, ?, ?)'.

db lobBufferSize: 32768.  "32KB is the default buffer size for read/write Large Objects."

insertTime1 := [
   res := db execSql: insertSQL vars: (Array with: ClobInput  with: BlobInput with: 1).
] millisecondsToRun.

"Print out the miliseconds spent."
('Insert time when lobBufferSize is 32768: ' + insertTime1) out.	

"Set lobBufferSize to 1MB"
db lobBufferSize: 1048576.

insertTime2 := [
   res := db execSql: insertSQL vars: (Array with: ClobInput  with: BlobInput with: 2).
] millisecondsToRun.

"Print out the miliseconds spent."
('Insert time when lobBufferSize is 1048576: ' + insertTime2) out.	

db commit.

db beginTran.

selectSQL := 'select * from TestLob'.

db lobBufferSize: 32768.  "32KB is the default buffer size for read/write Large Objects."

selectTime1 := [
        db execSql: selectSQL answerLobAsProxy: false.

] millisecondsToRun.

"Print out the miliseconds spent."
('Select time when lobBufferSizeis 32768: ' + selectTime1) out.	


"Set lobBufferSize to 1MB"
db lobBufferSize: 1048576.

selectTime2 := [
        db execSql: selectSQL answerLobAsProxy: false.

] millisecondsToRun.

"Print out the miliseconds spent."
('Select time when lobBufferSizeis 1048576: ' + selectTime2) out.	

db rollback.

To watch, click on the viewer below:

If you have trouble viewing that directly, you can click here to download the video directly

You can also watch it on YouTube:

Technorati Tags: smalltalk, objectstudiooracle, LOB, database, objectstudio