Re: [R] RODBC sqlQuery insert slow

From: Jerome Asselin <jerome.asselin_at_crchum.qc.ca>
Date: Fri 13 Oct 2006 - 14:25:55 GMT

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.
Received on Sat Oct 14 01:35:14 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 Fri 13 Oct 2006 - 18:30:09 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.