On Mon, Nov 30, 2020, at 7:37 PM, Bill Finn wrote: > 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.
do your "tests" each run in separate processes? or is the use case when you are working on something and just want to run one test? There is one area that SQLAlchemy could improve here and it is something I've considered, but not yet proposed, which is that if you are using different ORM "registries", which in 1.3 is the declarative_base(), however in 1.4 there is now a "registry" used for all ORM mappings, that the "configure_mappers()" step, which is where a large portion of your time is being spent, would only take place for the subset of mappers local to a single registry at a time. More simply, you could segment your classes among multiple "declarative base" objects and only have the "configure" step occur for the ones that you need. I expect that if I add this feature it will produce new kinds of regressions I'm not thinking of which is one of the reasons I'm hesitant. > > On Mon, Nov 30, 2020 at 11:27 PM Mike Bayer <mike...@zzzcomputing.com> 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 sqlalchemy+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/CANTn%2BY09s96J7KYf03cjtRvVtYUud4_dSLQLfFCb9JuyfzhY6g%40mail.gmail.com > > <https://groups.google.com/d/msgid/sqlalchemy/CANTn%2BY09s96J7KYf03cjtRvVtYUud4_dSLQLfFCb9JuyfzhY6g%40mail.gmail.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 sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/7c449f73-1eac-4a90-981d-e440cf7c051b%40www.fastmail.com.
