[R] RODBC sqlQuery insert slow

[ONKELINX, Thierry]

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.