<p><div><div><div><div data-show-faces="false" data-height="21" data-width="100" data-layout="button_count" data-send="true" data-href="https://www.r-bloggers.com/how-to-greatly-speed-up-your-spark-queries/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="http://www.win-vector.com/blog/2017/12/how-to-greatly-speed-up-your-spark-queries/"> R – Win-Vector Blog</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>For some time we have been teaching <a rel="nofollow" href="https://journal.r-project.org/" target="_blank"><code>R</code></a> users &quot;when working with wide tables on Spark or on databases: narrow to the columns you really want to work with early in your analysis.&quot;</p>
<p>The idea behind the advice is: working with fewer columns makes for quicker queries.</p>
<p><center><br>
<img border="0" src="https://i1.wp.com/www.win-vector.com/blog/wp-content/uploads/2017/12/NewImage-3.png?resize=599%2C403" alt="speed" width="450" title="NewImage.png" data-recalc-dims="1">
<p>
<a rel="nofollow" href="http://www.largeformatphotography.info/forum/showthread.php?31903-Jacques-Henri-Lartigue-and-his-camera" target="_blank">photo: Jacques Henri Lartigue 1912</a><br>
</center></p>
<p>The issue arises because wide tables (200 to 1000 columns) are quite common in big-data analytics projects. Often these are &quot;denormalized marts&quot; that are used to drive many different projects. For any one project only a small subset of the columns may be relevant in a calculation.</p>
<p><span></span></p>
<p>Some wonder is this really an issue or is it something one can ignore in the hope the downstream query optimizer fixes the problem. In this note we will show the effect is real.</p>
<p>Let’s set up our experiment. The data is a larger version of the problem from <a rel="nofollow" href="http://www.win-vector.com/blog/2017/08/lets-have-some-sympathy-for-the-part-time-r-user/" target="_blank">&quot;Let’s Have Some Sympathy For The Part-time R User&quot;</a>. We have expanded the number of subjects to 100000 and added 1000 irrelevant columns to the example. We define a new function that uses <code>dplyr</code> and <code>Sparklyr</code> to compute the diagnoses. We vary if the table is first limited to columns of interest and if the results are brought back to <code>R</code>.</p>
<div>
<pre><code>scale &lt;-<span> </span><span>0.237</span>


dplyr_run &lt;-<span> </span>function(narrow, <span>collect =</span> <span>FALSE</span>) {
  dR &lt;-<span> </span>dT
  if(narrow) {
    dR &lt;-<span> </span>dR %>%
<span> </span><span>select</span>(subjectID, surveyCategory, assessmentTotal)
  }
  dR &lt;-<span> </span>dR %>%
<span> </span><span>group_by</span>(subjectID) %>%
<span> </span><span>mutate</span>(<span>probability =</span>
             <span>exp</span>(assessmentTotal *<span> </span>scale)/
<span> </span><span>sum</span>(<span>exp</span>(assessmentTotal *<span> </span>scale))) %>%
<span> </span><span>arrange</span>(probability, surveyCategory) %>%
<span> </span><span>filter</span>(<span>row_number</span>() ==<span> </span><span>n</span>()) %>%
<span> </span><span>ungroup</span>() %>%
<span> </span><span>rename</span>(<span>diagnosis =</span> surveyCategory) %>%
<span> </span><span>select</span>(subjectID, diagnosis, probability) %>%
<span> </span><span>arrange</span>(subjectID)
  if(collect) {
    dR &lt;-<span> </span><span>collect</span>(dR)
  } else {
    dR &lt;-<span> </span><span>compute</span>(dR)
  }
  dR
}


<span>head</span>(<span>dplyr_run</span>(<span>narrow=</span><span>FALSE</span>))</code></pre>
</div>
<pre><code>## # Source: lazy query [?? x 3]
## # Database: spark_connection
## # Ordered by: probability, surveyCategory, subjectID
##   subjectID diagnosis           probability
##       </code></pre>