Using Prefetch with Oracle and VisualWorks
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