R help - Oct 2006 - RODBC sqlQuery insert slow

Large for loops are slow. Try to avoid them using apply, sapply, etc.
I've made the paste statements a lot shorter by using collapse. See
?paste for more info.

Append.SQL <- function(x, channel){
  sql="INSERT INTO logger (time, v1, v2, v3, v4, v5, v6, v7, v8, v9,
v10) VALUES("d1=strptime(x[2],"%d/%m/%y %H:%M:%S %p '", d1,
"' ,",
paste(x[3:12], collapse = ", "), ")" ) 
  sqlQuery(channel, sql)
}

ntry=dim(ti)[1]
date()
nbefore=sqlQuery(channel,"SELECT COUNT(*) FROM logger")
apply(ti, 2, Append.SQL, channel = channel)
nafter=sqlQuery(channel,"SELECT COUNT(*) FROM logger")
nadded=nafter-nbefore;nadded
date()

------------------------------------------------------------------------
----

ir. Thierry Onkelinx

Instituut voor natuur- en bosonderzoek / Reseach Institute for Nature
and Forest

Cel biometrie, methodologie en kwaliteitszorg / Section biometrics,
methodology and quality assurance

Gaverstraat 4

9500 Geraardsbergen

Belgium

tel. + 32 54/436 185

Thierry.Onkelinx op inbo.be

www.inbo.be 

 

Do not put your faith in what statistics say until you have carefully
considered what they do not say.  ~William W. Watt

A statistical analysis, properly conducted, is a delicate dissection of
uncertainties, a surgery of suppositions. ~M.J.Moroney


-----Oorspronkelijk bericht-----
Van: r-help-bounces op stat.math.ethz.ch
[mailto:r-help-bounces op stat.math.ethz.ch] Namens Bill Szkotnicki
Verzonden: vrijdag 13 oktober 2006 15:09
Aan: R-help op r-project.org
Onderwerp: [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 op 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.

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.