Thanks for the help ... the sqlSave() function was the solution. The lesson, which has been stated many times before, is to avoid loops wherever possible! Bill
# fast RODBC inserting dat <- cbind(as.character(strptime(ti[,2],"%d/%m/%y %H:%M:%S %p")),ti[,3:12]) # you need the as.character to make sure the time is stored correctly in mysql names(dat)=c("time","v1","v2","v3","v4","v5","v6","v7","v8","v9","v10") sqlSave(channel,dat,"logger",rownames=F,append=T) # very fast. # Jerome Asselin wrote: > On Fri, 2006-10-13 at 09:09 -0400, Bill Szkotnicki wrote: > >> 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() >> > > I sure will try to help you out here. I've been working with RODBC. I > think what slows you down here is your loop with multiple paste > commands. > > Have you considered the sqlSave() function with the append=T argument? I > think you could replace your loop with: > > dat <- cbind(strptime(ti[,2],"%d/%m/%y %H:%M:%S %p"),d1,ti[,3:12]) > sqlSave(channel,dat,"logger",append=T) > > Of course, I haven't tested this so you may need some minor adjustments, > but I think this will greatly speed up your insert job. > > Regards, > Jerome > ______________________________________________ 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.