[eclipselink-dev] ddl-generation is slow if table already exits in DB an

["Pavel Jancik" <pavel_jancik@xxxxxxxxxx>]

Hi,
  we have identified and issue
with DDL generation in EclipseLink.

If ddl-generation option is enabled
and the table(s) already exists in the database and the table(s) is large
(i.e., contains ten's of millions of entries)  the DDL generation
tooks long;
   the bigger the table is,
the longer DDL tooks.

We track the issue, and found that it
is caused by the SQL query used the check whether the table already exists
in DB or not.
  The query is created in the (org.eclipse.persistence.tools.schemaframework.)SchemaManager.checkTableExists(TableDefinition)
method (at line 470).
  It generates the queries in form
         SELECT
pk FROM table WHERE pk <> pk

We manually tried this type of queries
on DB2 and PostgreSql and their execution times are high and depends on
the number of entries in the table (i.e., forces full table scan).
   I currently do not have
access to (large enough) MySQL or Oracle database to verify this behavior
on DBs.

Since this SQL is used only to check
existence of the table in DB, I suggest to change the SQL to 
         SELECT
pk FROM table WHERE 0 = 1
   which behaves correctly
on DB2 and PostgeSQL -- it means it return immediately with empty result
if table exists or fails if table does not exists.

It means change the line 470  in
SchemaManager
         from
        String
sql= "SELECT "+ column+ " FROM "+ table.getFullName()
+ " WHERE "+ column+ " <> "+ column;
        to
    String sql= "SELECT "+ column+ " FROM "+ table.getFullName()
+ " WHERE 0 = 1";


Please, could some of the developers
EclipseLink 
  follow the process (https://wiki.eclipse.org/EclipseLink/Development/Process)
create a bug in bugzilla and fix the problem.


Thanks Pavel Jancik



Note1: To see this bug you need to have
to enable following options 
       
    <property name="eclipselink.ddl-generation"
value="create-tables" />
       
    <property name="eclipselink.ddl-generation.output-mode"
value="database" />

Note2: I have found a user ( https://www.eclipse.org/forums/index.php/t/1069268/)
which seems to have similar problem on H2DB.