<span class="kw">library</span>(RODBCext)
connHandle <-<span class="st"> </span><span class="kw">odbcConnect</span>(<span class="st">'EWD'</span>) <span class="co"># my sample ODBC database</span>
pupils =<span class="st"> </span><span class="kw">sqlQuery</span>(
connHandle, <span class="st">"SELECT id_obserwacji FROM obserwacje LIMIT 10000"</span>,
<span class="dt">stringsAsFactors =</span> F
)[, <span class="dv">1</span>]
<span class="co"># Ordinary query - paste0() called in every loop</span>
<span class="kw">system.time</span>({
for(i in pupils){
query <-<span class="st"> </span><span class="kw">paste0</span>(
<span class="st">"SELECT count(*) </span>
<span class="st"> FROM testy_obserwacje JOIN testy USING (id_testu) JOIN arkusze USING (arkusz) </span>
<span class="st"> WHERE id_obserwacji = "</span>, pupils[i]
)
tmp <-<span class="st"> </span><span class="kw">sqlQuery</span>(connHandle, query)
<span class="co"># some other computations here</span>
}
})
<span class="co"># user system elapsed </span>
<span class="co"># 10.896 1.508 61.424 </span>
<span class="co"># Ordinary query - paste0() called only once</span>
<span class="kw">system.time</span>({
queries <-<span class="st"> </span><span class="kw">paste0</span>(
<span class="st">"SELECT count(*) </span>
<span class="st"> FROM testy_obserwacje JOIN testy USING (id_testu) JOIN arkusze USING (arkusz) </span>
<span class="st"> WHERE id_obserwacji = "</span>, pupils
)
for(query in queries){
tmp <-<span class="st"> </span><span class="kw">sqlQuery</span>(connHandle, query)
<span class="co"># some other computations here</span>
}
})
<span class="co"># user system elapsed </span>
<span class="co"># 11.016 1.108 51.766 </span>
<span class="co"># Parameterized query</span>
<span class="kw">system.time</span>({
query =<span class="st"> "</span>
<span class="st"> SELECT count(*) </span>
<span class="st"> FROM testy_obserwacje JOIN testy USING (id_testu) JOIN arkusze USING (arkusz) </span>
<span class="st"> WHERE id_obserwacji = ?"</span>
<span class="kw">sqlPrepare</span>(connHandle, query)
for(i in pupils){
tmp =<span class="st"> </span><span class="kw">sqlExecute</span>(connHandle, <span class="ot">NULL</span>, pupils[i], <span class="dt">fetch=</span>T)
<span class="co"># some other computations here</span>
}
})
<span class="co"># user system elapsed </span>
<span class="co"># 12.140 0.312 26.468</span>