Re: [R] RODBC sqlQuery insert slow

From: Bill Szkotnicki <bszk_at_uoguelph.ca>
Date: Fri 13 Oct 2006 - 15:49:50 GMT

Thanks for the help ... the sqlSave() function was the solution. The lesson, which has been stated many times before, is to avoid loops wherever possible!
Bill

# fast RODBC inserting
dat <- cbind(as.character(strptime(ti[,2],"%d/%m/%y %H:%M:%S %p")),ti[,3:12])
# you need the as.character to make sure the time is stored correctly in mysql
names(dat)=c("time","v1","v2","v3","v4","v5","v6","v7","v8","v9","v10") sqlSave(channel,dat,"logger",rownames=F,append=T) # very fast. #

Jerome Asselin wrote:

> On Fri, 2006-10-13 at 09:09 -0400, Bill Szkotnicki wrote:
>   

>> Hello,
>> I am trying to insert a lot of data into a table using windows R (2.3.1)
>> and a mysql database via RODBC.
>> First I read a file with read.csv and then form sql insert statements
>> for each row and execute the insert query one row at a time. See the
>> loop below.
>> This turns out to be very slow.
>> Can anyone please suggest a way to speed it up?
>>
>> Thanks, Bill
>>
>> # R code
>> ntry=dim(ti)[1]
>> date()
>> nbefore=sqlQuery(channel,"SELECT COUNT(*) FROM logger")
>> for (i in 1:ntry) {
>> sql="INSERT INTO logger (time,v1,v2,v3,v4,v5,v6,v7,v8,v9,v10) VALUES("
>> d1=strptime(ti[i,2],"%d/%m/%y %H:%M:%S %p")
>> sql=paste(sql,"'",d1,"'" )
>> sql=paste(sql,",",ti[i,3] )
>> sql=paste(sql,",",ti[i,4] )
>> sql=paste(sql,",",ti[i,5] )
>> sql=paste(sql,",",ti[i,6] )
>> sql=paste(sql,",",ti[i,7] )
>> sql=paste(sql,",",ti[i,8] )
>> sql=paste(sql,",",ti[i,9] )
>> sql=paste(sql,",",ti[i,10])
>> sql=paste(sql,",",ti[i,11])
>> sql=paste(sql,",",ti[i,12])
>> sql=paste(sql,")" )
>> #print(sql)
>> sqlQuery(channel, sql)
>> }
>> nafter=sqlQuery(channel,"SELECT COUNT(*) FROM logger")
>> nadded=nafter-nbefore;nadded
>> date()
>>
>
> I sure will try to help you out here. I've been working with RODBC. I
> think what slows you down here is your loop with multiple paste
> commands.
>
> Have you considered the sqlSave() function with the append=T argument? I
> think you could replace your loop with:
>
> dat <- cbind(strptime(ti[,2],"%d/%m/%y %H:%M:%S %p"),d1,ti[,3:12])
> sqlSave(channel,dat,"logger",append=T)
>
> Of course, I haven't tested this so you may need some minor adjustments,
> but I think this will greatly speed up your insert job.
>
> Regards,
> Jerome
>

______________________________________________
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 and provide commented, minimal, self-contained, reproducible code. Received on Sat Oct 14 03:31:41 2006

Archive maintained by Robert King, hosted by the discipline of statistics at the University of Newcastle, Australia.
Archive generated by hypermail 2.1.8, at Fri 13 Oct 2006 - 18:30:09 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.