{"action":"created","issue":{"url":"https://api.github.com/repos/sqlalchemy/sqlalchemy/issues/4379","repository_url":"https://api.github.com/repos/sqlalchemy/sqlalchemy","labels_url":"https://api.github.com/repos/sqlalchemy/sqlalchemy/issues/4379/labels{/name}","comments_url":"https://api.github.com/repos/sqlalchemy/sqlalchemy/issues/4379/comments","events_url":"https://api.github.com/repos/sqlalchemy/sqlalchemy/issues/4379/events","html_url":"https://github.com/sqlalchemy/sqlalchemy/issues/4379","id":384991621,"node_id":"MDU6SXNzdWUzODQ5OTE2MjE=","number":4379,"title":"Metadata reflection slow with large schemas","user":{"login":"flewellyn","id":29442917,"node_id":"MDQ6VXNlcjI5NDQyOTE3","avatar_url":"https://avatars3.githubusercontent.com/u/29442917?v=4","gravatar_id":"","url":"https://api.github.com/users/flewellyn","html_url":"https://github.com/flewellyn","followers_url":"https://api.github.com/users/flewellyn/followers","following_url":"https://api.github.com/users/flewellyn/following{/other_user}","gists_url":"https://api.github.com/users/flewellyn/gists{/gist_id}","starred_url":"https://api.github.com/users/flewellyn/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/flewellyn/subscriptions","organizations_url":"https://api.github.com/users/flewellyn/orgs","repos_url":"https://api.github.com/users/flewellyn/repos","events_url":"https://api.github.com/users/flewellyn/events{/privacy}","received_events_url":"https://api.github.com/users/flewellyn/received_events","type":"User","site_admin":false},"labels":[{"id":1141277259,"node_id":"MDU6TGFiZWwxMTQxMjc3MjU5","url":"https://api.github.com/repos/sqlalchemy/sqlalchemy/labels/mssql","name":"mssql","color":"C02080","default":false},{"id":1142425974,"node_id":"MDU6TGFiZWwxMTQyNDI1OTc0","url":"https://api.github.com/repos/sqlalchemy/sqlalchemy/labels/performance","name":"performance","color":"aa29f4","default":false},{"id":1141273914,"node_id":"MDU6TGFiZWwxMTQxMjczOTE0","url":"https://api.github.com/repos/sqlalchemy/sqlalchemy/labels/postgres","name":"postgres","color":"60A070","default":false},{"id":1142415575,"node_id":"MDU6TGFiZWwxMTQyNDE1NTc1","url":"https://api.github.com/repos/sqlalchemy/sqlalchemy/labels/reflection","name":"reflection","color":"60e0cb","default":false}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":{"url":"https://api.github.com/repos/sqlalchemy/sqlalchemy/milestones/51","html_url":"https://github.com/sqlalchemy/sqlalchemy/milestone/51","labels_url":"https://api.github.com/repos/sqlalchemy/sqlalchemy/milestones/51/labels","id":3850624,"node_id":"MDk6TWlsZXN0b25lMzg1MDYyNA==","number":51,"title":"1.4","description":null,"creator":{"login":"sqlalchemy-bot","id":36047385,"node_id":"MDQ6VXNlcjM2MDQ3Mzg1","avatar_url":"https://avatars2.githubusercontent.com/u/36047385?v=4","gravatar_id":"","url":"https://api.github.com/users/sqlalchemy-bot","html_url":"https://github.com/sqlalchemy-bot","followers_url":"https://api.github.com/users/sqlalchemy-bot/followers","following_url":"https://api.github.com/users/sqlalchemy-bot/following{/other_user}","gists_url":"https://api.github.com/users/sqlalchemy-bot/gists{/gist_id}","starred_url":"https://api.github.com/users/sqlalchemy-bot/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/sqlalchemy-bot/subscriptions","organizations_url":"https://api.github.com/users/sqlalchemy-bot/orgs","repos_url":"https://api.github.com/users/sqlalchemy-bot/repos","events_url":"https://api.github.com/users/sqlalchemy-bot/events{/privacy}","received_events_url":"https://api.github.com/users/sqlalchemy-bot/received_events","type":"User","site_admin":false},"open_issues":26,"closed_issues":2,"state":"open","created_at":"2018-11-27T04:11:27Z","updated_at":"2018-11-28T15:47:57Z","due_on":null,"closed_at":null},"comments":4,"created_at":"2018-11-27T21:22:41Z","updated_at":"2018-11-28T19:44:08Z","closed_at":null,"author_association":"NONE","body":"Using SQLAlchemy 1.2.14, I attempted to reflect a schema from a customer which contained several thousand tables, and noticed this was very slow.  Using echo=\"debug\" as an option on the engine, I noticed that the SQLAlchem reflection code is iteratively querying the database for table metadata, instead of issuing one query.\r\n\r\nI believe this is sub-optimal, and performance would be greatly enhanced if the SQLAlchemy reflection code issued one query to the backend instead of iterating over the table list.  \r\n\r\nExample times: MS SQL Server database with 3,300 tables: 15 minutes.\r\nPostgreSQL database with 694 tables: 4 minutes.\r\nPostgreSQL database with 18,000+ tables: 45 minutes.\r\n"},"comment":{"url":"https://api.github.com/repos/sqlalchemy/sqlalchemy/issues/comments/442579254","html_url":"https://github.com/sqlalchemy/sqlalchemy/issues/4379#issuecomment-442579254","issue_url":"https://api.github.com/repos/sqlalchemy/sqlalchemy/issues/4379","id":442579254,"node_id":"MDEyOklzc3VlQ29tbWVudDQ0MjU3OTI1NA==","user":{"login":"zzzeek","id":128223,"node_id":"MDQ6VXNlcjEyODIyMw==","avatar_url":"https://avatars0.githubusercontent.com/u/128223?v=4","gravatar_id":"","url":"https://api.github.com/users/zzzeek","html_url":"https://github.com/zzzeek","followers_url":"https://api.github.com/users/zzzeek/followers","following_url":"https://api.github.com/users/zzzeek/following{/other_user}","gists_url":"https://api.github.com/users/zzzeek/gists{/gist_id}","starred_url":"https://api.github.com/users/zzzeek/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/zzzeek/subscriptions","organizations_url":"https://api.github.com/users/zzzeek/orgs","repos_url":"https://api.github.com/users/zzzeek/repos","events_url":"https://api.github.com/users/zzzeek/events{/privacy}","received_events_url":"https://api.github.com/users/zzzeek/received_events","type":"User","site_admin":false},"created_at":"2018-11-28T19:44:08Z","updated_at":"2018-11-28T19:44:08Z","author_association":"MEMBER","body":"The architectural changes I described are pretty straightforward.  the thing that made this seem daunting was the \"one\" query part where all the separate things like constraints and indexes would need to be  merged together; there's no reason that needs to be done.    the solution would be, if we have 1000 tables, instead of 1000 get columns queries, 1000 get indexes queries, 1000 get constraint queries, etc., we have one query for each of those.  so the 1000-table reflection (which is pretty realistic as a use case) would involve about eight or nine queries for each of those different things.    \r\n\r\nso to make it happen quickly, adding the availability of those \"bulk\" methods, adding a new method to Inspector that does what reflecttable() does, except for a list of table names and then calls into the bulk methods, organizing metadata.reflect() to create a list of Table() objects up front and send them to inspector.bulk_reflecttable(), would be what is needed to start.  then we can go into the target dialects and change methods as needed to work in terms of get_multi_xyz(); the get_columns() methods of SQL Server and Postgresql would serve your use case right now.\r\n\r\ni've milestoned this at 1.4 since 1.3 is in betas now but if you were highly motivated to make this happen sooner it can be released sooner, as we have very good test coverage of reflection at this point."}}
{"actor":{"display_login":"zzzeek"}}