Today's Smalltalk Daily looks at using Oracle's pre-fetch capabilities with ObjectStudio - note that this is an upcoming feature of ObjectStudio 8.2.1. 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:

"PrefetchRows example. Feature is available in OS8.2.1"

"Prefetch rows is similar to array fatching, but it happens in Oracle client. If 
OCI_ATTR_PREFETCH_ROWS is set then this value multiplied by the row size of 
memory is allocated.  When a call to fetch is made all the memory allocated 
is used to store records from the server. The first call gets back n rows and 
subsequent calls to fetch do not make a network call until all n records have 
been returned from the client."

"The following Workspace examples will show the performance 
improvements when using prefetchRows 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 := 2000.

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

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

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

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

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

"Default value of prefetchRows is 1."
db prefetchRows: 1.

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

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

"Set prefetchRows to be 100."	
db prefetchRows: 100.
selectTime2 := [
1 to: loopCount do: [ :i|
    db execSql: sql.
	]
] millisecondsToRun.

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

"Set prefetchRows to be 1000."	
db prefetchRows: 1000.
selectTime3 := [
1 to: loopCount do: [ :i|
    db execSql: sql.
	]
] millisecondsToRun.

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

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