我注意到SQLAlchemy获取(和ORMing)一些数据的速度很慢,而使用bare bone SQL获取这些数据是相当快的。首先,我创建了一个包含一百万条记录的数据库:
mysql> use foo
mysql> describe Foo;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| A | int(11) | NO | | NULL | |
| B | int(11) | NO | | NULL | |
| C | int(11) | NO | | NULL | |
+-------+---------+------+-----+---------+-------+
mysql> SELECT COUNT(*) FROM Foo;
+----------+
| COUNT(*) |
+----------+
| 1000000 |
+----------+
mysql>
作为一个粗略的测试,查询所有Foo大约需要2秒:
herbert@dev0 ~ $ date; echo 'use foo; select * from Foo;' | mysql -uroot -pxxx > /dev/null; date
zo apr 20 18:48:49 CEST 2014
zo apr 20 18:48:51 CEST 2014
herbert@dev0 ~ $ python BareORM.py
query execution time: 0:00:02.198986
total time: 0:00:03.403084
这是以下命令的输出:
#!/usr/bin/python
# -*- coding: utf-8 -*-
import MySQLdb
import sys
import time
import datetime
class Foo:
def __init__(self, a, b, c):
self.a=a; self.b=b; self.c=c;
try:
start = datetime.datetime.now()
con = MySQLdb.connect('localhost', 'root', 'xxx', 'foo')
cur = con.cursor();
cur.execute("""SELECT * FROM Foo LIMIT 1000000""")
print "query execution time: ", datetime.datetime.now()-start
foos = [];
for elem in cur:
foos.append(Foo(elem[1], elem[2], elem[3]))
con.commit()
except MySQLdb.Error, e:
print "Error %d: %s" % (e.args[0], e.args[1])
sys.exit(1)
finally:
if con: con.close()
print "total time: ", datetime.datetime.now()-start
herbert@dev0 ~ $ python AlchemyORM.py
total time: 0:00:24.649279
使用以下代码:
import sqlalchemy
import datetime
import MySQLdb
from sqlalchemy import Column, Integer, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship, backref
Base = declarative_base()
class Foo(Base):
__tablename__ = 'Foo'
id = Column(Integer, primary_key=True)
A = Column(Integer(unsigned=False), nullable=False)
B = Column(Integer(unsigned=False), nullable=False)
C = Column(Integer(unsigned=False), nullable=False)
engine = create_engine('mysql+mysqldb://root:xxx@localhost/foo')
Session = sessionmaker(bind=engine)
session = Session()
start = datetime.datetime.now()
foos = session.query(Foo).limit(1000000).all()
print "total time: ", datetime.datetime.now()-start
假设SQLAlchemy应该做大致相同的事情,为什么SQLAlchemy的运行速度比裸SQL解决方案慢约10倍?我能以某种方式加快速度吗?
这是一个更复杂的查询的最小工作示例,该查询使用预加载连接多个表。我正在考虑只对单个表执行简单的查询,然后使用字典创建id->对象映射并整理一对一关系。但是在这样做之前,我想确定SQLAlchemy不能表现得更好,因为从软件设计的角度来看,编写自己的对象关系管理不是一个好主意。Imho,2倍的减速(也许)是可以接受的。
如果您了解其他(更快的) python-SQL ORM,或者可能是类似于BigTable的解决方案(已经是ORM),请随时将它们作为注释提及。
from peewee import *
import datetime;
database = MySQLDatabase("foo", host="localhost", port=3306, user="root", passwd="xxx")
class Foo(Model):
id = IntegerField()
A = IntegerField()
B = IntegerField()
C = IntegerField()
class Meta:
db_table = 'Foo'
database = database
start = datetime.datetime.now()
foos = Foo.select()
cnt=0;
for i in foos: cnt=cnt+1
print "total time: ", datetime.datetime.now() - start
编辑:作为对Matthias的回应我尝试用Hibernate在Java语言中做同样的事情,结果大约是8到10秒,虽然不是很快,但比25秒快了很多。代码以一些类开始,以一些配置结束:
package herbert.hibernateorm;
import java.util.List;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
public class App {
public static void main(String[] args) throws Exception {
SessionFactory factory = new Configuration().configure().buildSessionFactory();
Session session = factory.openSession();
Transaction tx = session.beginTransaction();
long start = System.currentTimeMillis();
List foos = session.createQuery("FROM Foo").list();
System.out.println(foos.size());
System.out.printf("total time: %d\n", System.currentTimeMillis() - start);
session.close();
}
}
package herbert.hibernateorm;
public class Foo {
private int id, a, b, c;
public Foo() {}
public Foo(int A, int B, int C) { this.a=A; this.b=B; this.c=C; }
public int getId() { return id; }
public void setId(int id) { this.id = id; }
public int getA() { return a; }
public void setA(int a) { this.a = a; }
public int getB() { return b; }
public void setB(int b) { this.b = b; }
public int getC() { return c; }
public void setC(int c) { this.c = c; }
}
配置(分别为hibernate.cfg.xml和hibernate.hbm.xml )
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
<property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="hibernate.connection.url">jdbc:mysql://localhost:3306/foo?zeroDateTimeBehavior=convertToNull</property>
<property name="hibernate.connection.username">root</property>
<property name="hibernate.connection.password">xxx</property>
<mapping resource="hibernate.hbm.xml"/>
</session-factory>
</hibernate-configuration>
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="herbert.hibernateorm.Foo" table="Foo" catalog="foo">
<id name="id" type="int">
<column name="id" />
<generator class="assigned" />
</id>
<property name="a" type="int">
<column name="A" not-null="true" />
</property>
<property name="b" type="int">
<column name="B" not-null="true" />
</property>
<property name="c" type="int">
<column name="C" not-null="true" />
</property>
</class>
</hibernate-mapping>
最后是在maven中运行这一切的pom文件:
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>herbert</groupId>
<artifactId>hibernateORM</artifactId>
<version>1.0-SNAPSHOT</version>
<packaging>jar</packaging>
<name>hibernateORM</name>
<url>http://maven.apache.org</url>
<repositories>
<repository>
<id>unknown-jars-temp-repo</id>
<name>A temporary repository created by NetBeans for libraries and jars it could not identify. Please replace the dependencies in this repository with correct ones and delete this repository.</name>
<url>file:${project.basedir}/lib</url>
</repository>
</repositories>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>3.8.1</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.21</version>
</dependency>
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-core</artifactId>
<version>4.0.1.Final</version>
</dependency>
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-entitymanager</artifactId>
<version>4.0.1.Final</version>
</dependency>
<dependency>
<groupId>org.hibernate.common</groupId>
<artifactId>hibernate-commons-annotations</artifactId>
<version>4.0.1.Final</version>
</dependency>
<dependency>
<groupId>nz.ac.waikato.cms.weka</groupId>
<artifactId>weka-dev</artifactId>
<version>3.7.10</version>
</dependency>
<dependency>
<groupId>commons-configuration</groupId>
<artifactId>commons-configuration</artifactId>
<version>1.9</version>
</dependency>
<dependency>
<groupId>commons-net</groupId>
<artifactId>commons-net</artifactId>
<version>3.1</version>
<classifier>examples</classifier>
</dependency>
<dependency>
<groupId>com.google.code.gson</groupId>
<artifactId>gson</artifactId>
<version>2.2.2</version>
</dependency>
<dependency>
<groupId>maven</groupId>
<artifactId>maven-jetty-plugin</artifactId>
<version>1.1</version>
<type>plugin</type>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.4</version>
</dependency>
<dependency>
<groupId>com.kenai.nbpwr</groupId>
<artifactId>org-slf4j-jdk14</artifactId>
<version>1.6.1-201106101300</version>
<type>nbm</type>
</dependency>
</dependencies>
</project>
发布于 2014-08-28 02:07:12
以下是您的MySQL脚本的SQLAlchemy版本,它可以在4秒内执行,而MySQLdb只需3秒:
from sqlalchemy import Integer, Column, create_engine, MetaData, Table
import datetime
metadata = MetaData()
foo = Table(
'foo', metadata,
Column('id', Integer, primary_key=True),
Column('a', Integer(), nullable=False),
Column('b', Integer(), nullable=False),
Column('c', Integer(), nullable=False),
)
class Foo(object):
def __init__(self, a, b, c):
self.a = a
self.b = b
self.c = c
engine = create_engine('mysql+mysqldb://scott:tiger@localhost/test', echo=True)
start = datetime.datetime.now()
with engine.connect() as conn:
foos = [
Foo(row['a'], row['b'], row['c'])
for row in
conn.execute(foo.select().limit(1000000)).fetchall()
]
print "total time: ", datetime.datetime.now() - start
运行时:
total time: 0:00:04.706010
下面是一个使用ORM完全加载对象行的脚本;通过避免使用yield per一次性创建包含所有1M对象的固定列表,该脚本在 master下运行13秒(使用版本0.9运行18秒):
import time
from sqlalchemy import Integer, Column, create_engine, Table
from sqlalchemy.orm import Session
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Foo(Base):
__table__ = Table(
'foo', Base.metadata,
Column('id', Integer, primary_key=True),
Column('a', Integer(), nullable=False),
Column('b', Integer(), nullable=False),
Column('c', Integer(), nullable=False),
)
engine = create_engine('mysql+mysqldb://scott:tiger@localhost/test', echo=True)
sess = Session(engine)
now = time.time()
# avoid using all() so that we don't have the overhead of building
# a large list of full objects in memory
for obj in sess.query(Foo).yield_per(100).limit(1000000):
pass
print("Total time: %d" % (time.time() - now))
然后,我们可以将这两种方法的不同之处分开,只使用ORM加载单独的列:
for obj in sess.query(Foo.id, Foo.a, Foo.b, Foo.c).yield_per(100).limit(1000000):
pass
上面的代码再次在4秒中运行。
与原始MySQLdb游标相比,SQLAlchemy核心的比较更贴切。如果使用ORM查询单独的列,在最新的版本中大约需要4秒。
在对象关系管理级别,速度问题是因为在SQLAlchemy中创建对象很慢,Python在获取这些对象时会对这些对象进行大量的记账,这是履行其使用契约所必需的,包括工作单元、身份映射、急切加载、集合等。
要显著提高查询速度,请获取单个列,而不是整个对象。请参阅http://docs.sqlalchemy.org/en/latest/faq/performance.html#result-fetching-slowness-orm上描述这一点的技术。
与PeeWee相比,PW是一个更简单的系统,具有更少的功能,包括它不会对身份映射做任何事情。即使使用PeeWee,它仍然需要15秒的,这证明cPython与直接使用C语言的原始MySQLdb fetch相比真的很慢。
与Java相比,Java的速度比cPython快得多。Hibernate是非常复杂的ridiculously,但是由于JIT,Java的速度非常快,甚至所有这些复杂性最终都会运行得更快。如果您想将Python与Java进行比较,请使用Pypy。
发布于 2014-04-21 08:48:41
SQLAlchemy很复杂。它必须处理将类型转换为底层数据库本身不支持的Python、带有继承的表、连接、缓存对象、维护一致性、转换的行、部分结果等等。看看sqlalchemy/orm/loading.py:instance_processor
--它太疯狂了。
解决方案是拼凑并编译Python代码来处理特定查询的结果,就像Jinja2对模板所做的那样。到目前为止,还没有人完成这项工作,这可能是因为常见的情况是几行(这种优化将是悲观的),需要处理批量数据的人像您一样手工处理。
发布于 2014-04-28 19:09:48
这不是对我问题的回答,但可能会帮助普通公众解决大型数据集的速度问题。我发现选择一百万条记录通常可以在大约3秒内完成,但是JOINS可能会减慢过程。在这种情况下,一个人大约有150k Foo,这与1M条具有1-多关系,那么选择那些使用JOIN的Foo可能会很慢,因为每个Foo被返回大约6.5次。我发现在python中分别选择两个表并使用dicts连接它们的速度大约是SQLAlchemy的3倍(大约25秒),比使用joins的'bare‘python代码快2倍(大约17秒)。在我的用例中,代码花了8秒。选择1M条没有关系的记录,就像上面的Bar-example,需要3秒。我使用了以下代码:
#!/usr/bin/python
# -*- coding: utf-8 -*-
import MySQLdb
import sys
import time
import datetime
import inspect
from operator import itemgetter, attrgetter
# fetch all objects of class Class, where the fields are determined as the
# arguments of the __init__ constructor (not flexible, but fairly simple ;))
def fetch(Class, cursor, tablename, ids=["id"], where=None):
arguments = inspect.getargspec(Class.__init__).args; del arguments[0];
fields = ", ".join(["`" + tablename + "`.`" + column + "`" for column in arguments])
sql = "SELECT " + fields + " FROM `" + tablename + "`"
if where != None: sql = sql + " WHERE " + where
sql=sql+";"
getId = itemgetter(*[arguments.index(x) for x in ids])
elements = dict()
cursor.execute(sql)
for record in cursor:
elements[getId(record)] = Class(*record)
return elements
# attach the objects in dict2 to dict1, given a 1-many relation between both
def merge(dict1, fieldname, dict2, ids):
idExtractor = attrgetter(*ids)
for d in dict1: setattr(dict1[d], fieldname, list())
for d in dict2:
dd = dict2[d]
getattr(dict1[idExtractor(dd)], fieldname).append(dd)
# attach dict2 objects to dict1 objects, given a 1-1 relation
def attach(dict1, fieldname, dict2, ids):
idExtractor = attrgetter(*ids)
for d in dict1: dd=dict1[d]; setattr(dd, fieldname, dict2[idExtractor(dd)])
它帮助我加快了我的查询速度,但是我非常高兴听到专家们关于这种方法可能的改进。
https://stackoverflow.com/questions/23185319
复制相似问题