Module Expr Str Import alias Import alias Import alias Import alias Import alias Import alias Import alias ClassDef bases NameLoad body Assign NameStore ListLoad Assign NameStore NameLoad Assign NameStore NameLoad Assign NameStore NameLoad Assign NameStore Num FunctionDef arguments args NameParam NameParam defaults body Assign AttributeStore NameLoad attr AttributeLoad NameLoad attr Assign AttributeStore NameLoad attr AttributeLoad NameLoad attr Assign AttributeStore NameLoad attr AttributeLoad NameLoad attr Assign AttributeStore NameLoad attr AttributeLoad NameLoad attr Assign AttributeStore NameLoad attr AttributeLoad NameLoad attr Assign AttributeStore NameLoad attr AttributeLoad NameLoad attr Assign AttributeStore NameLoad attr AttributeLoad NameLoad attr Assign AttributeStore NameLoad attr AttributeLoad NameLoad attr Assign AttributeStore NameLoad attr ListLoad decorator_list FunctionDef arguments args NameParam NameParam NameParam defaults body Assign AttributeStore NameLoad attr NameLoad Assign AttributeStore NameLoad attr NameLoad decorator_list NameLoad FunctionDef arguments args NameParam NameParam defaults body If CompareIs AttributeLoad NameLoad attr NameLoad body Raise Call NameLoad Str Expr Call AttributeLoad AttributeLoad NameLoad attr attr NameLoad Return NameLoad decorator_list NameLoad FunctionDef arguments args NameParam NameParam defaults body If CompareIsNot AttributeLoad NameLoad attr NameLoad body Raise Call NameLoad BinOpMod Str AttributeLoad NameLoad attr Assign AttributeStore NameLoad attr Call NameLoad NameLoad Return NameLoad decorator_list NameLoad FunctionDef arguments args NameParam NameParam defaults body If CompareIsNot AttributeLoad NameLoad attr NameLoad body Raise Call NameLoad BinOpMod Str AttributeLoad NameLoad attr Assign AttributeStore NameLoad attr Call NameLoad NameLoad Return NameLoad decorator_list NameLoad FunctionDef arguments args NameParam defaults body If AttributeLoad NameLoad attr body Assign NameStore ListComp NameLoad comprehension NameStore AttributeLoad NameLoad attr CompareEq AttributeLoad NameLoad attr AttributeLoad NameLoad attr If UnaryOpNot NameLoad body Raise Call NameLoad BinOpMod Str AttributeLoad NameLoad attr orelse Assign NameStore AttributeLoad NameLoad attr If AttributeLoad NameLoad attr body Print Str Expr Call AttributeLoad NameLoad attr AttributeLoad NameLoad attr AttributeLoad NameLoad attr AttributeLoad NameLoad attr Print BinOpMod Str Call AttributeLoad Str attr ListComp AttributeLoad NameLoad attr comprehension NameStore NameLoad For NameStore NameLoad body Expr Call AttributeLoad NameLoad attr NameLoad Expr Call AttributeLoad SubscriptLoad AttributeLoad NameLoad attr Index Num attr decorator_list FunctionDef arguments args NameParam NameParam defaults body Assign NameStore Call AttributeLoad NameLoad attr Expr Call AttributeLoad NameLoad attr TryFinally body Assign NameStore Call NameLoad AttributeLoad NameLoad attr finalbody Expr Call AttributeLoad NameLoad attr Assign NameStore Call AttributeLoad Call AttributeLoad NameLoad attr NameLoad attr Str Expr Call AttributeLoad AttributeLoad NameLoad attr attr Call NameLoad NameLoad NameLoad keyword NameLoad Return NameLoad decorator_list FunctionDef arguments args NameParam NameParam defaults body Assign NameStore Call AttributeLoad NameLoad attr TryFinally body Return Call NameLoad AttributeLoad NameLoad attr finalbody Assign NameStore BinOpSub Call AttributeLoad NameLoad attr NameLoad Expr Call AttributeLoad AttributeLoad NameLoad attr attr Call NameLoad NameLoad NameLoad keyword NameLoad decorator_list FunctionDef arguments args NameParam NameParam defaults body If AttributeLoad NameLoad attr body Expr Call AttributeLoad NameLoad attr AttributeLoad NameLoad attr AttributeLoad NameLoad attr AttributeLoad NameLoad attr If BoolOpOr AttributeLoad NameLoad attr AttributeLoad NameLoad attr AttributeLoad NameLoad attr body Expr Call AttributeLoad NameLoad attr NameLoad orelse Expr Call AttributeLoad NameLoad attr NameLoad decorator_list FunctionDef arguments args NameParam defaults body Assign NameStore Call AttributeLoad NameLoad attr Str If CompareIs AttributeLoad NameLoad attr NameLoad body Expr Call AttributeLoad NameLoad attr Str keyword Call AttributeLoad NameLoad attr keyword Str If CompareGt Call NameLoad AttributeLoad NameLoad attr Num body Assign NameStore SubscriptLoad AttributeLoad NameLoad attr Index Num TryExcept body Assign NameStore Call NameLoad BinOpAdd BinOpAdd NameLoad Str NameLoad handlers ExceptHandler type NameLoad body Pass Expr Call AttributeLoad NameLoad attr Str keyword NameLoad keyword Str Expr Call AttributeLoad NameLoad attr Str keyword NameLoad keyword Str keyword Str Expr Call AttributeLoad NameLoad attr Str keyword NameLoad keyword AttributeLoad NameLoad attr keyword BinOpMod Str AttributeLoad NameLoad attr Expr Call AttributeLoad NameLoad attr Str keyword Str keyword Str Expr Call AttributeLoad NameLoad attr Str keyword Str keyword Str Expr Call AttributeLoad NameLoad attr Str keyword Str keyword Str Expr Call AttributeLoad NameLoad attr Str keyword Str keyword Str Expr Call AttributeLoad NameLoad attr Str keyword Str keyword Str Assign NameStore Call AttributeLoad NameLoad attr Assign AttributeStore NameLoad attr BoolOpOr AttributeLoad NameLoad attr AttributeLoad NameLoad attr Assign AttributeStore NameLoad attr BoolOpOr AttributeLoad NameLoad attr AttributeLoad NameLoad attr AttributeLoad NameLoad attr If CompareIs AttributeLoad NameLoad attr NameLoad body Assign NameStore Call NameLoad BinOpAdd BinOpAdd NameLoad Str AttributeLoad NameLoad attr Expr Call AttributeLoad Call NameLoad NameLoad attr decorator_list NameLoad FunctionDef arguments args NameParam defaults body Assign NameStore ListLoad For NameStore Call AttributeLoad NameLoad attr Call AttributeLoad AttributeLoad NameLoad attr attr NameLoad body Assign NameStore Call AttributeLoad NameLoad attr Str NameLoad If NameLoad body Expr Call AttributeLoad NameLoad attr Call AttributeLoad NameLoad attr Num Return NameLoad decorator_list NameLoad decorator_list ClassDef bases NameLoad body FunctionDef arguments args NameParam NameParam NameParam NameParam NameParam defaults NameLoad NameLoad body Assign AttributeStore NameLoad attr NameLoad Assign AttributeStore NameLoad attr NameLoad Assign AttributeStore NameLoad attr NameLoad Assign AttributeStore NameLoad attr NameLoad decorator_list FunctionDef arguments args NameParam defaults body Print Call AttributeLoad NameLoad attr If AttributeLoad AttributeLoad NameLoad attr attr body Expr Call AttributeLoad NameLoad attr decorator_list FunctionDef arguments args NameParam defaults body Assign NameStore BinOpMod Str TupleLoad AttributeLoad AttributeLoad NameLoad attr attr AttributeLoad AttributeLoad NameLoad attr attr AttributeLoad AttributeLoad NameLoad attr attr If AttributeLoad NameLoad attr body AugAssignAdd NameStore BinOpMod Str AttributeLoad NameLoad attr If AttributeLoad NameLoad attr body AugAssignAdd NameStore BinOpMod Str AttributeLoad AttributeLoad NameLoad attr attr Return NameLoad decorator_list FunctionDef arguments args NameParam defaults body If AttributeLoad AttributeLoad NameLoad attr attr body Expr Call AttributeLoad NameLoad attr orelse If AttributeLoad AttributeLoad NameLoad attr attr body Expr Call AttributeLoad NameLoad attr decorator_list FunctionDef arguments args NameParam defaults body Expr Call AttributeLoad AttributeLoad NameLoad attr attr Str Str Expr Call AttributeLoad AttributeLoad NameLoad attr attr If AttributeLoad AttributeLoad NameLoad attr attr body Expr Call AttributeLoad AttributeLoad NameLoad attr attr decorator_list FunctionDef arguments args NameParam defaults body Assign NameStore BinOpMod Str AttributeLoad AttributeLoad NameLoad attr attr TryFinally body Expr Call AttributeLoad AttributeLoad NameLoad attr attr NameLoad Expr Call AttributeLoad NameLoad attr BinOpMod Str NameLoad finalbody Expr Call AttributeLoad NameLoad attr NameLoad decorator_list decorator_list
N/A N/A A performance profiling suite for a variety of SQLAlchemy use cases.

Each suite focuses on a specific use case with a particular performance
profile and associated implications:

* bulk inserts
* individual inserts, with or without transactions
* fetching large numbers of rows
* running lots of short queries

All suites include a variety of use patterns illustrating both Core
and ORM use, and are generally sorted in order of performance from worst
to greatest, inversely based on amount of functionality provided by SQLAlchemy,
greatest to least (these two things generally correspond perfectly).

A command line tool is presented at the package level which allows
individual suites to be run::

    $ python -m examples.performance --help
    usage: python -m examples.performance [-h] [--test TEST] [--dburl DBURL]
                                          [--num NUM] [--profile] [--dump]
                                          [--runsnake] [--echo]

                                          {bulk_inserts,large_resultsets,single_inserts}

    positional arguments:
      {bulk_inserts,large_resultsets,single_inserts}
                            suite to run

    optional arguments:
      -h, --help            show this help message and exit
      --test TEST           run specific test name
      --dburl DBURL         database URL, default sqlite:///profile.db
      --num NUM             Number of iterations/items/etc for tests; default is 0
                            module-specific
      --profile             run profiling and dump call counts
      --dump                dump full call profile (implies --profile)
      --runsnake            invoke runsnakerun (implies --profile)
      --echo                Echo SQL output

An example run looks like::

    $ python -m examples.performance bulk_inserts

Or with options::

    $ python -m examples.performance bulk_inserts \
        --dburl mysql+mysqldb://scott:tiger@localhost/test \
        --profile --num 1000

.. seealso::

    :ref:`faq_how_to_profile`

File Listing
-------------

.. autosource::


Running all tests with time
---------------------------

This is the default form of run::

    $ python -m examples.performance single_inserts
    Tests to run: test_orm_commit, test_bulk_save,
                  test_bulk_insert_dictionaries, test_core,
                  test_core_query_caching, test_dbapi_raw_w_connect,
                  test_dbapi_raw_w_pool

    test_orm_commit : Individual INSERT/COMMIT pairs via the
        ORM (10000 iterations); total time 13.690218 sec
    test_bulk_save : Individual INSERT/COMMIT pairs using
        the "bulk" API  (10000 iterations); total time 11.290371 sec
    test_bulk_insert_dictionaries : Individual INSERT/COMMIT pairs using
        the "bulk" API with dictionaries (10000 iterations);
        total time 10.814626 sec
    test_core : Individual INSERT/COMMIT pairs using Core.
        (10000 iterations); total time 9.665620 sec
    test_core_query_caching : Individual INSERT/COMMIT pairs using Core
        with query caching (10000 iterations); total time 9.209010 sec
    test_dbapi_raw_w_connect : Individual INSERT/COMMIT pairs w/ DBAPI +
        connection each time (10000 iterations); total time 9.551103 sec
    test_dbapi_raw_w_pool : Individual INSERT/COMMIT pairs w/ DBAPI +
        connection pool (10000 iterations); total time 8.001813 sec

Dumping Profiles for Individual Tests
--------------------------------------

A Python profile output can be dumped for all tests, or more commonly
individual tests::

    $ python -m examples.performance single_inserts --test test_core --num 1000 --dump
    Tests to run: test_core
    test_core : Individual INSERT/COMMIT pairs using Core. (1000 iterations); total fn calls 186109
             186109 function calls (186102 primitive calls) in 1.089 seconds

       Ordered by: internal time, call count

       ncalls  tottime  percall  cumtime  percall filename:lineno(function)
         1000    0.634    0.001    0.634    0.001 {method 'commit' of 'sqlite3.Connection' objects}
         1000    0.154    0.000    0.154    0.000 {method 'execute' of 'sqlite3.Cursor' objects}
         1000    0.021    0.000    0.074    0.000 /Users/classic/dev/sqlalchemy/lib/sqlalchemy/sql/compiler.py:1950(_get_colparams)
         1000    0.015    0.000    0.034    0.000 /Users/classic/dev/sqlalchemy/lib/sqlalchemy/engine/default.py:503(_init_compiled)
            1    0.012    0.012    1.091    1.091 examples/performance/single_inserts.py:79(test_core)

        ...

Using RunSnake
--------------

This option requires the `RunSnake <https://pypi.python.org/pypi/RunSnakeRun>`_
command line tool be installed::

    $ python -m examples.performance single_inserts --test test_core --num 1000 --runsnake

A graphical RunSnake output will be displayed.

.. _examples_profiling_writeyourown:

Writing your Own Suites
-----------------------

The profiler suite system is extensible, and can be applied to your own set
of tests.  This is a valuable technique to use in deciding upon the proper
approach for some performance-critical set of routines.  For example,
if we wanted to profile the difference between several kinds of loading,
we can create a file ``test_loads.py``, with the following content::

    from examples.performance import Profiler
    from sqlalchemy import Integer, Column, create_engine, ForeignKey
    from sqlalchemy.orm import relationship, joinedload, subqueryload, Session
    from sqlalchemy.ext.declarative import declarative_base

    Base = declarative_base()
    engine = None
    session = None


    class Parent(Base):
        __tablename__ = 'parent'
        id = Column(Integer, primary_key=True)
        children = relationship("Child")


    class Child(Base):
        __tablename__ = 'child'
        id = Column(Integer, primary_key=True)
        parent_id = Column(Integer, ForeignKey('parent.id'))


    # Init with name of file, default number of items
    Profiler.init("test_loads", 1000)


    @Profiler.setup_once
    def setup_once(dburl, echo, num):
        "setup once.  create an engine, insert fixture data"
        global engine
        engine = create_engine(dburl, echo=echo)
        Base.metadata.drop_all(engine)
        Base.metadata.create_all(engine)
        sess = Session(engine)
        sess.add_all([
            Parent(children=[Child() for j in range(100)])
            for i in range(num)
        ])
        sess.commit()


    @Profiler.setup
    def setup(dburl, echo, num):
        "setup per test.  create a new Session."
        global session
        session = Session(engine)
        # pre-connect so this part isn't profiled (if we choose)
        session.connection()


    @Profiler.profile
    def test_lazyload(n):
        "load everything, no eager loading."

        for parent in session.query(Parent):
            parent.children


    @Profiler.profile
    def test_joinedload(n):
        "load everything, joined eager loading."

        for parent in session.query(Parent).options(joinedload("children")):
            parent.children


    @Profiler.profile
    def test_subqueryload(n):
        "load everything, subquery eager loading."

        for parent in session.query(Parent).options(subqueryload("children")):
            parent.children

    if __name__ == '__main__':
        Profiler.main()

We can run our new script directly::

    $ python test_loads.py  --dburl postgresql+psycopg2://scott:tiger@localhost/test
    Running setup once...
    Tests to run: test_lazyload, test_joinedload, test_subqueryload
    test_lazyload : load everything, no eager loading. (1000 iterations); total time 11.971159 sec
    test_joinedload : load everything, joined eager loading. (1000 iterations); total time 2.754592 sec
    test_subqueryload : load everything, subquery eager loading. (1000 iterations); total time 2.977696 sec

As well as see RunSnake output for an individual test::

    $ python test_loads.py  --num 100 --runsnake --test test_joinedload

 N/A argparse N/A cProfile N/A pstats N/A os N/A time N/A re N/A sys Profiler N/A object N/A N/A tests N/A N/A _setup None N/A _setup_once None N/A name None N/A num 0 __init__ N/A N/A self options N/A N/A N/A N/A self test N/A options test N/A N/A self dburl N/A options dburl N/A N/A self runsnake N/A options runsnake N/A N/A self profile N/A options profile N/A N/A self dump N/A options dump N/A N/A self callers N/A options callers N/A N/A self num N/A options num N/A N/A self echo N/A options echo N/A N/A self stats N/A N/A init N/A N/A cls name num N/A N/A N/A N/A cls name name N/A N/A cls num num N/A classmethod profile N/A N/A cls fn N/A N/A N/A N/A N/A cls name None N/A N/A N/A ValueError Need to call Profile.init(<suitename>, <default_num>) first. N/A N/A N/A N/A cls tests append fn N/A fn N/A classmethod setup N/A N/A cls fn N/A N/A N/A N/A N/A cls _setup None N/A N/A N/A ValueError N/A setup function already set to %s N/A cls _setup N/A N/A cls _setup N/A staticmethod fn N/A fn N/A classmethod setup_once N/A N/A cls fn N/A N/A N/A N/A N/A cls _setup_once None N/A N/A N/A ValueError N/A setup_once function already set to %s N/A cls _setup_once N/A N/A cls _setup_once N/A staticmethod fn N/A fn N/A classmethod run N/A N/A self N/A N/A N/A N/A self test N/A N/A tests N/A fn N/A fn N/A self tests N/A N/A fn __name__ N/A self test N/A N/A tests N/A N/A N/A ValueError N/A No such test: %s N/A self test N/A N/A tests N/A self tests N/A N/A self _setup_once N/A N/A Running setup once... N/A N/A N/A self _setup_once N/A self dburl N/A self echo N/A self num N/A N/A Tests to run: %s N/A N/A ,  join N/A N/A t __name__ N/A t tests N/A test tests N/A N/A N/A N/A self _run_test test N/A N/A N/A N/A N/A self stats N/A -1 report N/A _run_with_profile N/A N/A self fn N/A N/A N/A pr N/A N/A cProfile Profile N/A N/A N/A pr enable N/A N/A N/A result N/A fn N/A self num N/A N/A N/A N/A pr disable N/A stats N/A N/A N/A N/A pstats Stats pr sort_stats cumulative N/A N/A N/A N/A self stats append N/A TestResult self fn stats stats N/A result N/A _run_with_time N/A N/A self fn N/A N/A N/A now N/A N/A time time N/A N/A N/A N/A fn N/A self num N/A N/A total N/A N/A N/A time time now N/A N/A N/A N/A self stats append N/A TestResult self fn total_time total N/A _run_test N/A N/A self fn N/A N/A N/A N/A self _setup N/A N/A N/A N/A self _setup N/A self dburl N/A self echo N/A self num N/A N/A N/A self profile N/A self runsnake N/A self dump N/A N/A N/A N/A self _run_with_profile fn N/A N/A N/A N/A self _run_with_time fn N/A main N/A N/A cls N/A N/A N/A parser N/A N/A argparse ArgumentParser python -m examples.performance N/A N/A N/A cls name None N/A N/A N/A N/A parser add_argument name choices N/A N/A cls _suite_names help suite to run N/A N/A N/A len N/A sys argv 1 N/A N/A potential_name N/A N/A sys argv N/A 1 N/A N/A N/A suite N/A __import__ N/A N/A __name__ . potential_name N/A N/A N/A ImportError N/A N/A N/A N/A N/A parser add_argument --test type str help run specific test name N/A N/A N/A parser add_argument --dburl type str default sqlite:///profile.db help database URL, default sqlite:///profile.db N/A N/A N/A parser add_argument --num type int default N/A cls num help N/A Number of iterations/items/etc for tests; default is %d module-specific N/A cls num N/A N/A N/A parser add_argument --profile action store_true help run profiling and dump call counts N/A N/A N/A parser add_argument --dump action store_true help dump full call profile (implies --profile) N/A N/A N/A parser add_argument --callers action store_true help print callers as well (implies --dump) N/A N/A N/A parser add_argument --runsnake action store_true help invoke runsnakerun (implies --profile) N/A N/A N/A parser add_argument --echo action store_true help Echo SQL output N/A args N/A N/A parser parse_args N/A N/A args dump N/A N/A args dump N/A args callers N/A N/A args profile N/A N/A args profile N/A args dump N/A args runsnake N/A N/A N/A cls name None N/A N/A suite N/A __import__ N/A N/A __name__ . N/A args name N/A N/A N/A N/A Profiler args run N/A classmethod _suite_names N/A N/A cls N/A N/A N/A suites N/A N/A file_ N/A N/A os listdir N/A N/A N/A os path dirname __file__ N/A N/A match N/A N/A re match ^([a-z].*).py$ file_ N/A match N/A N/A N/A N/A suites append N/A N/A match group 1 N/A suites N/A classmethod N/A TestResult N/A object N/A __init__ N/A N/A self profile test stats total_time N/A None None N/A N/A N/A self profile profile N/A N/A self test test N/A N/A self stats stats N/A N/A self total_time total_time N/A report N/A N/A self N/A N/A N/A N/A N/A self _summary N/A N/A N/A self profile profile N/A N/A N/A N/A self report_stats N/A _summary N/A N/A self N/A N/A N/A summary N/A %s : %s (%d iterations) N/A N/A N/A self test __name__ N/A N/A self test __doc__ N/A N/A self profile num N/A N/A self total_time N/A N/A summary N/A ; total time %f sec N/A self total_time N/A N/A self stats N/A N/A summary N/A ; total fn calls %d N/A N/A self stats total_calls N/A summary N/A report_stats N/A N/A self N/A N/A N/A N/A N/A self profile runsnake N/A N/A N/A N/A self _runsnake N/A N/A N/A N/A self profile dump N/A N/A N/A N/A self _dump N/A _dump N/A N/A self N/A N/A N/A N/A N/A N/A self stats sort_stats time calls N/A N/A N/A N/A self stats print_stats N/A N/A N/A self profile callers N/A N/A N/A N/A N/A self stats print_callers N/A _runsnake N/A N/A self N/A N/A N/A filename N/A %s.profile N/A N/A self test __name__ N/A N/A N/A N/A N/A N/A self stats dump_stats filename N/A N/A N/A os system N/A runsnake %s filename N/A N/A N/A N/A os remove filename N/A N/A