RE: JDBC Meta-data performance for Foreign Keys
От | ldh@laurent-hasson.com |
---|---|
Тема | RE: JDBC Meta-data performance for Foreign Keys |
Дата | |
Msg-id | MN2PR15MB256042272AB6290DF87DE5AA85AC9@MN2PR15MB2560.namprd15.prod.outlook.com обсуждение исходный текст |
Ответ на | Re: JDBC Meta-data performance for Foreign Keys (Sehrope Sarkuni <sehrope@jackdb.com>) |
Ответы |
Re: JDBC Meta-data performance for Foreign Keys
(Mark Rotteveel <mark@lawinegevaar.nl>)
|
Список | pgsql-jdbc |
From: Sehrope Sarkuni <sehrope@jackdb.com> Sent: Thursday, September 30, 2021 11:14 To: ldh@laurent-hasson.com Cc: pgsql-jdbc@lists.postgresql.org Subject: Re: JDBC Meta-data performance for Foreign Keys On Fri, Sep 10, 2021 at 1:53 PM mailto:ldh@laurent-hasson.com <mailto:ldh@laurent-hasson.com> wrote: I have a piece of code that reads the meta-data for a whole database, including surveying the incoming and outgoing foreignkeys for all tables. These operations are very expensive. I am using the following code: ResultSet RS = meta.getImportedKeys(null, schemaName, tableName); ResultSet RS = meta.getExportedKeys(null, schemaName, tableName); I have instrumented my code to return the times spent in all the various things I am getting: Schemas : 23 in 33.00ms or 0.3% Tables : 311 in 3.00ms or 0.0% TableColumns: 6375 in 1,651.00ms or 13.2% PK : 307 in 960.00ms or 7.6% FK-Out : 381 in 4,451.00ms or 35.5% FK-In : 378 in 4,067.00ms or 32.4% Indices : 824 in 1,385.00ms or 11.0% Views : 362 in 18.00ms or 0.1% I have observed this behavior across multiple versions of Postgres, on Linux/Windows, most recently with the JDBC driversV42.2.15 and 42.2.19, where getting the foreign key information takes about 2/3rd of the overall time. Is this expected?Is there a way that I could make this faster? If you are getting the data for hundreds of tables, rather than invoking those methods one-by-one for each table, you caninvoke them with a null value for the schemaName and/or tableName to get results for many tables in step. You'll needto filter the results yourself to properly associate the results with the right table, but it should be doable usingthe fields in the result set. This batch approach should speed things up as you'll have a single round trip rather than hundreds (one per table). Both of those methods calls the same internal method that executes a single SQL command: https://github.com/pgjdbc/pgjdbc/blob/3a2bbd77969903f8a4ce721d45905c72bd1688d6/pgjdbc/src/main/java/org/postgresql/jdbc/PgDatabaseMetaData.java#L2226-L2320 Separately, but related, I tried to speed this up by creating multiple threads and multiple connections, but did not experienceany speedup, as if everything was getting serialized either at the JDBC driver level, or in the database. Any ideahere? If you're only interested in a subset of the fields and are willing to code your application specifically to Postgres internals(rather than the generic JDBC API), you can customize that SQL and execute your own version directly to get onlythe fields you need. Regards, -- Sehrope Sarkuni Founder & CEO | JackDB, Inc. | https://www.jackdb.com/ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Thank you Sehrope, that was a great idea! Lots of code refactoring on our end, but it was worth it as the results are quite good with a ~70% improvement! The only gotcha is what I tried to also optimize the calls for indices, I was surprised that the jdbc method DatabaseMetaData.getIndexInfo()doesn't take a table pattern like other "getXXXInfo" methods. For now we have decent performance,so dropping to a custom query to optimize indices is something we can do later. pre-optimization ========================================== Schemas: 23 in 42.00ms or 0.2% Tables: 312 in 4.00ms or 0.0% TableColumns: 6293 in 2,478.00ms or 12.6% PK: 308 in 1,381.00ms or 7.0% FK-Out: 381 in 7,186.00ms or 36.5% FK-In: 378 in 6,656.00ms or 33.8% Indices: 823 in 1,915.00ms or 9.7% Views: 362 in 22.00ms or 0.1% ------------------------------------------ Total: 19,667.00ms post-optimization ========================================== Schemas: 23 in 38.00ms or 0.7% Tables: 308 in 13.00ms or 0.2% TableColumns: 17917 in 643.00ms or 11.1% PK: 308 in 1,293.00ms or 22.3% FK-Out: 381 in 1,215.00ms or 20.9% FK-In: 378 in 697.00ms or 12.0% Indices: 823 in 1,907.00ms or 32.8% Views: 362 in 113.00ms or 1.9% ---------------------------------------------------------------------------- Total: 5,809.00ms Thank you, Laurent.
В списке pgsql-jdbc по дате отправления: