Hi Mike, Thanks very much for the helpful response. I'll include a few follow-up notes below, but I suspect the solution to our problem is either to:
1. partition our tests such that any given test only imports the models that it depends on (rather than blanketly importing all of the model classes to construct the entire set of metadata); and/or 2. separate more of our tests from the SQLAlchemy models, so most of our tests don't depend on the ORM at all (1) may be tricky given the highly relational nature of our data. On Mon, Nov 30, 2020 at 11:27 PM Mike Bayer <[email protected]> wrote: > > > 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. > I see -- ok, thanks. We have roughly 600 foreign key columns, and we are using multi-table inheritance in a number of places. > 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. > Yep, that makes sense. We do build the model once per test suite invocation, so the marginal time cost per test is quite small. However, we really would like to reduce the time it takes to run a single test to make TDD faster. > 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. > Makes sense -- thank you! Bill -- 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/CANTn%2BY09s96J7KYf03cjtRvVtYUud4_dSLQLfFCb9JuyfzhY6g%40mail.gmail.com.
