[R] RODBC sqlQuery insert slow

[Bill Szkotnicki]

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
>