Bug 75687 - dbschema generation is slow and creates huge output file.
dbschema generation is slow and creates huge output file.
Status: NEW
Product: db
Classification: Unclassified
Component: DB schema
5.x
PC Windows XP
: P2 (vote)
: TBD
Assigned To: Libor Fischmeistr
issues@db
: PERFORMANCE
: 76317 76380 (view as bug list)
Depends on:
Blocks:
  Show dependency treegraph
 
Reported: 2006-04-28 12:58 UTC by giorgio42
Modified: 2013-09-04 08:22 UTC (History)
2 users (show)

See Also:
Issue Type: ENHANCEMENT
:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description giorgio42 2006-04-28 12:58:04 UTC
[5.5q2 WinXPSP2, Mustang b77, Oracle 8.1.7, JDBC driver 10.x (newest). Pentium
M, 1.8 GHz, 1 GB RAM]

Generating the dbschema for a medium-sized Oracle schema containing about 70
tables takes 1-2 minutes. The resulting dbschema file is almost 10 MB in size
and contains more than 145000 (!!) lines.

Looking at the dbschema file one notices
o that the XML used is extremely verbose.
o that the file includes not only the user tables but also Oracle system tables
(333 tables in total). When creating "Entity classes from dbschema" the user is
usually not interested in internal database tables. Actually I wanted to create
an entity class for a single table in that schema...

Please make dbschema extraction more lightweight.

Thanks,
Georg
Comment 1 Andrei Badea 2006-05-16 13:47:16 UTC
*** Issue 76317 has been marked as a duplicate of this issue. ***
Comment 2 Andrei Badea 2006-05-16 15:00:24 UTC
Re. verbose XML: I definitely agree, but I'm afraid we won't be able to do
anything about it in 5.5 (except perhaps for giving the option not to serialize
the read schema to disk in the Entity Classes from Database wizard). As Pavel
mentioned in issue 76063 I have already started working on a replacement
(tracked by issue 61441), but it is not ready yet.

Re. system tables: I can reproduce, I will fix it.
Comment 3 giorgio42 2006-05-16 20:14:51 UTC
Thanks for listening to my complaints!

Actually I anticipated something like this. It is clear that the entity from
dbschema and JSF pages from entity class are still in their infancy.

I sincerely hope your plate is not too full and you have time to make them grow up! 

Cheers,
Georg
Comment 4 giorgio42 2006-05-16 20:15:17 UTC
Thanks for listening to my complaints!

Actually I anticipated something like this. It is clear that the entity from
dbschema and JSF pages from entity class are still in their infancy.

I sincerely hope your plate is not too full and you have time to make them grow up! 

Cheers,
Georg
Comment 5 giorgio42 2006-05-16 20:16:16 UTC
Thanks for listening to my complaints!

Actually I anticipated something like this. It is clear that the entity from
dbschema and JSF pages from entity class are still in their infancy.

I sincerely hope your plate is not too full and you have time to make them grow up! 

Cheers,
Georg
Comment 6 giorgio42 2006-05-16 20:18:15 UTC
Sorry for posting the same entry 3 times.
Actually sometimes a completely different issue is displayed after submit and I
don't whether the comment has been posted or not.

Georg
Comment 7 Andrei Badea 2006-05-17 13:55:58 UTC
*** Issue 76380 has been marked as a duplicate of this issue. ***
Comment 8 Andrei Badea 2006-05-17 14:00:11 UTC
Fixed the system tables problem.

Checking in
src/org/netbeans/modules/j2ee/persistence/editor/completion/db/Schema.java;
/cvs/j2ee/persistence/src/org/netbeans/modules/j2ee/persistence/editor/completion/db/Attic/Schema.java,v
 <--  Schema.java
new revision: 1.1.4.4; previous revision: 1.1.4.3
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.2; previous revision: 1.1.2.1
done
Comment 9 Andrei Badea 2006-05-17 14:30:07 UTC
Georg, please let me know if the fix made the schema retrieval process faster. I
tried it here on Oracle with a few tables and it seems to me Oracle is just slow
at returning the table's keys or indexes. The best way to fix this in NetBeans
is to retrieve only the table names in the first step, as you suggested in issue
76063. But having the table keys available allows us to select the related
tables "online" and display them to the user. I agree with Pavel that this is an
enhancement over the CMP from DB wizard in 5.0 and we think users will like it
(users on other databases that Oracle that is :-)).

Another "fix" would be to retrieve only the table names when the Include Related
Tables checkbox is not selected. But:

- the user has to know (s)he has to deselect the checkbox before selecting the
data source or database connection; it is not obvious.

- what is worse, deselecting the checkbox may not be what you want, it will
leave out related tables completely.

So maybe it's actually not worth doing it.

Regarding the verbose dbschema XML format, since it can't be fixed in 5.5 I
suggest I file an enhancement issue for it (or turn this issue into an
enhancement when everything else is fixed).
Comment 10 Andrei Badea 2006-07-07 11:48:13 UTC
The is one more thing I wanted to try: avoiding the driver executing the ANALYZE
TABLE statement when the indexes are retrieved by passing true as the
approximate parameter of getIndexInfo(). I did that in issue 77312 and the fixed
module is attached there. Please test if it improves the speed of the keys
retrieval.

This is the last thing I can do for improving the speed of the dbschema
retrieval in 5.5. But this is still a valid issue, and the size of the dbschema
file too, so turning it into an enhancement.
Comment 11 giorgio42 2006-07-07 21:35:15 UTC
Andrei, thanks!

I will check as soon I am back in the office on Monday...

Cheers,
Georg
Comment 12 giorgio42 2006-07-17 16:23:08 UTC
Andrei,

it took me some time to get back to this issue, but anyway I verified, that
o only user tables are now taken into account.
o dbschema generation is now much faster.
o the progress indicator is very useful.

The bad news is: Although almost all tables in this schema have a primary key,
only very few are displayed as selectable, all others are marked as having no
primary key. My guess is that getIndexInfo() with Oracle 8.1.7 gives you only
secondary indexes, but no primary keys (the selectable tables actually have
secondary indexes).

Therefore, in its current implementation, Entity from Classes is unusable in my
environment (sorry for that). Not sure which method in the JDBC driver would be
the correct one.

From my testing I have the impression that MySQL does not suffer from this problem.


Cheers,
Georg
Comment 13 Andrei Badea 2006-07-17 18:00:07 UTC
Georg, thank you for your time!

Bad news indeed. You're not the first to report that the primary keys are not
retrieved. I would very much like to fix this bug, but I've never (ever!) seen
it in my testing configuration.

If you have time I could provide a dbschema module with some more logging, which
could help us track the cause. If not, then please at least have a look at issue
77312 and see if granting/revoking ANALYZE ALL affects the behavior for you. If
you do this please also run NetBeans with -J-Dnetbeans.debug.exceptions=true and
attach any interesting exceptions in the message log or console. By the way:

- is the foreign keys (non-)retrieval a regression for you, or has it always
behaved like this? 

- do you see the primary keys in the Database Explorer?

- have you tried the 200607170000 daily build? It contains the fix of issue
56492, which could help in case no index is returned, but the primary key is.

Thanks again for your help and thanks for the good news too!
Comment 14 giorgio42 2006-07-24 16:01:37 UTC
Andrei,

answering your three questions:
o Is foreign key non-retrieval a regression? Not sure, because until recently I
always used a development database for testing, where I have more privileges.
o Primary Keys in DB Explorer? No, I get an Unable to read the database
structure, connection is broken; ORA-01013: Insufficient Priviledges.
o Using NB55 beta2 (hopefully based on an after 17.07. release), no primary keys
are retrieved when I use the read-only account.

If you want to reproduce the problem, let your DBA give you an read-only account
on some Oracle schema. I googled a bit and found messages on Oracle-related
message boards, that point to the same problem with "analyze all" as early as
2001 (one was from Thomas Kellerer, when he was writing his SQL Workbench [my
guess, though], you probably saw his name on the nbusers list). It seems the
problem still persists in latest Oracle JDBC drivers. No comment...

The Entity from Database and JSF Page from Entity pair is now generating useful
applications, but there are still quite a few quirks, which will lead to further
 bug reports.

Cheers and thanks for all your efforts,
Georg

Comment 15 Andrei Badea 2006-07-24 16:42:23 UTC
I might have good new for you. First of all, as you can see in the similar issue
77312, I managed to reproduce the keys not being retrieved. Just as you said, it
happened when the user was connecting to a schema it was not the owner of. 

The keys will be displayed now as a side-effect of the fix of issue 56492, but
note this fix is *not* included in beta 2.
Comment 16 Jiri Rechtacek 2009-10-16 14:11:30 UTC
Reassigned to new owner.


By use of this website, you agree to the NetBeans Policies and Terms of Use. © 2014, Oracle Corporation and/or its affiliates. Sponsored by Oracle logo