Row versus Set Processing Experiement.

31-May-2012 by Craig Shallahamer, craig@orapub.com

Set processing was performed using Oracle SQL and the row processing was performed using Oracle’s plsql. The experimental details and analysis is posted on Craig Shalahamer’s (craig@orapub.com) blog (A Wider View) in May of 2012.

The data below is summarized from the raw experimental data. Each of the below 10 row and set processing samples is the average from 12 actual samples. You can see the samples are grouped; (rows processed, elapsed time in seconds).

rowProcessing={{100000,244.827484},{200000,488.268893},{300000,740.67795},{400000,1002.74595},{500000,1250.8051},{600000,1507.10145},{700000,1789.35509},{800000,2026.92326},{900000,2339.14553},{1000000,2548.92394}}
setProcessing={{100000,.065524417},{200000,.131115083},{300000,.197437},{400000,.231783},{500000,.30142925},{600000,.360746583},{700000,.428058917},{800000,.489497917},{900000,.56059475},{1000000,.628519167}}

ListPlot [{rowProcessing,setProcessing},PlotLabel->"Set Processing vs Row Processing\nbased on number of rows processed",AxesLabel->{"Rows Processed","Time (s)"},AxesOrigin->{0,0},PlotMarkers->Automatic]
ListPlot [rowProcessing,PlotLabel->"Row Processing\nTime vs Rows Processed",AxesLabel->{"Rows Processed","Time (s)"},AxesOrigin->{0,0},PlotMarkers->Automatic,Filling->Axis]
ListPlot [setProcessing,PlotLabel->"Set Processing\nTime vs Rows Processed",AxesLabel->{"Rows Processed","Time (s)"},AxesOrigin->{0,0},PlotMarkers->Automatic,Filling->Axis]

rLm=LinearModelFit[rowProcessing,x,x]
Show[ListPlot[rowProcessing,PlotLabel->"Row Processing\nTime vs Rows Processed",AxesLabel->{"Rows Processed","Time (s)"},AxesOrigin->{0,0},PlotMarkers->Automatic],Plot[rLm[x],{x,0,1000000}]]

sLm=LinearModelFit[setProcessing,x,x]
Show[ListPlot[setProcessing,PlotLabel->"Set Processing\nTime vs Rows Processed",AxesLabel->{"Rows Processed","Time (s)"},AxesOrigin->{0,0},PlotMarkers->Automatic],Plot[sLm[x],{x,0,1000000}]]

setThroughput={{100000,1526475.76},{200000,1525424.15},{300000,1519560.23},{400000,1725765.05},{500000,1658774.21},{600000,1663226.12},{700000,1635291.37},{800000,1634334.23},{900000,1605444.99},{1000000,1591043.33}}
rowThroughput={{100000,408.461317},{200000,409.615077},{300000,405.039052},{400000,398.911805},{500000,399.751556},{600000,398.117411},{700000,391.215288},{800000,394.696477},{900000,384.778072},{1000000,392.338925}}
ListPlot [{rowThroughput,setThroughput},PlotLabel->"Throughput\nSet Processing vs Row Processing",AxesLabel->{"Rows Processed","Throughput (rows/s)"},AxesOrigin->{0,0},PlotMarkers->Automatic]