Today's Smalltalk Daily looks at using LOBs with Oracle (version 9 and up) with VisualWorks. If you're looking for a particular topic, you can find it with the Media Search application on our site.

The code used is below; To watch, click on the viewer:

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

"Connect to an Oracle database."
conn := OracleConnection new.
conn username: 'username';
password: 'password';
environment: 'ORACLEDB'.
conn connect.
sess := conn getSession.

"Drop the test table if existed."
sess prepare: 'DROP TABLE TestLob';
	execute;
	answer;
	answer.

"Create a test table."
sess prepare: 'CREATE TABLE TestLob (A CLOB, B BLOB, C INTEGER)';
	execute;
	answer;
	answer.

conn begin.
insertSQL := 'INSERT INTO TestLob (a, b, c) VALUES (?, ?, ?)'.
sess prepare: insertSQL.
clobLength := 2097152. "2MB"
blobLength := 2097152. "2MB"
clob := String new: clobLength withAll: $a.
blob := ByteArray new: blobLength withAll: 1.

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

insertTime1 := Time millisecondsToRun: [
		sess bindInput: (Array with: clob with: blob with: 1);
		execute;
		answer;
		answer.
].

"Print out the miliseconds spent."
Transcript 
	cr; 
	show: 'Time spent for insert when lobBufferSize is 32KB ', insertTime1 asFloat printString.

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

insertTime2 := Time millisecondsToRun: [
		sess bindInput: (Array with: clob with: blob with: 2);
		execute;
		answer;
		answer.
].

"Print out the miliseconds spent."
Transcript 
	cr; 
	show: 'Time spent  for insert when lobBufferSize is 1MB: ', insertTime2 asFloat printString.

conn commit.

conn begin.
sess := conn getSession.
selectSQL := 'SELECT * FROM TestLob'.
sess answerLobAsValue. "Get LOBs back as values."
sess defaultDisplayLobSize: 2097152. "We want every byte of the LOBs returned."

sess lobBufferSize: 32768.  "32KB is the default buffer size for read/write Large Objects."
selectTime1 := Time millisecondsToRun: [ | ans1 |
		sess prepare: selectSQL;
		execute.
		ans1 := sess answer.
		ans1 upToEnd.
].

"Print out the miliseconds spent."
Transcript 
	cr; 
	show: 'Time spent for select when lobBufferSize is 32KB ', selectTime1 asFloat printString.

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

selectTime2 := Time millisecondsToRun: [ | ans2 | 
		sess prepare: selectSQL;
		execute.
		ans2 := sess answer.
		ans2 upToEnd.
].

"Print out the miliseconds spent."
Transcript 
	cr; 
	show: 'Time spent  for select when lobBufferSize is 1MB: ', selectTime2 asFloat printString.

conn rollback.

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, visualworks, LOB, oracle, database