如何提升VS Code中Python调用SQL Server的查询速度？

问题描述 #

在VS Code中通过Python执行SQL查询耗时约68秒，但直接在SQL Server中执行相同查询仅需约11秒。当前使用SQLAlchemy结合pandas的read_sql_query实现，担心慢查询的放大效应会抵消Python处理的优势。选择全Python流程是因为结果需要时效性，SQL Server存储过程无法保证按时就绪。

当前代码（已补全缺失的pandas导入）：

import sqlalchemy
from sqlalchemy import create_engine
import pandas as pd

server = 'server'
database = 'database'
driver = 'driver'
database_con = f'mssql://@{server}/{database}?driver={driver}'

engine = create_engine(database_con)
con = engine.connect()

t = pd.Timestamp.now()
query = """
select top 1000 c.* from table a
join table2 b on a.number=b.number
join tabl3 c on b.id=c.id
where c.[column] = 59 and a.[Date] > '2023-01-01'
"""

data = pd.read_sql_query(query, con)
query_time = pd.Timestamp.now() - t
print(query_time)

优化方案 #

1. 调整SQLAlchemy连接配置 #

• 优先使用pyodbc驱动，修改连接字符串格式以适配更高效的驱动实现：

  database_con = f'mssql+pyodbc://{server}/{database}?driver={driver}&Trusted_Connection=yes'
  

• 为create_engine添加性能优化参数，减少连接开销并加速数据交互：

  engine = create_engine(
      database_con,
      pool_pre_ping=True,  # 检测并回收失效连接
      pool_recycle=3600,  # 定期回收连接避免超时
      fast_executemany=True  # 启用批量操作优化，大幅提升数据传输速度
  )
  

2. 优化pandas数据读取逻辑 #

• 对大结果集使用分块读取，避免一次性加载全部数据占用过多内存与带宽：

  chunks = []
  for chunk in pd.read_sql(query, con, chunksize=1000):
      chunks.append(chunk)
  data = pd.concat(chunks, ignore_index=True)
  

• 显式指定返回列的数据类型，避免pandas自动推断类型带来的额外开销：

  dtype = {
      'id': 'int32',
      'column': 'int8',
      'Date': 'datetime64[ns]'
  }
  data = pd.read_sql_query(query, con, dtype=dtype)
  

3. 对齐SQL Server执行计划 #

Python会话与SSMS的默认配置差异可能导致SQL Server生成不同的执行计划，可在查询开头添加统一配置语句：

SET ARITHABORT ON;
select top 1000 c.* from table a
join table2 b on a.number=b.number
join tabl3 c on b.id=c.id
where c.[column] = 59 and a.[Date] > '2023-01-01'

SET ARITHABORT ON是SQL Server优化器生成最优执行计划的关键配置，确保和SSMS中的会话设置一致。

4. 减少数据传输量 #

• 避免使用select *，明确列出仅需的列，降低网络传输的数据体积：

  select top 1000 c.id, c.[column], c.target_column from table a
  join table2 b on a.number=b.number
  join tabl3 c on b.id=c.id
  where c.[column] = 59 and a.[Date] > '2023-01-01'
  

• 尽量在SQL Server端完成数据过滤、聚合操作，减少返回给Python的行数。

5. 优化连接复用与网络环境 #

• 复用数据库连接，将engine和con的创建放在初始化逻辑中，避免每次查询重复建立连接；
• 确保Python运行环境与SQL Server处于同一局域网内，降低跨网络传输的延迟。

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