[ https://forge.continuent.org/jira/browse/SEQUOIA-885?page=comments#action_13654 ]
Guillaume Smet commented on SEQUOIA-885: ---------------------------------------- Sigh. Yes, it's an error of mine. The spec doesn't give any information on how we should take care of null values for table but only for catalog and schema (I misread it). The PostgreSQL JDBC driver does it however but it doesn't seem to be the common case. I suppose it's not a good idea to have a different code for PostgreSQL but it's really a problem when the schema is really big. Perhaps we can test if the driver supports this behavior and decide if we choose one code or the other? -- Guillaume > Fetching schema is too slow on PostgreSQL when there are too many tables > ------------------------------------------------------------------------ > > Key: SEQUOIA-885 > URL: https://forge.continuent.org/jira/browse/SEQUOIA-885 > Project: Sequoia > Type: Improvement > Components: Core > Environment: This problem concerns all versions of Sequoia released so far. > At least with PostgreSQL backends. I didn't test it on other RDBMS. > Reporter: Guillaume Smet > Assignee: Olivier Fambon > Fix For: Sequoia 2.10.5, Sequoia 3.0 > Attachments: foreign_keys_detection.diff > > > Hi Sequoia team and happy new year to everyone. > This improvement request is a followup of the following thread: > https://forge.continuent.org/pipermail/sequoia/2006-December/004229.html . > When fetching the database schema of a database containing a lot of tables > (PostgreSQL backend), it is far too slow (5 minutes for 200+ tables in our > case). It's really a problem because the schema is fetched every time a > backend is enabled (and backuped). > This is due to the discovery of foreign keys because the query generated by > the getExportedKeys/getImportedKeys methods of the PostgreSQL JDBC driver is > slow. It can be seen on this SQL queries profile obtained during a the > activation of a backend: > http://people.openwide.fr/~gsmet/sequoia/enable_backend_optimization/sequoia_enable_backend.html > I tested a few things and it happens that the query is far faster without any > schema/table name filter (100 ms instead of 2000 ms). It's not really normal > and I'll see with PostgreSQL JDBC drivers developer what we can do about this > problem. > Anyway, I decided to test what I can do by removing the per table foreign > keys detection for a global one. I obtained far better results: it's nearly > instantaneous. I only tested this change with PostgreSQL but it should work > with any database as the behaviour of getExportedKeys when we don't pass any > filter is fixed per spec. > The SQL queries profile with my patch applied can be found here: > http://people.openwide.fr/~gsmet/sequoia/enable_backend_optimization/sequoia_enable_backend_new.html > I attached a patch against 2.10 branch as it's the one I used currently and > it's a real problem for us. I checked there's no conflict with what has been > done in HEAD atm. > Feedback really welcome. I'd like to be sure I didn't do anything wrong. > -- > Guillaume -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: https://forge.continuent.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira _______________________________________________ Sequoia mailing list [email protected] https://forge.continuent.org/mailman/listinfo/sequoia
