Smalltalk with James Robertson

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 just 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.

You can download the video directly here. If you like this kind of video, why not subscribe to "Smalltalk Daily"?

Technorati Tags: video, objectstudio, oracle, lob, database