• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

JDBC method to get child tables and all its descendants

 
S Ganesh
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:

 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 21559
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 21559
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 :



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;




 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 like
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic