[R] RODBC sqlQuery insert slow

[Armstrong, Whit]

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.