On Mon, Nov 30, 2020, at 5:17 PM, Bill Finn wrote: > Hello! > > We're running a unit test that includes constructing ≈330 tables & ≈2,000 > columns total in a local PostgreSQL 10.13 database (on disk, not in memory). > After profiling the test, we found that it takes ≈4 seconds to create all of > the SQLAlchemy `Mapper` objects [0], which are currently a prerequisite to > running the test. This time doesn't include emitting any SQL -- just > constructing the in-memory python mapper objects. > > So, my questions are: > > 1. Is this performance about expected for the number of tables & columns? Or > does it point to something we may be doing wrong?
it seems a bit steep but mapper construction is optimized to cache a lot of work up front so subsequent operations, namely loading objects, can be faster. to support all that it has to apply Python instrumentation to every attribute on every class and also construct structures that are used for value tracking, load and persist operations of every attribute on every class. It depends on how complex the relationships between these mappers are and if you are using inheritance, things like that. 330 mappers is a very large model however, it's not going to feel snappy if you have to build the entire series of them. > 2. Are there any tips or tricks for structuring tests so that a single test > that requires the database schema runs quickly? I read through the "Testing" > section > <https://www.oreilly.com/library/view/essential-sqlalchemy-2nd/9781491916544/ch04.html> > of Essential SQLAlchemy, and we'd like to avoid mocking, if possible. it seems unusual you have just a single unit test that requires a 300-mapper model to be built up and then i presume torn down? if you have many unit tests that use this model you'd want to build the model when the test suite starts, then use it for all of the tests without tearing it down. > > As far as due diligence goes, I read through the Performance FAQ > <https://docs.sqlalchemy.org/en/13/faq/performance.html>, but I didn't see a > mention of debugging slowness initializing the mapper objects, and I found > this StackOverflow post <https://stackoverflow.com/a/50823545/584663> re: > mapping slowness in the application server, but that's not my primary concern. It's not a typical issue as an application with such a huge model is typically a long running server of some kind so a few seconds for startup is not very critical. test suites would build up the model at startup just once and run all tests against the same model. > > Versions: > PostgreSQL 10.13 > SQLAlchemy 1.3.20 > > Happy to provide more details if you think they'd be helpful. > > Thanks, > Bill > > [0] > https://github.com/sqlalchemy/sqlalchemy/blob/rel_1_3_20/lib/sqlalchemy/ext/declarative/api.py#L74 > https://github.com/sqlalchemy/sqlalchemy/blob/rel_1_3_20/lib/sqlalchemy/ext/declarative/base.py#L695-L697 > (we don't set "__mapper_cls__" on any classes, so we're using the default > `mapper`) > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/1958022d-e679-4d9f-b2de-91ef7926dde9n%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/1958022d-e679-4d9f-b2de-91ef7926dde9n%40googlegroups.com?utm_medium=email&utm_source=footer>. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/1b7af31a-9fbf-456b-b401-2354995e6bfe%40www.fastmail.com.
