python - 如何针对 db2 数据库优化 SQL/Python 选择查询?
问题描述
通过 Python 连接到服务器上的 db2 数据库并查询一个巨大的表(可能有 2 亿条记录,50 列)。此表用于分析(OLAP 可能是正确的术语)而不是事务。我想优化我的 sql/python 代码以更快地执行查询。
没有深入了解sql查询,我的怀疑是SELECT
语句从表的第一条记录开始,一直持续到满足查询为止。FETCH FIRST 10 ROWS ONLY
在 <1 秒内执行。但是,WHERE date_col > 20210701
在识别前 10 条记录之前,包括需要扫描几条 1 亿条记录——执行此查询需要几分钟以上的时间。性能与游标对象类似。
或者,我通过 Microsoft Access 连接到同一个表。Access 中相同的日期查询在 <.5 秒内执行——甚至比我最快的SELECT
语句还要快。Access 肯定在幕后做着我不知道的事情。
所以Access证明了这些sql查询可以快速执行的概念。我留下了一个问题:如何优化我的 sql/python 代码以匹配 Microsoft Access 的性能?谢谢大家。
import ibm_db_dbi as db
import pandas as pd
cnxn = db.connect(dsn= '********',
user= '********',
password='********',
host= '********',
database='********')
cols = "{0}col1, {0}col2, {0}col3, {0}col4".format('database.')
# Executes in <1 second
fast_sql = '''SELECT {} FROM bigtable
FETCH FIRST 10 ROWS ONLY'''.format(cols)
# Executes in ~5 seconds
slower_sql = '''SELECT {} FROM bigtable
WHERE col1 = 1234
FETCH FIRST 10 ROWS ONLY'''.format(cols)
# Giving up after ~3 minutes
slowest_sql = '''SELECT {} FROM bigtable
WHERE date_col > 20210701
FETCH FIRST 10 ROWS ONLY'''.format(cols)
df = pd.read_sql_query(horribly_slow_sql , cnxn)
cnxn.close()
解决方案
有几个因素影响了这个问题。
最重要的是一个简单的数据类型问题。date_col 不是整数也不是日期时间,而是 CHAR 格式。这导致了索引问题,导致查询缓慢。通过将结果放在引号中解决问题:
...WHERE date_col > '20210701'
而不是...WHERE date_col > 20210701
.另一个问题是对 Access 如何处理数据的误解。切换到“数据表视图”时,Access 不会执行整个查询。相反,它执行与 等效的操作
FETCH FIRST 50 ROWS ONLY
。查询出现得如此之快,因为服务器不需要将整个数据集发送到客户端。但是,导出时会执行整个查询。在我的例子中,这大约需要 5 秒,代表了一个相当于我正在执行的 Python 脚本的过程。
谢谢大家上面的评论。
推荐阅读
- java - 如何在 Apache Flink 作业中添加关闭挂钩?
- python - Scrapy、分页和 AJAX 调用
- ajax - 如何修复此 ajax 请求以停止 MVC5 中的失败
- c# - 托管服务标识是否应用于从控制台应用程序访问 Azure 应用程序服务
- xamarin - 如何在 Xcode UI builder 中放置文件打开对话框?
- tensorflow - Keras LSTM 层的输入形状应该如何
- ruby-on-rails - Ruby on Rails 测试可以/应该持续到数据库吗?
- perl - 来自另一组的 Perl 打印组
- android - Android 将 JSONArray 转换为 List
与 GSON - javascript - 如果新值为空或null,则比较两个对象并将旧对象道具值分配给新对象道具值的函数?