<p><div><div><div><div data-width="100" data-show-faces="false" data-send="true" data-layout="button_count" data-height="21" data-href="https://www.r-bloggers.com/size-of-xdf-files-using-revoscaler-package/"></div></div><div><a data-url="https://www.r-bloggers.com/size-of-xdf-files-using-revoscaler-package/" data-counturl="https://www.r-bloggers.com/size-of-xdf-files-using-revoscaler-package/" data-count="horizontal" data-text="Size of XDF files using RevoScaleR package" class="twitter-share-button" data-via="rbloggers" href="https://twitter.com/share"></a></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://tomaztsql.wordpress.com/2016/09/22/size-of-xdf-files-using-revoscaler-package/"> R – TomazTsql</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>It came to my attention that size of XDF (external data frame) file can change drastically based on the compute context and environment. When testing the output of a dataset I was working on in SQL Server Management Studio I was simultaneously testing R code in <a target="_blank" href="https://www.visualstudio.com/en-us/features/rtvs-vs.aspx">RTVS</a> or  <a target="_blank" href="https://www.rstudio.com/">R Studio</a> and I have noticed a file growth.</p>
<p>Following stored procedure will do a simple test:</p>
<pre>CREATE PROCEDURE rxImport_Test ( @rowsPerRead INT )
AS
BEGIN
    DECLARE @RStat NVARCHAR(4000)
    SET @RStat = 'library(RevoScaleR)
                  #rxSetComputeContext(&quot;RxLocalSeq&quot;)
                  ptm &lt;- proc.time()
                  inFile &lt;- file.path(rxGetOption(&quot;sampleDataDir&quot;), &quot;AirlineDemoSmall.csv&quot;)
                  filename &lt;- &quot;AirlineDemoSmall_'+CAST(@rowsPerRead AS VARCHAR(100))+'_TSQL_NC.xdf&quot;
                  rxTextToXdf(inFile = inFile, outFile = filename,  stringsAsFactors = T, rowsPerRead = '+CAST(@rowsPerRead AS VARCHAR(100))+', overwrite=TRUE)
                  outFile &lt;- file.path(rxGetOption(&quot;sampleDataDir&quot;), filename)
                  rxImport(inData = inFile, outFile = outFile, overwrite=TRUE)
                  d &lt;- proc.time() - ptm
                  filesize &lt;- data.frame(file.size(filename))    
                  time     &lt;- data.frame(d[3])
                  RowsPerRead &lt;- data.frame('+CAST(@rowsPerRead AS VARCHAR(100))+')
                  filename_xdf &lt;- data.frame(filename)
                  ran &lt;- data.frame(Sys.time())
                  OutputDataSet &lt;- cbind(as.character(filesize), time, RowsPerRead, filename_xdf, ran)';
    EXECUTE sp_execute_external_script
          @language = N'R'
         ,@script = @RStat
    WITH RESULT SETS (( 
                         Filesize NVARCHAR(100)
                        ,Time_df NVARCHAR(100)
                        ,RowsPerRead NVARCHAR(100)
                        ,filename_xdf NVARCHAR(100)
                        ,DateExecute NVARCHAR(100)
                        ))
END</pre>
<p>But let’s first understand and test the Computation context and path to the data.</p>
<pre>-- Make sure your path location is pointing to RevoscaleR library folder!
EXECUTE sp_execute_external_script
      @language = N'R'
     ,@script = N'library(RevoScaleR) 
                OutputDataSet &lt;- data.frame(rxGetOption(&quot;sampleDataDir&quot;))'
WITH RESULT SETS (( 
                    path_folder NVARCHAR(1000)
                    ))

-- check for ComputeContext
DECLARE @RStat NVARCHAR(4000)
SET @RStat = 'library(RevoScaleR)
             cc &lt;- rxGetOption(&quot;computeContext&quot;)
             OutputDataSet &lt;- data.frame(cc@description)';
EXECUTE sp_execute_external_script
      @language = N'R'
     ,@script = @RStat
WITH RESULT SETS ((compute_context NVARCHAR(100)))</pre>
<p>At my computer, this looks like this:</p>
<p><img alt="2016-09-22-14_33_40-revoscale_r_file_conversion-sql-sicn-kastrun-sqlr-spar_si01017988-59-mic" class="wp-image-2395" src="https://i1.wp.com/tomaztsql.files.wordpress.com/2016/09/2016-09-22-14_33_40-revoscale_r_file_conversion-sql-sicn-kastrun-sqlr-spar_si01017988-59-mic.png?w=456&ssl=1" data-recalc-dims="1"></p>
<p>No we will run procedure</p>
<pre>rxImport_Test</pre>
<p>with different chunk sizes (this is what I will test) and observe execution times.</p>
<pre>INSERT INTO rxImport_results
EXEC rxImport_Test @rowsPerRead = 2;
GO

INSERT INTO rxImport_results
EXEC rxImport_Test @rowsPerRead = 20;
GO

INSERT INTO rxImport_results
EXEC rxImport_Test @rowsPerRead = 200;
GO

INSERT INTO rxImport_results
EXEC rxImport_Test @rowsPerRead = 2000;
GO</pre>
<p>Running with different chunk size the procedure, it yields interesting results:</p>
<p><img alt="2016-09-22 15_22_37-Revoscale_R_file_conversion.sql - SICN-KASTRUN.SQLR (SPAR_si01017988 (60))_ - Mi.png" class="wp-image-2404" src="https://i0.wp.com/tomaztsql.files.wordpress.com/2016/09/2016-09-22-15_22_37-revoscale_r_file_conversion-sql-sicn-kastrun-sqlr-spar_si01017988-60_-mi.png?w=456&ssl=1" data-recalc-dims="1"></p>
<p>Now, let’s see the summary information on this file / dataset.</p>
<p> </p>
<p><img alt="2016-09-22-15_35_58-book1-excel" class="wp-image-2408" src="https://i2.wp.com/tomaztsql.files.wordpress.com/2016/09/2016-09-22-15_35_58-book1-excel.png?w=456&ssl=1" data-recalc-dims="1"></p>
<p>Considering that original file holds 600.000 rows, number of blocks for each of the files is also changing and therefore the size of the files is growing.</p>
<p>Retrieving information on block size</p>
<pre>DECLARE @RStat NVARCHAR(4000)
SET @RStat = 'library(RevoScaleR)    
              info &lt;- rxGetInfoXdf(data=&quot;AirlineDemoSmall_20000000_TSQL_NC.xdf&quot;, getVarInfo = TRUE)    
              OutputDataSet &lt;- data.frame(info$numBlocks)';

EXECUTE sp_execute_external_script
      @language = N'R'
     ,@script = @RStat
WITH RESULT SETS (( 
                    nof_blocks NVARCHAR(100)))</pre>
<p>one can see the change between the files and where is the optimal block size. In my test, number of blocks would be 3 to 30 max to receive maximum performance from creating XDF file. This means from 2000 up to 200.000 rows per block would yield best performance results. Otherwise I haven’t found the the golden rule of the block size, but take caution, especially when dealing with larger files.</p>
<p>I ran test couple of times in order to check the consistency of the results, and they hold water. As for the file size; this is the presentation of internal file, as of *.xdf file (as external structure) size should not differ as the block size changes, but perfomance does!</p>
<p>Code is available at <a target="_blank" href="https://github.com/tomaztk/xdf_file_sizes">Github</a>.</p>
<p>Happy R-sqling!</p>
<p> <a target="_blank" rel="nofollow" href="http://feeds.wordpress.com/1.0/gocomments/tomaztsql.wordpress.com/2374/"><img alt="" src="http://feeds.wordpress.com/1.0/comments/tomaztsql.wordpress.com/2374/" border="0"></a> <img alt="" src="https://i1.wp.com/pixel.wp.com/b.gif?resize=1%2C1&ssl=1" border="0" data-recalc-dims="1"></p>
<p><div><div><div><div data-width="55" data-show-faces="false" data-send="true" data-layout="box_count" data-height="62" data-href="https://www.r-bloggers.com/size-of-xdf-files-using-revoscaler-package/"></div></div><div><a data-url="https://www.r-bloggers.com/size-of-xdf-files-using-revoscaler-package/" data-counturl="https://www.r-bloggers.com/size-of-xdf-files-using-revoscaler-package/" data-count="vertical" data-text="Size of XDF files using RevoScaleR package" class="twitter-share-button" data-via="rbloggers" href="https://twitter.com/share"></a></div><div></div></div><div></div></div>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="https://tomaztsql.wordpress.com/2016/09/22/size-of-xdf-files-using-revoscaler-package/"> R – TomazTsql</a></strong>.</div>
<hr>
<a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers.com</a> offers <strong><a rel="nofollow" href="https://feedburner.google.com/fb/a/mailverify?uri=RBloggers">daily e-mail updates</a></strong> about <a title="The R Project for Statistical Computing" rel="nofollow" href="https://www.r-project.org/">R</a> news and <a title="R tutorials" rel="nofollow" href="https://www.r-bloggers.com/?s=tutorial">tutorials</a> on topics such as: <a title="Data science" rel="nofollow" href="https://www.r-bloggers.com/?s=data%20science">Data science</a>, <a title="Big Data" rel="nofollow" href="https://www.r-bloggers.com/?s=Big%20Data">Big Data, <a title="R jobs" rel="nofollow" href="https://www.r-users.com/">R jobs</a>, visualization (<a title="ggplot and ggplot2 tutorials" rel="nofollow" href="https://www.r-bloggers.com/?s=ggplot2">ggplot2</a>, <a title="Boxplots using lattice and ggplot2 tutorials" rel="nofollow" href="https://www.r-bloggers.com/?s=boxplot">Boxplots</a>, <a title="Maps and gis" rel="nofollow" href="https://www.r-bloggers.com/?s=map">maps</a>, <a title="Animation in R" rel="nofollow" href="https://www.r-bloggers.com/?s=animation">animation</a>), programming (<a title="RStudio IDE for R" rel="nofollow" href="https://www.r-bloggers.com/?s=RStudio">RStudio</a>, <a title="Sweave and literate programming" rel="nofollow" href="https://www.r-bloggers.com/?s=sweave">Sweave</a>, <a title="LaTeX in R" rel="nofollow" href="https://www.r-bloggers.com/?s=LaTeX">LaTeX</a>, <a title="SQL and databases" rel="nofollow" href="https://www.r-bloggers.com/?s=SQL">SQL</a>, <a title="Eclipse IDE for R" rel="nofollow" href="https://www.r-bloggers.com/?s=eclipse">Eclipse</a>, <a title="git and github, Version Control System" rel="nofollow" href="https://www.r-bloggers.com/?s=git">git</a>, <a title="Large data in R using Hadoop" rel="nofollow" href="https://www.r-bloggers.com/?s=hadoop">hadoop</a>, <a title="Web Scraping of google, facebook, yahoo, twitter and more using R" rel="nofollow" href="https://www.r-bloggers.com/?s=Web+Scraping">Web Scraping</a>) statistics (<a title="Regressions and ANOVA analysis tutorials" rel="nofollow" href="https://www.r-bloggers.com/?s=regression">regression</a>, <a title="principal component analysis tutorial" rel="nofollow" href="https://www.r-bloggers.com/?s=PCA">PCA</a>, <a title="Time series" rel="nofollow" href="https://www.r-bloggers.com/?s=time+series">time series</a>, <a title="finance trading" rel="nofollow" href="https://www.r-bloggers.com/?s=trading">trading</a>) and more...
<img height="1" alt="" width="1" src="http://feeds.feedburner.com/~r/RBloggers/~4/3kbxpP2tPlQ"></a></div>