python - 为什么带有参数的 pandas.read_sql 比带有内联参数的要慢得多

Question

我在 python 中有 cgi 页面，它使用 pandas 与 SQL Server 中的数据进行交互。

概括

查询是基于用户与来自另一个查询的一些其他数据的交互运行的。这些都是使用pandas.read_sql()函数加载的。由于某种原因，第二个查询的运行速度比它在 python 中与直接在数据库上运行时（在 SQL Server Management Studio 中）时的运行速度要慢得多。经过一些测试，我发现当我使用 传递参数时查询要慢得多params=[p]，这是我最初正在做的并且更愿意这样做，而不是在查询中内联（下面的代码）。我不确定为什么会这样，并认为那里的某个人可能有一个想法。

代码

#Method 1: using param=[] 
query = "select * from FloorPlans where hydroid = ? order by plan_date desc"
t1 = datetime.datetime.now()
df2 = pd.read_sql(query, conn, params=[row["HydroID"]])
t2 = datetime.datetime.now()
print(t2-t1)

#Method 2: inline
query = "select * from FloorPlans where hydroid = '" + row["HydroID"] + "' order by plan_date desc"
t3 = datetime.datetime.now()
df2 = pd.read_sql(query, conn)
t4 = datetime.datetime.now()
print(t4-t3)

时代

方法 1：~210.0 秒

方法 2：~0.05 秒

在 SSMS 中：~0.04 秒

---

有谁知道为什么会发生这种情况？我已经检查以确保 param 方法按预期发送字符串（通过将其包装在 str() 中）并且我检查了各种值。我在 hydrooid 列上有一个聚集索引，但这无关紧要，因为它在所有三种情况下都是相同的值。我还有其他两个查询在其他表上做几乎相同的事情（在带有聚集索引的 varchar 列上选择 *），它们没有这个问题。

到目前为止，我唯一能想到的是，在 FloorPlans 表中，Hydroid 目前始终是一个数字序列（这可能会在未来发生变化，因为包含相同标识符的其他表具有带有字母数字的实例hydroids) 并且尽管确保变量是字符串，但 pandas 中的某些内容在发送到 SQL 之前将其转换回 int，这会导致查询出现问题。

标签： pythonsql-serverpython-3.xpandaspyodbc

Answer 1

如果您使用的是 Python_3，那么所有字符串都是 Unicode。Python代码...

sql = "SELECT * FROM MillionRows WHERE varchar_column = ?"
crsr.execute(sql, 'record012345')

... 在 SQL Server 上被处理为

declare @p1 int
set @p1=1
exec sp_prepexec @p1 output,N'@P1 nvarchar(24)',N'SELECT * FROM MillionRows WHERE varchar_column = @P1',N'record012345'
select @p1

请注意，参数值是 Unicode：nvarchar(24)

现在，如果我们检查 SSMS 中等效查询的实际执行计划......

SELECT * FROM MillionRows WHERE varchar_column = N'record012345'

... 我们看

   Physical operation: Index Scan
Actual Number of Rows: 1
  Number of Rows Read: 1000000

另一方面，如果我们运行一个使用varchar值的查询......

SELECT * FROM MillionRows WHERE varchar_column = 'record012345'

...执行计划向我们展示

   Physical operation: Index Seek
Actual Number of Rows: 1
  Number of Rows Read: 1

不同之处在于第一个查询必须nvarchar针对索引执行扫描（隐式转换）值varchar，而第二个查询能够执行搜索而不是扫描。

原始 Python 代码的修复方法是使用setinputsizes来指定查询参数应该是varchar...

sql = "SELECT * FROM MillionRows WHERE varchar_column = ?"
crsr.setinputsizes([(pyodbc.SQL_VARCHAR, 25)])
crsr.execute(sql, 'record012345')

.. 在 SQL Server 上被处理为

declare @p1 int
set @p1=1
exec sp_prepexec @p1 output,N'@P1 varchar(25)',N'SELECT * FROM MillionRows WHERE varchar_column = @P1','record012345'
select @p1

pandas 的一种解决方法read_sql_query是CASTSQL 查询本身的参数值

sql = "SELECT * FROM MillionRows WHERE varchar_column = CAST(? AS varchar(25))"
df = pd.read_sql_query(sql, engine, params=['record012345'])