R help - Jan 2006 - importing a VERY LARGE database from Microsoft SQL into R

I am using R 2.1.1 in a Windows Xp environment.

I need to import a large database from Microsoft SQL
into R.

I am currently using the ?sqlQuery? function/command.

This works, but I sometimes run out of memory if my
database is too big, or it take quite a long time for
the data to import into R.

Is there a better way to bring a large SQL database
into R? 

IS there an efficient way to convert the data into R
format prior to bringing it into R? (E.g. directly
from Microsoft SQL?)

This question comes up a number of times what most people will tell you is
that even if you get all you data into R you won't be able to do anything
with it.  By that I mean, you need about 3 or 4 times as much memory as the
size of your data object because R will need to create copies of it.

I can tell you what I do in case it helps.  I also have a SQL Server
database and the good thing about having the data in that format is that you
probably don't need all of the data all of the time. First of all, a windows
machine can handle up to 4GB of RAM, but most software cannot use all of
it by default.  I have 4GB and I also use the windows binary, so that means
that whenever I download a new version of R, I have to modify the header
file to the it LARGEADDRESSAWARE.  Using this trick, I can load up my big
matrix into R to the point where task manager shows that R is using about
1.7GB of memory.  Despite such large objects, I am able to do regressions
and other things with the data, so I am quite happy.  If you need more
details just let me know.







On 1/24/06, r user <ruser2006@yahoo.com> wrote:
>
> I am using R 2.1.1 in a Windows Xp environment.
>
> I need to import a large database from Microsoft SQL
> into R.
>
> I am currently using the "sqlQuery" function/command.
>
> This works, but I sometimes run out of memory if my
> database is too big, or it take quite a long time for
> the data to import into R.
>
> Is there a better way to bring a large SQL database
> into R?
>
> IS there an efficient way to convert the data into R
> format prior to bringing it into R? (E.g. directly
> from Microsoft SQL?)
>
> ______________________________________________
> R-help@stat.math.ethz.ch mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide!
> http://www.R-project.org/posting-guide.html
>
	[[alternative HTML version deleted]]

On 1/24/06 3:28 PM, "r user" <ruser2006 at yahoo.com> wrote:
> I am using R 2.1.1 in a Windows Xp environment.
> 
> I need to import a large database from Microsoft SQL
> into R.
> 
> I am currently using the ??sqlQuery?? function/command.
> 
> This works, but I sometimes run out of memory if my
> database is too big, or it take quite a long time for
> the data to import into R.
> 
> Is there a better way to bring a large SQL database
> into R? 
How are you using sqlQuery?  The power of SQL is that you can fetch records
on demand.  If you don't need this functionality, then you may want to just
dump to a text file from Microsoft SQL and read into R.
> IS there an efficient way to convert the data into R
> format prior to bringing it into R? (E.g. directly
> from Microsoft SQL?)
You could probably check into how Microsoft SQL dumps a table.

Also, if you haven't read this:

http://cran.r-project.org/doc/manuals/R-data.html

it is probably a good time to look at it--it is full of tips for doing data
import from various sources including SQL databases.

Sean