[R] RODBC sqlQuery insert slow

[Jerome Asselin]

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