sqlalchemy 批量插入比构建原始 SQL 慢

Question

我正在阅读这篇关于 sqlalchemy 批量插入性能的文章。我尝试了基准测试中指定的各种方法 -SQLAlchemy ORM bulk_insert_mappings()、SQLAlchemy Core.不幸的是，要插入 1000 行，所有这些方法都需要大约 1 分钟才能插入它们。这是非常缓慢的。我也尝试了这里指定的方法 - 这需要我构建一个大型 SQL 语句，例如：

INSERT INTO mytable (col1, col2, col3)
VALUES (1,2,3), (4,5,6) ..... --- up to 1000 of these

这个原始SQL的插入是这样的：

MySession.execute('''
insert into MyTable (e, l, a)
values {}
'''.format(",".join(my_insert_str)))

使用这种方法，我将性能提高了 50x+ 倍，在 10-11 秒内达到 10000 次插入。

下面是使用内置库的方法的代码。

class MyClass(Base):
__tablename__ = "MyTable"
e = Column(String(256), primary_key=True)
l = Column(String(6))
a = Column(String(20), primary_key=True)
def __repr__(self):
return self.e + " " + self.a+ " " + self.l

.......

dict_list = []
for i, row in chunk.iterrows():
dict_list += [{"e" : row["e"], "l" : l, "a" : a}]
MySession.execute(
Myclass.__table__.insert(),
dict_list
)

以下是我连接到数据库的方式。

params = urllib.quote_plus("DRIVER={SQL Server Native Client 10.0};SERVER=servername;DATABASE=dbname;UID=user;PWD=pass")
engine = create_engine("mssql+pyodbc:///?odbc_connect=%s" % params )
MySession.configure(bind=engine, autoflush=False, expire_on_commit=False)

我的设置是否存在严重降低性能的问题？我尝试使用不同的数据库驱动程序 - pyodbc 和 pymssql。无论我尝试什么，我都无法接近他们在文章中声称的数字，即：

SQLAlchemy ORM: Total time for 100000 records 2.192882061 secs
SQLAlchemy ORM pk given: Total time for 100000 records 1.41679310799 secs
SQLAlchemy ORM bulk_save_objects(): Total time for 100000 records 0.494568824768 secs
SQLAlchemy ORM bulk_insert_mappings(): Total time for 100000 records 0.325763940811 secs
SQLAlchemy Core: Total time for 100000 records 0.239127874374 secs
sqlite3: Total time for 100000 records 0.124729156494 sec

我正在连接到 MS SQL Server 2008。如果我错过了任何其他细节，请告诉我。

原始SQL方法的问题在于它不是SQL注入安全的。因此，如果您有如何解决此问题的建议，那也将非常有帮助:)。

Answer 1

你正在做

MySession.execute(
Myclass.__table__.insert(),
dict_list
)

其中使用executemany().它与INSERT INTO ... VALUES ...不同.要使用VALUES，请执行以下操作：

MySession.execute(
Myclass.__table__.insert().values(dict_list)
)

作为旁注，SQL注入问题是使用参数解决的：

MySession.execute('''
insert into MyTable (e, l, a)
values (?, ?, ?), (?, ?, ?), ...
''', params)

这里的要点是，您不是在比较等效的构造。您没有在 SQLAlchemy 生成的查询中使用VALUES，而是在文本 SQL 中使用，并且您没有在文本 SQL 中使用参数化，而是在 SQLAlchemy 生成的查询中使用。如果为已执行的 SQL 语句打开日志记录，您将确切地看到不同之处。