Today's Smalltalk Daily looks at using Array Binding for insertions and queries with Oracle and ObjectStudio 8. 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:

"Array binding and Array fetching example."

"Some databases allow client control over the number of rows that
 will be physically transferred between the server and the client in 
one logical bind or fetch. Using array binding and array 
fetching can greatly improve the performance of many applications 
by trading buffer space for time (network traffic)."


"The following Workspace examples will show the performance
 improvements when using array binding and array fetching in OS8."

"Logon to the Oracle Server."
ObjectStudio.OracleDatabase logOnServer: #'OracleDB' user: #'username' password: #'pwd' alias: #'OracleDB'.

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

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

"Create a test table."
db execSql: 'CREATE TABLE TESTTABLE(
	NUMMER int ,
	BEMERKUNG varchar2 (30)
)'.

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

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

"Insert: not using array binding."
insertTime1 := [

	1 to: loopCount do: [ :i|
         db execSql: sql vars: (Array with: i with: 'test').
	].			
] millisecondsToRun.

"Print out the miliseconds spent."
('Insert without using array binding: ' + insertTime1) out.

"Insert: Using array binding."
insertTime2 := [
|bindArray numArray stringArray |
    numArray := OrderedCollection new.
    stringArray := OrderedCollection new.
	1 to: loopCount do: [ :i|
		numArray add: i.
		stringArray add: 'bla'.
	].			
	bindArray := OrderedCollection with: numArray with: stringArray.
    sql := 'INSERT INTO TESTTABLE VALUES (?, ?)'.
    db execSql: sql vars: bindArray.
] millisecondsToRun.

"Print out the miliseconds spent."
('Insert using array binding: ' + insertTime2) out.

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

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

"Default value of arrayFetchSize is 1."
db setArrayFetchSizeTo: 1.

selectTime1 := [
1 to: loopCount do: [ :i|
        db execSql: sql.
    ].
] millisecondsToRun.

"Print out the miliseconds spent."
('Select with arrayFetchSize= 1: ' + selectTime1) out.	

"Set arrayFetchSize to be 100."	
db setArrayFetchSizeTo: 100.

selectTime2 := [
1 to: loopCount do: [ :i|
    db execSql: sql.
	]
] millisecondsToRun.

"Print out the miliseconds spent."
('Select with arrayFetchSize=100: ' + selectTime2) out.

"Set arrayFetchSize to be 500."	
db setArrayFetchSizeTo: 500.

selectTime3 := [
1 to: loopCount do: [ :i|
    db execSql: sql.
	]
] millisecondsToRun.

"Print out the miliseconds spent."
('Select with and arrayFetchSize=500: ' + selectTime3) out.

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