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

From: Prof Brian Ripley <ripley_at_stats.ox.ac.uk>
Date: Mon, 28 Apr 2008 20:28:28 +0100 (BST)

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

______________________________________________
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.
Received on Mon 28 Apr 2008 - 20:22:54 GMT

Archive maintained by Robert King, hosted by the discipline of statistics at the University of Newcastle, Australia.
Archive generated by hypermail 2.2.0, at Tue 29 Apr 2008 - 01:30:33 GMT.

Mailing list information is available at https://stat.ethz.ch/mailman/listinfo/r-help. Please read the posting guide before posting to the list.

list of date sections of archive