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 -- Jerome Asselin, M.Sc., Agent de recherche, RHCE CHUM -- Centre de recherche 3875 rue St-Urbain, 3e etage // Montreal QC H2W 1V1 Tel.: 514-890-8000 Poste 15914; Fax: 514-412-7106 ______________________________________________ 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.