R包teradatasql dbGetQuery总是返回更大的数据，而python的工作速度非常快。

Question

我有一个数据库表，我试图从其中获取两列的5+百万行。

下面这段python代码完美而快速地工作(大约在3分钟内完成完整的5+行数据，通过查询检索并写入CSV)：

import pandas as pd
import teradatasql

hostname = "myhostname.domain.com"
username = "myusername"
password = "mypassword"

with teradatasql.connect(host=hostname, user=username, password=password, encryptdata=True) as conn:
    df = pd.read_sql("SELECT COL1, COL2 FROM MY_TABLE", conn)

df.to_csv(mypath, sep = '\t', index = False)

下面这段R和teradatasql包中的代码适用于显式提供的要检索的行数的小值。但是，当n足够大时(实际上并不是那么大)，或者当我请求它检索完整的5+行数据集时，它会花费大量的时间，或者几乎不会返回。

知道这是怎么回事吗？

library(teradatasql)

dbconn <- DBI::dbConnect(
  teradatasql::TeradataDriver(),
  host = 'myhostname.domain.com',
  user = 'myusername', password = 'mypassword'
  )

dbExecute(dbconn, "SELECT COL1, COL2 FROM MY_TABLE")
[1] 5348946

system.time(dbGetQuery(dbconn, "SELECT COL1, COL2 FROM MY_TABLE", n = 10))
   user  system elapsed 
  0.084   0.016   1.496 

system.time(dbGetQuery(dbconn, "SELECT COL1, COL2 FROM MY_TABLE", n = 100))
   user  system elapsed 
  0.104   0.024   1.548 

system.time(dbGetQuery(dbconn, "SELECT COL1, COL2 FROM MY_TABLE", n = 1000))
   user  system elapsed 
  0.488   0.036   1.826 

system.time(dbGetQuery(dbconn, "SELECT COL1, COL2 FROM MY_TABLE", n = 10000))
   user  system elapsed 
  7.484   0.100   9.413 

system.time(dbGetQuery(dbconn, "SELECT COL1, COL2 FROM MY_TABLE", n = 100000))
   user  system elapsed 
767.824   4.648 782.518 

system.time(dbGetQuery(dbconn, "SELECT COL1, COL2 FROM MY_TABLE", n = 5348946))
 < DOES NOT RETURN IN HOURS >

以下是一些版本信息以供参考：

> packageVersion('teradatasql')
[1] ‘17.0.0.2’
> version
               _                           
platform       x86_64-pc-linux-gnu         
arch           x86_64                      
os             linux-gnu                   
system         x86_64, linux-gnu           
status                                     
major          3                           
minor          6.1                         
year           2019                        
month          07                          
day            05                          
svn rev        76782                       
language       R                           
version.string R version 3.6.1 (2019-07-05)
nickname       Action of the Toes          

Answer 1

对于teradatasql驱动程序来说，从获取的结果集行在内存中构造大型data.frame非常慢。

要获得良好的提取性能，您需要限制从结果集中提取的一次行数。

res <- DBI::dbSendQuery (con, "select * from mytable")
repeat {
  df <- DBI::dbFetch (res, n = 100)
  if (nrow (df) == 0) { break }
}

以下是从包含两列的表中提取行的一些非正式性能测试的结果，该表包含一个integer列和一个varchar(100)列。当一次读取100行时，性能最佳。

Fetched 100000 total rows (10 rows at a time) in 28.6985738277435 seconds, throughput = 3484.49371039225 rows/sec
Fetched 100000 total rows (50 rows at a time) in 23.4930009841919 seconds, throughput = 4256.58689016736 rows/sec
Fetched 100000 total rows (100 rows at a time) in 22.7485280036926 seconds, throughput = 4395.8888233897 rows/sec
Fetched 100000 total rows (500 rows at a time) in 24.1652879714966 seconds, throughput = 4138.16711466265 rows/sec
Fetched 100000 total rows (1000 rows at a time) in 25.222993850708 seconds, throughput = 3964.63641833672 rows/sec
Fetched 100000 total rows (2000 rows at a time) in 27.1710178852081 seconds, throughput = 3680.3921156903 rows/sec
Fetched 100000 total rows (5000 rows at a time) in 34.9067471027374 seconds, throughput = 2864.77567519197 rows/sec
Fetched 100000 total rows (10000 rows at a time) in 45.7679090499878 seconds, throughput = 2184.9370459721 rows/sec