JDBC method to get child tables and all its descendants

[S Ganesh]

Is there a JDBC method to get child tables and all of its descendants.

getExportedKeys returns only the direct child and not all its descendants. If I keep calling getExportedKeys() method recursively , it takes around 3 minutes for 50 tables or so (which is really slow)

Could someone please help me out for a solution. If not JDBC , is there any other open source API/tool which helps me in getting the solution.

Main objective is to avoid using queries since I would be dealing with different databases.

Recursive function used:

 private static void getChildTables(DatabaseMetaData dbmd,Set<String> dependencies,String tableName,String schemaname ) throws SQLException{
  		ResultSet rs3 = dbmd.getExportedKeys(null,schemaname, tableName);    
          while (rs3.next()){    	
 if(dependencies.contains(rs3.getString(7)))
  	continue;
  dependencies.add(rs3.getString(7));
  String childTable=rs3.getString(7);
 getChildTables(dbmd, dependencies, childTable, null);
  rs3.close();
  rs3 = dbmd.getExportedKeys(null,schemaname, tableName);                    

          }

[Roel De Nijs]

S Ganesh wrote:If I keep calling getExportedKeys() method recursively , it takes around 3 minutes for 50 tables or so (which is really slow)

Why do you need to call the getExportedKeys method recursively? You just invoke this method once for every table you are interested about.

[S Ganesh]

Example: if A->B A has B as child and Say B->C B has C as child . getExportedkeys called on A would return only B and not C. This is why I am calling recursively... I want child tables and all its Descendants...

[Roel De Nijs]

I can understand that use case.

In the while loop you invoke the getChildTables method with childTable as parameter (for recursiveness). But you also invoke the getExportedKeys method again with the exact same parameters. Why?

Note about code quality: you use rs3.getString(7) 3 times in just 12 lines of code. Why not do something like

while (rs3.next()) {
  String childTable = rs3.getString(7);
  if (dependencies.contains(childTable))
    continue;
  dependencies.add(childTable);
  getChildTables(dbmd, dependencies, childTable, null);

[Roel De Nijs]

Roel De Nijs wrote:But you also invoke the getExportedKeys method again with the exact same parameters. Why?

I see now why you do this. Because you are closing the result set inside the loop. Why are you closing the result set inside the loop? Why not outside the loop?

[S Ganesh]

Thanks @Roel De Nijs for the suggestion. I have changed everything as you said. I have put the closing of result set outside the loop but it takes the same time. Is there an alternate solution/work-around for getting all child tables and all its descendants.

[Roel De Nijs]

For how many tables are you querying the exported keys in total? And how much time does it take to execute the getChildTables for 1 table?

[S Ganesh]

The total tables in the database would be around 100-120 tables. getExportedKeys for each tables varies. But on an average I would say around 2-3 seconds.

[Roel De Nijs]

S Ganesh wrote:The total tables in the database would be around 100-120 tables. getExportedKeys for each tables varies. But on an average I would say around 2-3 seconds.

So 120 times 2 seconds = 240 seconds which is 4 minutes (which is exactly what you are experiencing).

Now if you comment out the recursive call to the getChildTables method, how long takes the execution of this method on average?

[S Ganesh]

If I comment out the recursive call , it would be quick only as it is just gonna verify child tables for only a single table only. May be 1-2 second(s) or at max 3 seconds (varies for each table)

But the main reason for recursive call is to not only get all its direct child tables and but also all its descendants.

[Roel De Nijs]

S Ganesh wrote:If I comment out the recursive call , it would be quick only as it is just gonna verify child tables for only a single table only. May be 1-2 second(s) or at max 3 seconds (varies for each table)

1-2 seconds for a single query is very slow... And it's almost the same as the recursive call.

I know why you need the recursive call. I'm trying to troubleshoot your issue by trying to detect what the bottleneck is causing the slow performance

[S Ganesh]

Thanks @Roel De Nijs. Kindly let me if you find any other alternate solutions/work-around. It would be of great help to me.

[Paul Clapham]

I don't see why a recursive algorithm is necessary. Why not just start by getting a list of all the tables in the schema, and then find the exported keys for each of them?

[Roel De Nijs]

Paul Clapham wrote:Why not just start by getting a list of all the tables in the schema, and then find the exported keys for each of them?

That would be my approach as well...

[S Ganesh]

I am designing a portal where a user will be entering the database configuration details and I would connect to the database and get him the list of all tables. Then based on his selection of tables I would show the child tables and its Descendants. So the database that I would connect to is dynamic and on the fly. I will try what you said. But wouldn't it be a bad approach to call getExportedkeys for each table , considering it would be around 1000 tables or so.

[Paul Clapham]

S Ganesh wrote:The total tables in the database would be around 100-120 tables. getExportedKeys for each tables varies. But on an average I would say around 2-3 seconds.

S Ganesh wrote:But wouldn't it be a bad approach to call getExportedkeys for each table , considering it would be around 1000 tables or so.

[S Ganesh]

The database I am using now for testing purpose is around 100-120 tables only. But the maximum range would be around 1000 tables that is why specified as 1000 tables.

[Roel De Nijs]

I'm still wondering how much time it takes to get the exported keys for 1 single table. Because I have a hard time believing it would take 1-2 seconds which is really slow for such simple query. It seems you just mentioned an arbitrary number without actually testing it.

[S Ganesh]

Nope I tested it out today also. I have two test databases installed. One installed locally and other on a different server. I even tested out locally today which just has around 23 tables. And it took me from 800-900 milliseconds for each table. But if I use queries recursively instead of getExportedkeys , it takes only 400-450 milliseconds on an average which is half of what consumed in jdbc getExportedkeys method. But I would like to get it done in JDBC method itself as I don't want end up writing queries for each database.

[S Ganesh]

Again this is local database which has just fewer tables and less complex compared to the one installed in a different server , that is why it is around 900 milliseconds. I even read in some sites stating these operations like getExportedKeys method are generally slow but I am not sure whether it is true.

[Roel De Nijs]

So it's 800-900 milliseconds per table or for all (23) tables?

It's indeed a daunting task to write queries for every kind of database and it's easier to simply use getExportedkeys. But the latter one seems to be much slower. So it seems it's ease of use against performance. Using a query you have much more control and you probably can return all keys for all tables with just one query. That's something which is impossible with using the getExportedkeys method.

[S Ganesh]

I guess that might be the alternate solution. I will opt for queries then. I have a doubt in queries. In oracle there is a query syntax where I can start with the specified table name and link with the child tables and all its descendants. Example: If A->B , A HAS b AS CHILD AND B-> , B has C as child,C->d and so on the result would be:

Level Table Name Child table Name
1 A B
2 B C
3 C D

The query that I used is :

Oracle: 
SELECT  level, main.table_name  parent, link.table_name child
FROM    user_constraints main, user_constraints link    
WHERE   main.constraint_type    IN ('P', 'U')
AND link.r_constraint_name  = main.constraint_name
START WITH main.table_name  LIKE UPPER('Your_table_name')
CONNECT BY main.table_name = PRIOR link.table_name
ORDER BY level, main.table_name, link.table_name;

I have queries for other databases like MySQL,DB2,SQL but I am not able to get all child tables and its descendants and I am getting only direct child tables. Could you please let me know how to modify these queries to get it working like Oracle query. Sorry I am not sure if this is the right forum to ask about queries.

SQL query:
Select object_name(rkeyid) [Master_Table_Name], object_name(fkeyid) [Child_Table_Name], SysCol.Name ForiegnKey_Column_Name, SysCol2.name Reference_Key_Column_Name From sys.sysforeignkeys SysFrKey
Inner join sys.syscolumns SysCol on (SysFrKey.fkeyid = SysCol.id And SysFrKey.fkey = SysCol.colid ) Inner join syscolumns SysCol2 on ( SysFrKey.rkeyid = SysCol2.id And SysFrKey.rkey = SysCol2.colid ) Order by Master_Table_Name,Child_Table_Name;

MYSQL:

SELECT 
  `TABLE_SCHEMA`,                          
  `TABLE_NAME`,                            
  `COLUMN_NAME`,                           
  `REFERENCED_TABLE_SCHEMA`,               
  `REFERENCED_TABLE_NAME`,                 
  `REFERENCED_COLUMN_NAME`                 
FROM
  `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE`  
WHERE
  `TABLE_SCHEMA` = SCHEMA()                 
  AND `REFERENCED_TABLE_NAME` IS NOT NULL;

DB2:

select  substr(R.reftabschema,1,12) as P_Schema, substr(R.reftabname,1,12) as PARENT,
        substr(R.tabschema,1,12) as C_Schema, substr (R.tabname,1,12) as CHILD,
        substr(R.constname,1,12) as CONSTNAME, 
        substr(LISTAGG(C.colname,', ') WITHIN GROUP (ORDER BY C.colname),1,20) as FKCOLS 
from syscat.references R, syscat.keycoluse C 
where R.constname = C.constname and R.tabschema = C.tabschema and R.tabname = C.tabname
group by R.reftabschema, R.reftabname, R.tabschema, R.tabname, R.constname;

[Roel De Nijs]

S Ganesh wrote:I guess that might be the alternate solution. I will opt for queries then. I have a doubt in queries. In oracle there is a query syntax where I can start with the specified table name and link with the child tables and all its descendants.

That's not the query I would write for the different databases. I would try to keep it much more simple and do something likeSELECT table_schema, table_name, table_column, ref_table_schema, ref_table_name, ref_table_column FROM table_with_key_info WHERE table_name IN ( SELECT table_name FROM table_with_table_info WHERE condition_to_select_all_tables_you_need_key_info_from )