Python SQL查询性能 [英] Python SQL Query Performance

问题描述

我正在使用jaydebeapi(Mac OS X)查询Netezza数据库并执行一些快速/肮脏的计时:

I am using jaydebeapi (Mac OS X) to query a Netezza database and performing some quick/dirty timing:

t0 = time.time()
curs.execute('''select * from table1;''')
print time.time() - t0

我自己创建了该表，它包含650,000行和9列(整数和日期).

I created the table myself and it contains 650,000 rows and 9 columns (integers and dates).

当我运行上述命令时，大约需要1.3分钟才能完成(平均运行10次以上).

When I run the above command it takes about 1.3 minutes to complete (averaged over 10 runs).

然后，当我尝试获取数据时:

Then, when I try to fetch the data:

t0 = time.time()
curs.execute('''select * from table1;''')
row = curs.fetchone()
while row is not None:
    row = curs.fetchone()
print time.time() - t0

大约需要10分钟才能完成(平均运行10次以上).

It takes about 10 minutes to complete (averaged over 10 runs).

现在，当我使用WinSQL(Windows 7，ODBC)运行相同的SQL查询时，大约需要3分钟才能返回数据.我似乎无法弄清楚为什么它在Python中花费的时间如此之长，并且不确定如何或在哪里开始寻找.

Now, when I run the same SQL query using WinSQL (Windows 7, ODBC), it takes about 3 minutes to return the data. I can't seem to figure out why it is taking so much longer in Python and am not sure how or where to start looking.

推荐答案

您是将JayDeBeApi与JPype结合使用还是与Jython结合使用?使用JPype实现获取大型结果集会导致对每个单元格值进行一些JNI调用，这会导致大量开销. 您应该考虑以下选项之一:

Are you using JayDeBeApi in combination with JPype or together with Jython? Fetching of large result sets with the JPype implementation causes some JNI calls for every single cell value which causes lot's of overhead. You should consider one of the following options:

1. 最小化结果集的大小.使用SQL函数进行聚合.
2. 尝试使用 JPype1 的最新实现.性能有了一些改善.
3. 将运行时切换到Jython(JayDeBeApi也适用于Jython)
4. 直接在Java中实现数据库查询和数据提取，并使用JPype调用逻辑，但其接口不返回大数据集.
5. 尝试改进 JPype 和

1. Minimize the size of your resultset. Do aggregations using SQL functions.
2. Give the newest implementation of JPype1 a try. There have been some performance improvements.
3. Switch your runtime to Jython (JayDeBeApi works on Jython as well)
4. Implement the db queries and data extraction directly in Java and call the logic using JPype but with a interface not returning a large data set.
5. Try to improve JPype and JayDeBeApi code

这篇关于Python SQL查询性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！