Using Prefetch with Oracle and ObjectStudio: Video
Today's screencast 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.
You can download the video directly here. If you like this kind of video, why not subscribe to "Smalltalk Daily"?
Technorati Tags: database, oracle, objectstudio, video