Today's Smalltalk Daily looks at using Array Binding against 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 Workspace examples will show the performance improvements when using array 
binding and array fetching in VisualWorks."

"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 TESTTABLE';
	execute;
	answer;
	answer.

"Create a test table."
sess prepare:  'CREATE TABLE TESTTABLE(
	NUMMER int ,
	BEMERKUNG varchar2 (30)
)';
	execute;
	answer;
	answer.

"Set the number of records being inserted."
loopCount := 1000.

"The SQL used to do inerst."
sql := 'INSERT INTO TESTTABLE VALUES (?, ?)'.

"Insert: not using array binding."
sess prepare: sql.
insertTime1 := Time millisecondsToRun: [
	1 to: loopCount do: [ :i|
         sess bindInput: (Array with: i with: 'test');
		execute;
		answer;
		answer.
	].			
].

"Print out the miliseconds spent."
Transcript cr; show: 'Time spent without using array binding: ', insertTime1 asFloat printString.

"Insert: Using array binding."
insertTime2 := Time millisecondsToRun: [
|bindArray numArray stringArray |
    numArray := Array new: loopCount.
    stringArray := Array new: loopCount.
	1 to: loopCount do: [ :i|
		numArray at: i put: i.
		stringArray at: i put: 'test'.
	].			
	bindArray := Array with: numArray with: stringArray.
	sess prepare: sql.
         sess bindInput: bindArray;
		execute;
		answer;
		answer.
].

"Print out the miliseconds spent."
Transcript cr; show: 'Time spent using array binding: ', insertTime2 asFloat printString.

"Set times to repeat."
loopCount := 1.

"Set the SQL to do the fetch."
sql := 'SELECT * from TESTTABLE'.

"Default value of blockFactor is 1."
sess blockFactor: 1.

selectTime1 := Time millisecondsToRun: [
	1 to: loopCount do: [ :i|
		sess prepare: sql;
		execute.
		ans := sess answer.
		res := ans upToEnd.
    	].
].

"Print out the miliseconds spent."
Transcript cr; show: 'Time spent when blcokFactor is 1: ', selectTime1 asFloat printString.

"Set blockFactor to 100."
sess blockFactor: 100.

selectTime2 := Time millisecondsToRun: [
	1 to: loopCount do: [ :i|
		sess prepare: sql;
		execute.
		ans := sess answer.
		res := ans upToEnd.
    	].
].

"Print out the miliseconds spent."
Transcript cr; show: 'Time spent when blcokFactor is 100: ', selectTime2 asFloat printString.

"Set blockFactor to 500."
sess blockFactor: 500.

selectTime3 := Time millisecondsToRun: [
	1 to: loopCount do: [ :i|
		sess prepare: sql;
		execute.
		ans := sess answer.
		res := ans upToEnd.
    	].
].

"Print out the miliseconds spent."
Transcript cr; show: 'Time spent when blcokFactor is 500: ', selectTime3 asFloat printString.

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, oracle, database, array binding