{"action":"opened","issue":{"url":"https://api.github.com/repos/Paradigm4/SciDBR/issues/94","repository_url":"https://api.github.com/repos/Paradigm4/SciDBR","labels_url":"https://api.github.com/repos/Paradigm4/SciDBR/issues/94/labels{/name}","comments_url":"https://api.github.com/repos/Paradigm4/SciDBR/issues/94/comments","events_url":"https://api.github.com/repos/Paradigm4/SciDBR/issues/94/events","html_url":"https://github.com/Paradigm4/SciDBR/issues/94","id":156335136,"number":94,"title":"Ability to efficiently execute AFL query for repeated call case","user":{"login":"ksens","id":13973052,"avatar_url":"https://avatars.githubusercontent.com/u/13973052?v=3","gravatar_id":"","url":"https://api.github.com/users/ksens","html_url":"https://github.com/ksens","followers_url":"https://api.github.com/users/ksens/followers","following_url":"https://api.github.com/users/ksens/following{/other_user}","gists_url":"https://api.github.com/users/ksens/gists{/gist_id}","starred_url":"https://api.github.com/users/ksens/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/ksens/subscriptions","organizations_url":"https://api.github.com/users/ksens/orgs","repos_url":"https://api.github.com/users/ksens/repos","events_url":"https://api.github.com/users/ksens/events{/privacy}","received_events_url":"https://api.github.com/users/ksens/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":0,"created_at":"2016-05-23T18:25:47Z","updated_at":"2016-05-23T18:25:47Z","closed_at":null,"body":"Recently ran into the following workflow using SciDBR (writing SciDB code):\r\n```{R}\r\nfor (param in myList){\r\naflstr = formulateQuery(params); # formulate AFL query based on the parameters\r\nresult = iquery(aflstr, return=TRUE)\r\n# Do some processing on result\r\n}\r\n```\r\nAlso, it was guaranteed that the different `aflstr` would always return the same nature of `result` i.e. identical attribute names, type and nullability. \r\n\r\nNoticed an inefficiency here:\r\n - `iquery()` always interrogates SciDB using `aflstr` to find out the nature of returned attributes, types and nullability. This is inefficient as the result is always same -- that part should be unrolled out of the loop.\r\n\r\nNext, if we use `foreach` instead of `for` and then use `doRedis` to distribute the load across different worker nodes, the processing inside the `for` loop above needs to be prefaced with:\r\n```\r\nscidbconnect(HOST, PORT)\r\n```\r\nNow, `scidbconnect()` function executes a few AFL queries per each connection (e.g. `setopt(), list('libraries')`). This also introduces inefficiency because the total number of queries that hit SciDB goes up by a factor. \r\n\r\nFor my use case, I combined code from `scidbconnect()` and `scidb_unpack_to_dataframe()` to create a more efficient function. Note that all the options of `scidbconnect()` are not represented in my function yet (e.g. it assumes HTTP connection and has no option for HTTPS). I am providing the code here as a suggestion because the need for such efficient query execution in SciDBR will surely arise in other workflows.\r\n\r\n```{R}\r\n#' fastquery run query from R but without scidbconnect(), iquery() etc. \r\n#' @export\r\nfastquery = function(host = options(\"scidb.default_shim_host\")[[1]], \r\n                     port = options(\"scidb.default_shim_port\")[[1]], \r\n                     aflstr, attribs, TYPES, NULLABILITY)\r\n{\r\n  # Formulate the shim url\r\n  shimurl=paste(\"http://\", host, \":\", port, sep = \"\")\r\n  \r\n  # Connect to SciDB\r\n  # /new_session\r\n  id = httr::GET(paste(shimurl, \"/new_session\", sep=\"\"))\r\n  session = rawToChar(id$content)\r\n  \r\n  # AFL query and save format\r\n  savestr=sprintf(\"(%s)\", paste(TYPES, ifelse(NULLABILITY, \"+null\", \"\"), sep=\"\", collapse = \",\"))\r\n  \r\n  # Now formulate and execute the query\r\n  # /execute_query\r\n  query = sprintf(\"%s/execute_query?id=%s&query=%s&save=%s\",\r\n                  shimurl, session, aflstr, savestr)\r\n  resp = httr::GET(query)\r\n  \r\n#   # View the status of the result\r\n#   cat(\"save status: \")\r\n#   cat(sprintf(\"%d\\n\", resp$status_code))\r\n  \r\n  # Get the data returned by the previous save\r\n  # /read_bytes\r\n  resp = httr::GET(sprintf(\"%s/read_bytes?id=%s&n=0\", shimurl, session))\r\n#   cat(\"read status: \")\r\n#   cat(sprintf(\"%d\\n\", resp$status_code))\r\n#   print(resp)\r\n  \r\n  # Release the session\r\n  httr::GET(sprintf(\"%s/release_session?id=%s\", shimurl, session))\r\n  #print(res)\r\n  \r\n  #BEGIN: Copied from scidbr/R/internal.R >> scidb_unpack_to_dataframe()\r\n  len = length(resp$content)\r\n  p = 0 \r\n  ans = c()\r\n  \r\n  # BEGIN: Hard coded for tests\r\n  buffer = 100000L\r\n  # END: HARD CODED\r\n  \r\n  cnames = c(attribs, \"lines\", \"p\")  # we are unpacking to a SciDB array, ignore dims\r\n  n = length(attribs)\r\n  \r\n  while(p < len)\r\n  {\r\n    #  dt2 = proc.time()\r\n    tmp   = .Call(\"scidb_parse\", as.integer(buffer), TYPES, NULLABILITY, resp$content, as.double(p), PACKAGE=\"scidb\")\r\n    names(tmp) = cnames\r\n    lines = tmp[[n+1]]\r\n    p_old = p\r\n    p     = tmp[[n+2]]\r\n    #  if(DEBUG) cat(\"  R buffer \", p, \"/\", len, \" bytes parsing time\", (proc.time() - dt2)[3], \"\\n\")\r\n    #  dt2 = proc.time()\r\n    if(lines > 0)\r\n    {\r\n          if(\"binary\" %in% TYPES)\r\n          {\r\n#            if(DEBUG) cat(\"  R rbind/df assembly time\", (proc.time() - dt2)[3], \"\\n\")\r\n            return(lapply(1:n, function(j) tmp[[j]][1:lines]))\r\n          }\r\n      len_out = length(tmp[[1]])\r\n      if(lines < len_out) tmp = lapply(tmp[1:n], function(x) x[1:lines])\r\n      # adaptively re-estimate a buffer size\r\n      avg_bytes_per_line = ceiling((p - p_old) / lines)\r\n      buffer = min(getOption(\"scidb.buffer_size\"), ceiling(1.3 * (len - p) / avg_bytes_per_line)) # Engineering factors\r\n      # Assemble the data frame\r\n      if(is.null(ans)) ans = data.table::data.table(data.frame(tmp[1:n], stringsAsFactors=FALSE))\r\n      else ans = rbind(ans, data.table::data.table(data.frame(tmp[1:n], stringsAsFactors=FALSE)))\r\n    }\r\n    #  if(DEBUG) cat(\"  R rbind/df assembly time\", (proc.time() - dt2)[3], \"\\n\")\r\n  }\r\n  ans = as.data.frame(ans)\r\n  ans\r\n  #END: Copied from scidbr/R/internal.R >> scidb_unpack_to_dataframe()\r\n}\r\n```"}}
{"actor":{"display_login":"ksens"}}