Re: [R] RODBC sqlQuery insert slow

From: ONKELINX, Thierry <Thierry.ONKELINX_at_inbo.be>
Date: Fri 13 Oct 2006 - 14:47:03 GMT


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

R-help@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. Received on Sat Oct 14 14:07:55 2006

Archive maintained by Robert King, hosted by the discipline of statistics at the University of Newcastle, Australia.
Archive generated by hypermail 2.1.8, at Sat 14 Oct 2006 - 05:30:10 GMT.

Mailing list information is available at https://stat.ethz.ch/mailman/listinfo/r-help. Please read the posting guide before posting to the list.