Today's Smalltalk Daily looks at using Oracle's pre-fetch capability 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:

"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 recrods being inserted."
loopCount := 1000.

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

"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 prefetch rows is 1."
sess setPrefetchRows: 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 prefetch rows  is 1: ', selectTime1 asFloat printString.

"Set prefetch rows to 100."
sess setPrefetchRows: 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 prefetch rows  is 100: ', selectTime2 asFloat printString.

"Set prefetch rows to 500."
sess setPrefetchRows: 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 prefetch rows is 500: ', selectTime3 asFloat printString.

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

Technorati Tags: visualworks, database, oracle, prefetch