[R] tips to speed up sqlSave with MS SQL Server?

Tue Apr 29 07:32:52 CEST 2008

Sorry, but Thomas needs a format something else can read, e.g. 'a flat 
file'.

write.table() is better than write(), and you may want to write in 
sections of, say, 1million rows.

The comparison you quote is for ca 7-14 secs to impoort 1m rows of 4 
integers.  6 secs is what write.table(DF, "foo.dat", row.names=FALSE) 
is doing for me, so looks like export can be faster than import.

On Mon, 28 Apr 2008, jim holtman wrote:

> ?save
>
> This will write the object to a file in the fastest manner.  Here is an example:
>
>> x <- runif(125000000/8)
>> object.size(x)
> [1] 125000024
>> system.time(save(x, file='/tempxx.Rdata'))
>   user  system elapsed
>  56.84    0.86   87.97
>>
>
> This wound up to be 79MB on disk after compression.
>
> Without compression, most of the time is my slow disk:
>
>> system.time(save(x, file='/tempxx.Rdata', compress=FALSE))
>   user  system elapsed
>   5.07    1.27   56.66
>
> The size on disk was 119MB.
>
> On Mon, Apr 28, 2008 at 8:51 PM, Thomas Pujol <thomas.pujol at yahoo.com> wrote:
>> Prof,
>> Thanks for your generous assistance.
>>
>> I'm unsure, but an thinking that to utilize one of MS SQL Server's bulk 
>> import utilities, I'll need to export my dataframe to a "flat-file".
>>
>> Any tips on the best approach for exporting such a large dataframe to a 
>> flat-file? Is write() or write.table() the "best" function to use, or 
>> are there others I should consider?
>>
>> Also, not specifc to R, but I came across this:
>> "Flat File Bulk Import methods speed comparison in SQL Server 2005"
>> http://weblogs.sqlteam.com/mladenp/archive/2006/07/17/10634.aspx
>>
>> Thanks again.
>>
>> Prof Brian Ripley <ripley at stats.ox.ac.uk> wrote:
>>
>>    I think the short answer is that RODBC is not designed for that, because
>>    ODBC is not. There seems to be an ODBC extension specific to SQL Server
>>    to do so (somewhere said 'SQL Server version 7.0 or later', which may not
>>    apply to you).
>>
>>    I'm pretty unlikely to add support for just one database, especially one
>>    that requires files from SQL Server. Also, I don't know of any RODBC /SQL
>>    Server users who might be motivated to do so.
>>
>>    There is work in progress to implement SQLBulkOperations, but that is a
>>    different concept (and not yet wired up to sqlSave).
>>
>>    On Mon, 28 Apr 2008, Thomas Pujol wrote:
>>
>>   > I am using R2.6.0 on ?Windows Small Business Server 2003?. I apologize
>>   > if the answer to my question is available?I have searched but have not
>>   > found anything that I thought helped me.
>>   >
>>   > I have a dataframe that contains ~4.5 million rows and 5 columns.
>>   > (see memory and df details below). I am trying to save the dataframe to
>>   > a MS SQL Server database, using the ?sqlSave? function. The code below
>>   > seems to work, but takes several hours.
>>   >
>>   > ?sqlSave(channel, dat=idxdata, varTypes=c(ddates="datetime") )?
>>   >
>>   > Any tips how I can speed things up? Or is my dataframe so large that it
>>   > is going to take a while? (I have ~20 dataframes that I need to save to
>>   > SQL, so speed is somewhat important.) Is there an altogether different
>>   > approach I should consider taking?
>>
>>    Use a different client that does implement bulk copy operations? At least
>>    SQL Server 2005 comes with a bcp.exe command-line client to do this. See
>>    http://msdn2.microsoft.com/en-us/library/ms188728.aspx
>>
>>
>>   > FYI, here is information re: the dataframe and memory on my system.
>>   > Please let me know if there is any further information I should provide.
>>   >
>>   >> memory.size(max = F) #reports amount of memory currently in use
>>   > [1] 131.8365
>>   >
>>   >> str(idxdata)
>>   > 'data.frame': 4474553 obs. of 5 variables:
>>   > $ idkey : int 1003 1003 1003 1003 1003 1003 1003 1003 1003 1003 ...
>>   > $ nnd : Factor w/ 25 levels "01","01C","02",..: 1 1 1 1 1 1 1 1 1 1 ...
>>   > $ curcdd : Factor w/ 2 levels "CAD","USD": 2 2 2 2 2 2 2 2 2 2 ...
>>   > $ ddates:Class 'Date' num [1:4474553] 6942 6943 6944 6945 6948 ...
>>   > $ idx : num 100 100 100 100 100 100 100 100 100 100 ...
>>   >
>>   >> object.size(idxdata)
>>   > [1] 125289688
>>
>>    --
>>    Brian D. Ripley, ripley at stats.ox.ac.uk
>>    Professor of Applied Statistics, http://www.stats.ox.ac.uk/~ripley/
>>    University of Oxford, Tel: +44 1865 272861 (self)
>>    1 South Parks Road, +44 1865 272866 (PA)
>>    Oxford OX1 3TG, UK Fax: +44 1865 272595
>>
>>
>> ---------------------------------
>> [[elided Yahoo spam]]
>>        [[alternative HTML version deleted]]
>>
>> ______________________________________________
>> R-help at r-project.org mailing list
>> https://stat.ethz.ch/mailman/listinfo/r-help
>> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
>> and provide commented, minimal, self-contained, reproducible code.
>>
>
>
>
> -- 
> Jim Holtman
> Cincinnati, OH
> +1 513 646 9390
>
> What is the problem you are trying to solve?
>
> ______________________________________________
> R-help at r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>

-- 
Brian D. Ripley,                  ripley at stats.ox.ac.uk
Professor of Applied Statistics,  http://www.stats.ox.ac.uk/~ripley/
University of Oxford,             Tel:  +44 1865 272861 (self)
1 South Parks Road,                     +44 1865 272866 (PA)
Oxford OX1 3TG, UK                Fax:  +44 1865 272595