This Bugzilla instance is a read-only archive of historic NetBeans bug reports. To report a bug in NetBeans please follow the project's instructions for reporting issues.
Summary: | Retrieving database schema too long | ||
---|---|---|---|
Product: | javaee | Reporter: | kovica <kovica> |
Component: | Persistence | Assignee: | Andrei Badea <abadea> |
Status: | REOPENED --- | ||
Severity: | blocker | CC: | pbuzek |
Priority: | P1 | Keywords: | PERFORMANCE |
Version: | 5.x | ||
Hardware: | PC | ||
OS: | Windows 10 | ||
Issue Type: | ENHANCEMENT | Exception Reporter: | |
Attachments: |
messages.log
thread dump Thread dump with JCC driver Messages with JCC |
Description
kovica
2006-05-08 09:35:21 UTC
It's been over two hours now and I'm still seeing that "Retrieving the database schema" dialog. The approach used here (reading the complete db schema and then let the user select from the list, probably inherited from Forte days), does not scale well with large databases. The right approach would be to do a very superficial scan of the db schema (select * from user_table in oracle), let the user select the tables, then try to identify the relationships between the selected tables (like the References feature in DbVisualizer). The second point is to improve the used XML format to drastically reduce the file size. The current format is ridiculously verbose (and get rid of the Forte package names in there). Best regards, Georg I agree it is a bug that you do not see that the wizard is doing anything and what it is doing and that you cannot cancel what it is doing. It would be good to get that fixed. If nothing else then by saying "Stop Reading!!". I am not sure if we will be able to make it perform well for 2000 tables and I do not think that it is absolutely required for this release. It is not an imposible use case but IMO it is kind of infrequent to have 2000 tables - at least for most users. Anyway, how quick would you expect it to be for 200 tables? Seriously, I am asking what would be acceptable for you, because I have never worked with a 2000 tables DB myself. The reason why it needs to read more then just the list of tables is that it helps you to select related tables. This may not be always needed (and you can turn it off) but we think that most users will like it (it is the default). I agree that the dbschema is not ideal. FYI, Andrei started working on its future replacement, but it was not a priority for this release. The priority was to get the job done as quickly as possible (i.e. reusing what we had) to have nb 5.5 as close as possible to ee5 spec/reference implemenation release. thanks for testing and thanks for feedback & ideas! -pavel How about if, for a temporary solution, yoiu put a System.out.println somewhere in there, so I can see what table is it reading? Georg and Pavle, thank you for your comments. I answered Georg in issue 75687, I suggest we continue the discussion about how to improve dbschema there. Gregor, could you please run NetBeans *from a console* and with the -J-Dnetbeans.debug.exceptions=true option, reproduce the error (it is enough to wait for a few minutes I think) and attach the exceptions in the console? Could you also check if there is any network activity while the "Retrieving the schema" dialog is displayed? Please make sure you attach the console contents and not the message log, since the logging in dbschema uses System.out.println() instead of proper logging statements. Probably I could add a "Cancel" button to the progress dialog, although it won't work in all cases. If the driver is receiving data from the server the schema retrieving process can be interrupted. But if the driver is blocked waiting from data from the server it is not possible to reliably interrupt it, so the dialog won't be closed until the call into the driver returns. I didn't get any errors in console, but there are some in messages.log file. I'm ataching that. Created attachment 30420 [details]
messages.log
Is issue 76380 a duplicate of this? By the way, the exceptions in the attached messages.log that refer to not finding sun-cmp-mappings.xml are from the SJSAS plugin and are not related to this issue. I filed issue 76455 to deal with those, as while they are probably benign, they should not have happened. I've run this with the COM.ibm.db2.jdbc.app.DB2Driver driver and it did return all the tables, but they are all listed with a comment saying "no primary key". I'm 100 % sure they do have a primary key. I also cannot select any table from the list, they are all grayed out. I'm not sure about the time it took to read the tables, since I waited for couple of minutes and then went home. raccah: no, but it is a duplicate of issue 75687. Thanks for notifying. The exception is the same that you reproduced in issue 76061, so let's deal with it there. However, the progress dialog should have disappeared and an error message displayed. I will look at it. Another question is why the primary keys weren't retrieved using the APP driver. Were there any exception or anything ususual? Please try to browse the database in the DB Explorer. Are the primary keys displayed as such (they have a red-colored column icon)? I didn't see any exceptions, but I'll check. I can see the primary keys in Database Explorer. Unfortunately I don't have the possibility to test with the APP driver. I can only test with the NET one (and JCC). Could you please try if you get the same behavior with the NET driver that you get with the APP driver (that is, the primary keys are not detected in the Entity Classses from DB wizard, but they are displayed correctly in the DB Explorer)? Thanks. BTW with the NET driver the primary keys are detected correctly for me in the wizard. Would it be possible to post a SQL script to create one of your tables so that I can test it here? I tried to "generate" some exceptions while the schema is retrieved hoping I would get a forever-runnning progress dialog, but didn't manage to. I don't know why the progress ran for so long, maybe the driver is waiting for data from the database server. Could you please to reproduce again with the JCC driver, let the progress dialog run for a few minutes and then generate a full JVM thread dump and attach it to the issue? Thanks. Now I'm pretty sure why NB 5.5 got out during the night. When I started it this morning the dialog "Retrieving the database schema" was up for 8 hours and then I killed it. During the night a backup is made of the database and all connections to the database are closed. So... NB ran to midnight (database backup time), the conenction was closed and then the dialog dissapered. The tables were listed in the list box, but because the process of getting the schema did not finish there were not all primary keys listed. I hope I'm making sense. Steps that I think you are doing: connect to database list tables = get list of tables for tableName in list do get primary key for tableName end for So the conenction was closed when the process was in the for loop meaning not all primary keys were read from database. Created attachment 30500 [details]
thread dump
Attached thread dump is atfer the dialog ran for about 30 minutes and I'm using APP driver. With JCC I get exception: [org.netbeans.modules.db.explorer.DatabaseConnection] connect() [org.netbeans.modules.db.runtime.DatabaseRuntimeManager] Runtime: org.netbeans.modules.derby.RegisterDerby for driver org.apache.derby.jdbc.ClientDr iver [org.netbeans.modules.db.runtime.DatabaseRuntimeManager] Runtime: org.netbeans.modules.j2ee.sun.ide.j2ee.db.RegisterPointbase for driver com.pointba se.jdbc.jdbcUniversalDriver [org.netbeans.modules.db.explorer] Creating DbURLClassLoader for [file:/home/gregor/LIB/DB2/8.2/db2jcc.jar, file:/home/gregor/LIB/DB2/8.2/db2jcc_jav ax.jar, file:/home/gregor/LIB/DB2/8.2/db2jcc_license_cu.jar] You are trying to access file: DB2JccConfiguration.properties from the default package. Please see http://www.netbeans.org/download/dev/javadoc/org-openide-modules/org/openide/modules/doc-files/classpath.html#default_package [org.netbeans.modules.db.explorer.DatabaseConnection] connect() [org.netbeans.modules.db.runtime.DatabaseRuntimeManager] Runtime: org.netbeans.modules.derby.RegisterDerby for driver org.apache.derby.jdbc.ClientDr iver [org.netbeans.modules.db.runtime.DatabaseRuntimeManager] Runtime: org.netbeans.modules.j2ee.sun.ide.j2ee.db.RegisterPointbase for driver com.pointba se.jdbc.jdbcUniversalDriver [org.netbeans.modules.db.explorer] Creating DbURLClassLoader for [file:/home/gregor/LIB/DB2/8.2/db2jcc.jar, file:/home/gregor/LIB/DB2/8.2/db2jcc_jav ax.jar, file:/home/gregor/LIB/DB2/8.2/db2jcc_license_cu.jar] You are trying to access file: DB2JccConfiguration.properties from the default package. Please see http://www.netbeans.org/download/dev/javadoc/org-openide-modules/org/openide/modules/doc-files/classpath.html#default_package Warning: use of system property netbeans.debug.exceptions in org.netbeans.modules.dbschema.jdbcimpl.SchemaElementImpl has been obsoleted in favor of ErrorManager com.ibm.db2.jcc.a.SqlException at com.ibm.db2.jcc.a.s.a(s.java:1520) at com.ibm.db2.jcc.a.s.m(s.java:465) at com.ibm.db2.jcc.a.s.M(s.java:1102) at com.ibm.db2.jcc.a.jf.getString(jf.java:901) at org.netbeans.lib.ddl.impl.DriverSpecification.getRow(DriverSpecification.java:198) at org.netbeans.modules.dbschema.jdbcimpl.SchemaElementImpl.initTables(SchemaElementImpl.java:218) at org.netbeans.modules.dbschema.jdbcimpl.SchemaElementImpl.initTables(SchemaElementImpl.java:180) at org.netbeans.modules.j2ee.persistence.wizard.fromdb.DBSchemaManager$3.run(DBSchemaManager.java:125) at org.netbeans.modules.j2ee.persistence.util.EventRequestProcessor.invokeActionsUntilThreadSwitch(EventRequestProcessor.java:160) at org.netbeans.modules.j2ee.persistence.util.EventRequestProcessor.access$000(EventRequestProcessor.java:42) at org.netbeans.modules.j2ee.persistence.util.EventRequestProcessor$1.run(EventRequestProcessor.java:97) at org.openide.util.RequestProcessor$Task.run(RequestProcessor.java:493) at org.openide.util.RequestProcessor$Processor.run(RequestProcessor.java:926) Thanks for the explanation about the connection being closed at midnight. It could be an explanation. Also thanks for the thread dump, but it should have been with the JCC driver, since that driver throws an exception and I wanted to know if the exception causes the progress dialog to stay opened forever. Could you please send another one with the JCC driver? I modified the progress dialog to show a determinate progress bar (it shows the percent of the job that is done) and the names of the retrieved tables. I will commit it tomorrow. Just a comment: I've seen in your code that you get info about the database and tables via DatabaseMetadata and alike. I must point that, at least with DB2, this approach is VERY slow. We had done this in our application as weel, but then we switched to using direct SELECT statements againt the catalog. I know that you must support several different databases and the DatabaseMetadata is the only standard approach for this. I know that DbVisualizer is much faster reading info about the database and tables. I guess they are using SELECTs too. I'd recommend it using them here too. I'd be willing to help too. And the performance will be at least 10x better. Believe me. I've been there, done that. :)) With JCC is like this that when I create a connection and enter username and password the connection is made, the dialog only appears for a second and dissapears then. I'm attaching messages.log and thread dump. Created attachment 30516 [details]
Thread dump with JCC driver
Created attachment 30517 [details]
Messages with JCC
Displaying the retrieved tables and views in the progress dialog. Checking in src/org/netbeans/modules/j2ee/persistence/util/EventRequestProcessor.java; /cvs/j2ee/persistence/src/org/netbeans/modules/j2ee/persistence/util/Attic/EventRequestProcessor.java,v <-- EventRequestProcessor.java new revision: 1.1.2.3; previous revision: 1.1.2.2 done Checking in src/org/netbeans/modules/j2ee/persistence/util/ProgressPanel.java; /cvs/j2ee/persistence/src/org/netbeans/modules/j2ee/persistence/util/Attic/ProgressPanel.java,v <-- ProgressPanel.javanew revision: 1.1.2.3; previous revision: 1.1.2.2 done Checking in src/org/netbeans/modules/j2ee/persistence/wizard/fromdb/Bundle.properties; /cvs/j2ee/persistence/src/org/netbeans/modules/j2ee/persistence/wizard/fromdb/Attic/Bundle.properties,v <-- Bundle.properties new revision: 1.1.2.3.2.10; previous revision: 1.1.2.3.2.9 done Checking in src/org/netbeans/modules/j2ee/persistence/wizard/fromdb/DBSchemaManager.java; /cvs/j2ee/persistence/src/org/netbeans/modules/j2ee/persistence/wizard/fromdb/Attic/DBSchemaManager.java,v <-- DBSchemaManager.java new revision: 1.1.2.3; previous revision: 1.1.2.2 done Checking in test/unit/src/org/netbeans/modules/j2ee/persistence/util/EventRequestProcessorTest.java; /cvs/j2ee/persistence/test/unit/src/org/netbeans/modules/j2ee/persistence/util/Attic/EventRequestProcessorTest.java,v <-- EventRequestProcessorTest.java new revision: 1.1.2.3; previous revision: 1.1.2.2 done Thanks for the suggestion to query the system catalog instead of using DatabaseMetaData. Unfortunately I don't think this will be possible to 5.5, but I will take it into account in the dbschema replacement. Please feel free to file an enhancement issue for it. I will also try to add a cancel button to the progress dialog. When that is done I think the issue can be closed. I've tried the new build and it did work. It lasted for almost two hours that the data was read from the database, but at least I could see where the process is at. This process would be over in 10 seconds if direct SELECT statements would be used. :) The .dbschema file that was generated is over 115 MB large. :)) Added a button to cancel the schema retrieval. Gregor, if you have time please test it. Also please test the schema retrieval speed again. On May 19 I managed to commit a debugging Thread.sleep() call which made everything even slower. Please try with the daily build from May 25 or later. I don't think I can do more in this issue, thus closing. Checking in db/model/src/org/netbeans/modules/dbschema/jdbcimpl/SchemaElementImpl.java; /cvs/db/model/src/org/netbeans/modules/dbschema/jdbcimpl/SchemaElementImpl.java,v <-- SchemaElementImpl.java new revision: 1.3.6.1.2.2; previous revision: 1.3.6.1.2.1 done Checking in j2ee/persistence/src/org/netbeans/modules/j2ee/persistence/util/EventRequestProcessor.java; /cvs/j2ee/persistence/src/org/netbeans/modules/j2ee/persistence/util/Attic/EventRequestProcessor.java,v <-- EventRequestProcessor.java new revision: 1.1.2.5; previous revision: 1.1.2.4 done Checking in j2ee/persistence/src/org/netbeans/modules/j2ee/persistence/util/ProgressPanel.form; /cvs/j2ee/persistence/src/org/netbeans/modules/j2ee/persistence/util/Attic/ProgressPanel.form,v <-- ProgressPanel.form new revision: 1.1.2.2; previous revision: 1.1.2.1 done Checking in j2ee/persistence/src/org/netbeans/modules/j2ee/persistence/util/ProgressPanel.java; /cvs/j2ee/persistence/src/org/netbeans/modules/j2ee/persistence/util/Attic/ProgressPanel.java,v <-- ProgressPanel.java new revision: 1.1.2.4; previous revision: 1.1.2.3 done Checking in j2ee/persistence/src/org/netbeans/modules/j2ee/persistence/wizard/fromdb/DBSchemaManager.java; /cvs/j2ee/persistence/src/org/netbeans/modules/j2ee/persistence/wizard/fromdb/Attic/DBSchemaManager.java,v <-- DBSchemaManager.java new revision: 1.1.2.5; previous revision: 1.1.2.4 done Checking in j2ee/persistence/test/unit/src/org/netbeans/modules/j2ee/persistence/util/EventRequestProcessorTest.java; /cvs/j2ee/persistence/test/unit/src/org/netbeans/modules/j2ee/persistence/util/Attic/EventRequestProcessorTest.java,v <-- EventRequestProcessorTest.java new revision: 1.1.2.5; previous revision: 1.1.2.4 done Reading the schema seems a bit faster now, but not much. I've tested the Cancel button today and it seems to work. But I don't like it behaviour. When pressing Cancel button the list should be populated with tables that were already read. I stand corrected. Reading of database schema took about 20 minutes. Great improvement. This issue is still not fixed in version 8.1 I would suggest to just retrieve the name of the tables in the first step. Once the user select the required tables, in the next step retrieve the schema details only for the selected tables, instead of retrieve it for all the tables which could be in hundreds. 10 years it is. |