Skip to main content

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
[eclipselink-dev] ddl-generation is slow if table already exits in DB and contains a lot of entries.

  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 ( 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 ( 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 ( which seems to have similar problem on H2DB.

Back to the top