Anaconda Python调用Sqlite3查询比DB Browser慢千倍的问题排查

问题现象 #

使用Python脚本处理数据：通过Pandas加载csv/xlsx文件并轻量转换后保存到Sqlite3数据库，此步骤速度正常。但后续通过Python函数执行Sqlite3查询生成中间数据集时，速度异常缓慢：

• 在DB Browser中执行单条查询仅需2-4秒，全部查询执行完成仅需1-2分钟
• 相同查询在Python脚本中耗时近20小时，性能差距超过千倍

环境信息 #

• 系统：Windows 10 Enterprise
• 运行环境：Anaconda/Python 3.9（JupyterLab）

已尝试的优化方案（均无效） #

• 为查询添加BEGIN/COMMIT事务语句，无性能提升
• 设置Sqlite3的journal_mode = WAL、synchronous = NORMAL，无效果
• 使用内存数据库：
  • 从头创建表，速度无提升，备份内存数据库成为新瓶颈
  • 创建视图，速度有所提升，但备份仍为瓶颈
• 直接在文件数据库中创建视图，速度与创建表同样缓慢

最终解决方法 #

切换至独立Python 3.11环境（仍使用JupyterLab），通过pip安装Pandas 1.5.3和Sqlite 3.38.4后，脚本速度恢复正常。推测问题根源为Anaconda分发版的库版本或默认配置存在性能问题。

相关代码示例 #

Python数据库操作函数 #

def runSqliteScript(destConnString, queryString):
    '''Runs an sqlite script given a connection string and a query string
    '''
    try: 
        print('Trying to execute sql script: ')
        print(queryString)
        cursorTmp = destConnString.cursor()
        cursorTmp.executescript(queryString)
    except Exception as e: 
        print('Error caught: {}'.format(e))

def createSqliteDb(db_file): 
    ''' Creates an sqlite database at direct/file name specified
    '''
    conSqlite = None
    try:
        conSqlite = sqlite3.connect(db_file)
        return conSqlite
    except Error as e: 
        print('Error {} when trying to create {}'.format(e, db_file)) 

示例查询SQL #

-- PRAGMA journal_mode = WAL;
-- PRAGMA synchronous = NORMAL;

BEGIN; 

drop table if exists tbl_1110_cop_omd_fmd; 
COMMIT; 
BEGIN; 
create table tbl_1110_cop_omd_fmd as

select 
        siteId, 
        orderNumber, 
        familyGroup01, familyGroup02, 
        count(*) as countOfLines
from tbl_0000_ob_trx_for_frazelle
where 1 = 1
  -- and dateCreated between datetime('now', '-365 days') and datetime('now', 'localtime') -- temporarily commented due to no date in file
group by siteId, 
         orderNumber, 
         familyGroup01, familyGroup02
order by dateCreated asc
;
COMMIT
;

内容的提问来源于stack exchange，提问作者Brad d