Подтвердить что ты не робот

Почему загрузка объектов SQLAlchemy через ORM 5-8x медленнее, чем строки через необработанный курсор MySQLdb?

Я заметил, что SQLAlchemy медленно загружал (и ORMing) некоторые данные, которые были довольно быстрыми, чтобы извлекать с использованием bare-кости 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 секунды:

[email protected] ~ $ 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

Если я делаю это в python, используя MySQLdb, это занимает приблизительно 3 секунды, включая конструкцию объектов Foo:

[email protected] ~ $ 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

Однако, используя SQLAlchemy для уменьшения кода шаблона, понадобилось примерно 25 секунд для выполнения той же работы:

[email protected] ~ $ 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:[email protected]/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 работает примерно на 10 раз медленнее, чем простое решение SQL, предполагая, что SQLAlchemy должен делать примерно то же самое? Могу ли я что-то ускорить?

Это минимальный рабочий пример более сложного запроса, который объединяет несколько таблиц, используя активную загрузку. Я рассматривал возможность просто делать простые запросы в одной таблице, а затем использовать словари для создания id- > карт объектов и сопоставления отношений one-to-N. Но прежде чем это сделать, я хочу быть уверенным, что SQLAlchemy не может работать лучше, потому что писать собственную ORM - это плохая идея с точки зрения программного обеспечения. Imho, спад в 2 раза будет приемлемым (возможно).

Если вы знаете о других (более быстрых) ORM Python-SQL или, может быть, решениях BigTable, которые уже являются ORM, не стесняйтесь упоминать их как комментарий.

EDIT: Также попробовал это с Peewee, результатом чего стало ~ 15 с.

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

EDIT:. В ответ на Matthias я попытался сделать то же самое в Java с Hibernate, результат составляет примерно 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>

И, наконец, файл pom, чтобы запустить все это в maven:

<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>
4b9b3361

ОТВЕТЫ

Ответ 1

Вот версия SQLAlchemy MySQL script, которая выполняется за четыре секунды, по сравнению с тремя для MySQLdb:

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:[email protected]/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

Вот script, который использует ORM для полной загрузки строк объекта; избегая создания фиксированного списка со всеми объектами 1M одновременно с использованием yield per, это выполняется в 13 секунд с помощью мастера SQLAlchemy (18 секунд с rel 0.9):

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:[email protected]/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 секунды.

Сравнение SQLAlchemy Core более сопоставимо с исходным курсором MySQLdb. Если вы используете ORM, но запрос для отдельных столбцов, это примерно четыре секунды в последних версиях.

На уровне ORM проблемы с производительностью связаны с тем, что создание объектов на Python происходит медленно, а ORM SQLAlchemy применяет к этим объектам большой объем бухгалтерского учета, что необходимо для того, чтобы выполнить его контракт на использование, включая единицу работы, идентификационную карту, загружаемую загрузку, коллекции и т.д.

Чтобы ускорить запрос резко, выберите отдельные столбцы вместо полных объектов. См. Методы в http://docs.sqlalchemy.org/en/latest/faq/performance.html#result-fetching-slowness-orm, которые описывают это.

Для вашего сравнения с PeeWee, PW - намного более простая система с гораздо меньшими возможностями, в том числе, что она ничего не делает с картами идентичности. Даже с PeeWee, примерно так же, как ORM, как это возможно, оно по-прежнему занимает 15 секунд, что свидетельствует о том, что cPython действительно очень медленный по сравнению с необработанной выборкой MySQLdb, которая находится в прямой C.

Для сравнения с Java виртуальная машина Java способ быстрее, чем cPython. Спящий режим смехотворно сложный, но Java VM чрезвычайно быстро из-за JIT, и даже вся эта сложность заканчивается быстрее. Если вы хотите сравнить Python с Java, используйте Pypy.

Ответ 2

SQLAlchemy сложна. Он должен иметь дело с преобразованиями типов в Python, которые базовая база данных не поддерживает изначально, таблицы с наследованием, JOINs, кэширование объектов, сохранение согласованности, переведенные строки, частичные результаты и многое другое. Проверьте sqlalchemy/orm/loading.py:instance_processor - это безумие.

Решение состоит в том, чтобы скомпоновать и скомпилировать код Python для обработки результатов конкретного запроса, например Jinja2 для шаблонов. До сих пор никто не выполнял эту работу, возможно, потому что общий случай - это пара строк (где такая оптимизация была бы пессимальной), и люди, которым нужно обрабатывать массивные данные, делают это вручную, как и вы.

Ответ 3

Это не ответ на мой вопрос, но может помочь широкой общественности с проблемами скорости на больших наборах данных. Я обнаружил, что выбор миллиона записей обычно может выполняться примерно за 3 секунды, однако JOINS может замедлить процесс. В этом случае, если у одного есть приблизительно 150 К Foo, у которого отношение 1-много к 1М Bars, то выбор тех, кто использует JOIN, может быть медленным, так как каждый Foo возвращается приблизительно в 6.5 раз. Я обнаружил, что выбор обеих таблиц отдельно и объединение их с использованием dicts в python примерно в 3 раза быстрее, чем SQLAlchemy (примерно 25 секунд) и в 2 раза быстрее, чем "чистый" код python с использованием объединений (около 17 секунд). Код занял 8 секунд в моем случае использования. Выбор 1M записей без отношений, например, пример Bar, занял 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)])

Это помогло мне ускорить мои запросы, однако я более чем рад услышать от экспертов о возможных улучшениях этого подхода.