jfinkels/flask-restless — pull request #278 by andir (2014-01-14 10:50:33+00:00)
Title: Speedup to_dict with sqlalchemy.inspection

I've spent some time on figuring out what could be done to improve the performance of
flask-restless. Since most of the time consumption seems to come from to_dict I started from there.

It shortly showed up that the way the columns of the current result are being retrieved is not optimal.
The hybrid_method stuff is rather expensive, even if no or just a few hybrid properties
exist.

Since sqlalchemy 0.8 there is an inspect() method (sqlalchemy.inspection.inspect) which does what
has previosuly been done using mro & isinstance and the object_mapper calls.

Execute time has been reduced about 20-50% depending on use-case.

I've create a rather simple test case:

class Parent(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(10), unique=True)
childs = db.relationship('Child')

@hybrid_property
def test(self):
return '%s-test' % self.name
class Child(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(10), unique=True)
parent_id = db.Column(db.ForeignKey('parent.id'))
parent = db.relationship('Parent')

Create a Parent, add 100 Child's and pass the parent (with deep for childs) to to_dict.

Here is a line-profiler result of the unoptimized version (upstream/master @1d81d6486fef4fe8c5b0051ec772e2f53dc16eb3):

Function: to_dict at line 228
Total time: 23.377 s

Line # Hits Time Per Hit % Time Line Contents
==============================================================
[..]
270 50500 134320 2.7 0.6 if (exclude is not None or exclude_relations is not None) and \
271 (include is not None or include_relations is not None):
272 raise ValueError('Cannot specify both include and exclude.')
273 # create a list of names of columns, including hybrid properties
274 50500 118004 2.3 0.5 try:
275 252000 1259072 5.0 5.4 columns = [p.key for p in object_mapper(instance).iterate_properties
276 201500 613966 3.0 2.6 if isinstance(p, ColumnProperty)]
277 except UnmappedInstanceError:
278 return instance
# first hotspot here:
279 252500 702816 2.8 3.0 for parent in type(instance).mro():
280 2121000 5514722 2.6 23.6 columns += [key for key, value in parent.__dict__.items()
281 1919000 5627984 2.9 24.1 if isinstance(value, hybrid_property)]
[..]
298 202000 602245 3.0 2.6 for key, value in result.items():
299 # TODO We can get rid of this when issue #33 is resolved.
300 151500 520331 3.4 2.2 if isinstance(value, datetime.date):
301 result[key] = value.isoformat()
302 151500 476480 3.1 2.0 elif isinstance(value, uuid.UUID):
303 result[key] = str(value)
# next hotspot here:
304 151500 5372080 35.5 23.0 elif is_mapped_class(type(value)):
305 result[key] = to_dict(value)
[..]

Here the exact same code executed with inspection:
Function: to_dict at line 230
Total time: 7.14106 s

Line # Hits Time Per Hit % Time Line Contents
==============================================================
[..]
272 50500 123470 2.4 1.7 if (exclude is not None or exclude_relations is not None) and \
273 (include is not None or include_relations is not None):
274 raise ValueError('Cannot specify both include and exclude.')
275 # create a list of names of columns, including hybrid properties
276 50500 124745 2.5 1.7 instance_type = type(instance)
277 50500 113436 2.2 1.6 columns = []
278 50500 111525 2.2 1.6 try:
279 50500 785644 15.6 11.0 inspected_instance = sqlalchemy_inspect(instance_type)
280 50500 323395 6.4 4.5 column_attrs = inspected_instance.column_attrs.keys()
281 303000 841419 2.8 11.8 hybrid_columns = [ k for k, descriptor in inspected_instance.all_orm_descriptors.items()\
282 252500 646694 2.6 9.1 if descriptor.extension_type == hybrid.HYBRID_PROPERTY ]
283 50500 141898 2.8 2.0 columns = column_attrs + hybrid_columns
284 except NoInspectionAvailable:
285 return instance
[..]
302 202000 519672 2.6 7.3 for key, value in result.items():
303 # TODO We can get rid of this when issue #33 is resolved.
304 151500 459529 3.0 6.4 if isinstance(value, datetime.date):
305 result[key] = value.isoformat()
306 151500 433290 2.9 6.1 elif isinstance(value, uuid.UUID):
307 result[key] = str(value)
308 151500 380193 2.5 5.3 elif key not in column_attrs and is_mapped_class(type(value)):
309 result[key] = to_dict(value)
310 # recursively call _to_dict on each of the `deep` relations

Both of the previously recognized 'hotspots' are no longer that heavy. Due to inspection we can also
re-use some of the information gathered for the column list in order to avoid expensive type-checks.

Even thought all tests are still passing I'm not 100% certain this optimized code works in all
circumstances.

Tests had been performed by using the following script:

from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy
from flask.ext.restless.helpers import to_dict
from sqlalchemy.ext.hybrid import hybrid_property
import line_profiler

profile = line_profiler.LineProfiler()
profile.add_function(to_dict)

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite://'
db = SQLAlchemy(app)

class Parent(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(10), unique=True)
childs = db.relationship('Child')

@hybrid_property
def test(self):
return '%s-test' % self.name

class Child(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(10), unique=True)
parent_id = db.Column(db.ForeignKey('parent.id'))
parent = db.relationship('Parent')

db.create_all()

parent = Parent(name='parent-%d' % 1)
db.session.add(parent)
db.session.commit()

for ci in range(0,100):
child = Child(parent_id=parent.id, name='child-%d-%d' % (1, ci))
db.session.add(child)

db.session.commit()

if __name__ == "__main__":

from timeit import Timer

profile.enable()
def test():
to_dict(parent, deep={'childs': []})

timer = Timer(stmt=test)
print timer.repeat(5, number=100)
profile.disable()
profile.print_stats()
profile.dump_stats('to_dict.lprof')