MySQL查询在工作台上工作良好，但在R中花费的时间太长 [英] Mysql query works well at workbench but takes too long in r

问题描述

我有一个要在R中运行的查询，该查询从数据库中检索数据并对其执行操作.当我在mysql workbench中运行它时，它工作得很好，但是在r中，它花费的时间太长，可能会挂起整个系统.我也尝试在命令提示符下运行它，但收到错误:

I have a query to run in R which retrieves data from the database and performs operations on it. When I run it in mysql workbench, it works just fine but in r it takes way too long and may hang the entire system. I also tried to run it in command prompt but got the error:

Error: memory exhausted (limit reached?)

mysql查询:

library(DBI)
  library(RMySQL)

  con <- dbConnect(RMySQL::MySQL(),
                   dbname ="mydb",
                   host      = "localhost",
                   port  = 3306,
                   user  = "root",
                   password = "")



pedigree <- dbGetQuery (connection, "SELECT aa.name as person, mother as mom, father as dad
    FROM addweight LEFT JOIN aa ON addweight.name2 = aa.name2 or addweight.name = aa.name
    LEFT JOIN death ON addweight.name2 = death.name2 or addweight.name = death.name 
Where((death.dodeath > curdate() OR aa.name2 NOT IN (SELECT name2 FROM death)  OR  aa.name NOT IN (SELECT name FROM death) OR  aa.name NOT IN (SELECT name FROM death)) AND (dob < curdate() AND domove < curdate()))")

推荐答案

解决方案可能是将dbGetQuery替换为dbSendQuery和dbFetch调用.

The solution could be to replace dbGetQuery with dbSendQuery and dbFetch call.

简单的步骤可能是:

library(RMySQL)

# From OP
con <- dbConnect(RMySQL::MySQL(),
               dbname ="mydb",
               host      = "localhost",
               port  = 3306,
               user  = "root",
               password = "")

# iterationresults is a table in your database. One can replace query with his own
rs = dbSendQuery(con, "select * from iterationresults")

# Fetch first 20 rows and repeat it for all rows  
df = dbFetch(rs, n=20)

# For repeated call
while (!dbHasCompleted(rs)){
 df<- dbFetch(rs, n=20)
}
# OR Fetch all rows in one go
df = dbFetch(rs, n=-1)

# Free all resources
dbClearResult(rs) 
# Close connection
dbDisconnect(con)
# df will contain results i.e.
df
#   ID Truck_ID Speed trip_id
#1  11  TTI 039     6     217
#2  12  TTI 039     6     217
# ........

这篇关于MySQL查询在工作台上工作良好，但在R中花费的时间太长的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！