为何SQLite Select语句比numpy.select慢3-5倍？如何提速？

我的目标 #

我正在尝试在pandas与sqlite数据库之间导出和导入数据表，原因如下：

• 需要将特定数据以sqlite格式存储；
• 在基于多层if/case when语句创建新变量时，我认为sqlite语法比numpy向量化操作更清晰易懂。

我的疑问 #

运行内存中sqlite数据库的select语句速度相当慢——比使用numpy.select慢约3-5倍。我的疑问如下：

• 是什么导致了如此大的性能差距？
• 我知道numpy.select是向量化操作，而pandas.DataFrame.apply()不是，但sqlite不是用C语言编写的吗？我原本预期它的速度能与numpy相当。
• 有没有办法加速sqlite中的select语句？我尝试过创建索引，但反而让速度更慢了。
• 具体来说，当表较小时（约<1000行），sqlite速度更快，但当表达到10万行时，sqlite就比numpy.select慢了。
• 我使用SQLAlchemy是因为用sqlite3包将数据从SQL导出回pandas时遇到了问题。

10万行数据的测试结果如下：

最小可复现示例 #

以下是一个测试示例，请注意我将select语句的计时与pandas和sqlite之间的数据导入导出计时分开。

import numpy as np
import pandas as pd

from sqlalchemy.engine import create_engine
from sqlalchemy import text
import time
start = time.time()
time_df = pd.DataFrame()
time_df['start'] = [start]
rng = np.random.default_rng()
myrows = int(100e3)
mycols = 20
df = pd.DataFrame(data=rng.integers(low=0, high=100, size=(myrows, mycols)))
df['my field'] = np.arange(0,myrows)
df['y'] = df['my field']*2
df['city'] = np.tile(['Paris','New York'],int(myrows/2))
df['mydate'] = pd.to_datetime("15-Jan-2023")

time_df['df creation'] = [time.time()]

df_date_cols = [col for col in df.columns if df[col].dtype == 'datetime64[ns]']
engine = create_engine('sqlite:///:memory:', echo=False)
conn_sqla = engine.connect()
df.to_sql('df', conn_sqla)
time_df['export to sql'] = [time.time()]
values = {'myx':1}

# conn_sqla.execute(text("CREATE INDEX idx_city ON df(city)"))
# conn_sqla.execute(text("CREATE INDEX idx_myfield ON df([my field])"))
conn_sqla.execute(text("""
                  CREATE TABLE df2 as SELECT m.*
                  , case when city = 'New York' then 'NY'
                      when [my field] > 50 then 'not NY; > 50'
                      else 'not NY; <= 50'
                      end as [New field]
                  from df m
                  where [my field] > :myx
                  """), values)
time_df['run SQL select'] = [time.time()]
        
df_from_sql = pd.read_sql("df2", conn_sqla, parse_dates=df_date_cols)
conn_sqla.close()
time_df['import from SQL'] = [time.time()]

df_from_np = df.query("`my field` > 1").copy(deep=True)
conditions = {'NY': df_from_np['city'].eq("New York"),
              'not NY; > 50': df_from_np['my field'].gt(50)}

df_from_np['new field'] = np.select(conditions.values(), conditions.keys(), "not NY; <= 50")

time_df['np select'] = [time.time()]

def myfunc(city,value):
    if city == 'New York':
        return 'NY'
    elif value > 50:
        return'not NYY;  50'
    else:
        return 'not NY; <= 50'
    
df_apply = df.query("`my field` > 1").copy(deep=True)
df_apply['new field'] = df_apply.apply(lambda x: myfunc(x['city'], x['my field']), axis =1 )

time_df = time_df.transpose()
time_df['seconds elapsed'] = np.hstack([0, np.diff(time_df[0])])

print(time_df)

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