. .

smalltalk

Using Array Binding with Oracle and ObjectStudio 8: Video

April 20, 2010 9:55:07.782

Today's screencast looks at using Array binding 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.

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

Technorati Tags: , , , ,

posted by James Robertson

 Share Tweet This