[R] RODBC sqlQuery insert slow

Armstrong, Whit whit.armstrong at hcmny.com
Fri Oct 13 16:53:05 CEST 2006


Is there a reason why the data have to be inserted 1 row at a time?

Is it possible to insert the entire table at once?

sqlSave perhaps.



> -----Original Message-----
> From: r-help-bounces at stat.math.ethz.ch 
> [mailto:r-help-bounces at stat.math.ethz.ch] On Behalf Of Bill Szkotnicki
> Sent: Friday, October 13, 2006 9:09 AM
> To: R-help at r-project.org
> Subject: [R] RODBC sqlQuery insert slow
> 
> 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()
> 
> ______________________________________________
> R-help at 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.
> 




This e-mail message is intended only for the named recipient(s) above. It may contain confidential information. If you are not the intended recipient you are hereby notified that any dissemination, distribution or copying of this e-mail and any attachment(s) is strictly prohibited. If you have received this e-mail in error, please immediately notify the sender by replying to this e-mail and delete the message and any attachment(s) from your system. Thank you.



More information about the R-help mailing list