[R-sig-DB] Time taken by dbExistsTable()
Mark Van De Vyver
mvyver @end|ng |rom gm@||@com
Tue May 23 00:02:17 CEST 2006
Hi,
Fristly thanks for the efforts put into the DBI package for R-project.
This has enhanced my use of R considerably - several colleagues raise
their eye brows when I indicate I no-longer use SAS.....
My set-up:
Windows-xp
R-2.2.1
R-MySQL package.
MySQL 5.0.19-nt (using TCP-IP connections)
I will highlight that the db in question has 10,000+ tables, so the
following may not be of general interest/concern.
I have noticed that considerable time is used by the function
dbExistsTable(), about 5 times more than the SQL 'show table' work
around - in my case this translates to about a 6 hour saving.
This of course may be due to my 'set-up' rather than dbExistsTable()?
Is it possible my set-up is at fault?
Is it possible to improve this time further using R-DBI?
The timing results are shown below.
Regards
Mark
system.time(
for(tbli in tblunionlist[1:15]){
tblExist <- dbExistsTable(dbcon, tbli)
}
)
system.time(
for(tbli in tblunionlist[1:15]){
myshowstring<-paste("SHOW TABLE STATUS FROM `myDB` LIKE '",tbli, "'", sep="")
mytblstatus<-mysqlQuickSQL( dbcon, myshowstring )
if( identical( identical(mytblstatus,data.frame(NULL)),F) ) {
tblExist <- TRUE
}
}
)
[1] 4.96 1.52 29.20 NA NA
[1] 1.99 0.30 5.64 NA NA
More information about the R-sig-DB
mailing list