ORM을 통해 SQLAlchemy 개체를 로드하는 것이 원시 MySQLDB 커서를 통해 행을 로드하는 것보다 느린 이유는 무엇입니까?

ORM을 통해 SQLAlchemy 개체를 로드하는 것이 원시 MySQLDB 커서를 통해 행을 로드하는 것보다 느린 이유는 무엇입니까?

SQL 화학에서 일부 데이터를 가져오는 속도가 느리고(그리고 ORMing) 베어본 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

MySQLDB를 사용하는 파이썬에서 이 작업을 수행하면 Foo 개체 구성을 포함하여 약 3초가 걸립니다.

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

그러나 SQL 화학을 사용하여 보일러 플레이트 코드를 줄이면 동일한 작업을 수행하는 데 약 25초가 소요되었습니다.

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

SQL 화학이 거의 동일한 작업을 수행해야 한다고 가정할 때 SQL 화학이 기본 SQL 솔루션보다 최대 10배 느리게 작동하는 이유는 무엇입니까?어떻게든 속도를 높일 수 있을까요?

이것은 빠른 로드를 사용하여 여러 테이블을 조인하는 더 복잡한 쿼리의 최소한의 작업 예입니다.저는 단일 테이블에서 간단한 쿼리를 수행한 다음 사전을 사용하여 id-> 객체 맵을 만들고 1대 N 관계를 수집하는 것을 고려하고 있었습니다.하지만 그 전에 SQL 화학이 더 나은 성능을 발휘할 수 없다는 것을 확인하고 싶습니다. ORM을 직접 작성하는 것은 소프트웨어 설계 관점에서 좋지 않은 생각이기 때문입니다.임호, 2배의 속도 저하가 허용될 수 있습니다(아마도).

다른 (더 빠른) python-SQL ORM 또는 BigTable과 유사한 솔루션(이미 ORM임)에 대해 알고 있다면 의견으로 언급하십시오.

에디트: 피위와 함께 이것을 시도했고, 결과는 ~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

편집: 마티아스에 대한 응답으로, 저는 하이버네이트를 사용하여 자바에서 같은 일을 하려고 시도했습니다. 결과는 약 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>

그리고 마지막으로 메이븐에서 모든 것을 실행할 폼 파일:

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

MySQLdb의 경우 3개의 MySQL에 비해 4초 만에 수행되는 MySQL 스크립트의 SQLChemy 버전은 다음과 같습니다.

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을 사용하여 개체 행을 완전히 로드하는 스크립트입니다. 한 번에 1M개의 개체가 모두 포함된 고정 목록이 생성되는 것을 방지하여 SQLAlchemy 마스터(rel 0.9 사용 시 18초)를 사용하여 13초 만에 실행됩니다.

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초 후에 다시 실행됩니다.

SQLAlchemyCore 비교는 원시 MySQLDB 커서와 비교하는 것이 더 적절합니다.ORM을 사용하지만 개별 열을 쿼리하는 경우 대부분의 최신 버전에서는 약 4초입니다.

ORM 수준에서 속도 문제는 Python에서 객체를 만드는 속도가 느리기 때문입니다. SQL Calchemy ORM은 객체를 가져올 때 이러한 객체에 대량의 부기를 적용합니다. 이는 작업 단위, ID 맵, eager loading, collection 등을 포함한 사용 계약을 이행하는 데 필요합니다.

쿼리 속도를 크게 높이려면 전체 개체 대신 개별 열을 가져옵니다.이를 설명하는 기술은 http://docs.sqlalchemy.org/en/latest/faq/performance.html#result-fetching-slowness-orm 을 참조하십시오.

PeeWee와 비교해 보면 PW는 훨씬 더 단순한 시스템으로, ID 맵에서 아무것도 하지 않는다는 점을 포함하여 기능이 훨씬 적습니다.PeeWee를 사용하더라도 가능한 한 간단한 ORM을 수행하는 데 15초가 걸립니다. 이는 cPython이 직선 C의 원시 MySQLdb fetch에 비해 매우 느리다는 증거입니다.

Java와 비교하면 Java VM이 cPython보다 훨씬 빠릅니다.최대 절전 모드는 터무니없이 복잡하지만 JIT로 인해 Java VM의 실행 속도가 매우 빠릅니다.Python을 Java와 비교하려면 Pypy를 사용합니다.

SQL 화학은 복잡합니다.기본 데이터베이스가 기본적으로 지원하지 않는 Python으로 유형 변환, 상속이 있는 테이블, JOIN, 개체 캐싱, 일관성 유지, 변환된 행, 부분 결과 등을 처리해야 합니다.해 보세요.sqlalchemy/orm/loading.py:instance_processor말도 안 돼요.

솔루션은 Jinja2가 템플릿에 대해 수행하는 것처럼 특정 쿼리의 결과를 처리하기 위해 Python 코드를 함께 구성하고 컴파일하는 것입니다.지금까지 이 작업을 수행한 사람은 아무도 없습니다. 일반적인 경우가 몇 줄(이런 최적화 작업은 거의 없음)이고 대량 데이터를 처리해야 하는 사용자가 손으로 처리하기 때문일 수 있습니다.

이것은 제 질문에 대한 답변은 아니지만, 일반 대중이 대용량 데이터 세트에 대한 속도 문제를 해결하는 데 도움이 될 수 있습니다.100만 개의 레코드를 선택하는 것은 일반적으로 약 3초 안에 완료될 수 있지만, JONS를 사용하면 처리 속도가 느려질 수 있습니다.이 경우 약 150k Foo가 1M Bar와 1-다중 관계에 있는 경우 각 Foo가 약 6.5배 반환되므로 JOIN을 사용하여 선택하는 작업이 느려질 수 있습니다.두 테이블을 개별적으로 선택하고 python에서 dict를 사용하여 결합하는 것이 SQLAlchemy(약 25초)보다 약 3배 빠르고 join(약 17초)을 사용하는 '베어' python 코드보다 약 2배 빠르다는 것을 알게 되었습니다.코드는 제 유스케이스에서 8초가 걸렸습니다.위의 Bar-example과 같이 관계가 없는 1M 레코드를 선택하는 데 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)])

질문 속도를 높이는 데 도움이 되었지만, 전문가로부터 이 접근 방식의 개선 가능성에 대해 듣게 되어 매우 기쁘게 생각합니다.

언급URL : https://stackoverflow.com/questions/23185319/why-is-loading-sqlalchemy-objects-via-the-orm-5-8x-slower-than-rows-via-a-raw-my