Constraint information is not being cached about sqlalchemy-redshift HOT 9 CLOSED

Thanks for catching the lack of caching on _get_redshift_constraints. Sounds like an easy optimization.

When running something like alembic which does a lot of reflection, this can slow things down considerably because the dialect winds up querying information about the entire database every time alembic asks for anything

I believe there's a fundamental constraint in SQLAlchemy's API for reflection that allows caching only within the context of reflecting a single table. I originally wrote the reflection code in this dialect with bulk queries like _get_all_relation_info hoping that we could reconstruct the entire database schema with just a few queries, but I don't believe this is possible.

I do hope that using cache_info for the constraints query will speed things up a bit, but reflection across many tables is likely going to continue to be slow.

from sqlalchemy-redshift.

PR for this in #102

from sqlalchemy-redshift.

Also see #103 for more thoughts on persisting schema info across multiple tables.

from sqlalchemy-redshift.

I think your approach of doing just a few queries actually does work. You're right that the reflection methods that make up the public interface of the dialect generally require a table name, and that caching the result of those methods by itself won't reduce the number of queries that you have to make, but caching the result of your _get_all_* methods, which return information about the whole database, does definitely speed things up considerably. With the change you made in PR #102, a test that we have for making sure that our migrations are up to date goes from 2+ minutes to about 3 seconds.

from sqlalchemy-redshift.

a test that we have for making sure that our migrations are up to date goes from 2+ minutes to about 3 seconds

Wow. That is much more improvement than I expected.

from sqlalchemy-redshift.

Well, previously that query in _get_all_constraint_info was being run over and over and over again. Now it only gets run once.

from sqlalchemy-redshift.

Thank you again for inspecting the code enough to figure out what was going on. I look forward to seeing whether this significantly improves some of my own use cases.

from sqlalchemy-redshift.

And thank you for the quick response. :)

It's an especially large gain for us because we often have a bunch of stale schemas in this cluster. Therefore, the _get_all_constraint_info query slows down slightly, and the cumulative effect of running that slightly slower query over and over again can cause the test that I mentioned to take 2 minutes or longer. I guess you probably won't notice as much of a speedup, but probably still some.

from sqlalchemy-redshift.

This change is now merged to master.

from sqlalchemy-redshift.