Child pages
  • EOF-Using EOF-EOF Performance Tuning

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

The SQL that EOModeler generates for many to many join tables does not allow full optimization of the join. Many to many join tables are only generated with a single index which is the compound key of both columns.

For example,

CREATE TABLE
Panel
Code Block
sql
sql

  CREATE TABLE "GROUP_FILE" (


    "FILE_PKEY" NUMERIC NOT NULL,


    "GROUP_PKEY" NUMERIC NOT NULL


  );
ALTER TABLE

  
  ALTER TABLE "GROUP_FILE"


    ADD PRIMARY
KEY
 KEY
      ("GROUP_PKEY","FILE_PKEY")


    NOT DEFERRABLE INITIALLY IMMEDIATE;

This index can only be used to optimize joins on the first column in the index (which is the second column in the table for some reason). The lack of an index for the other column results in a table scan when joining on the other column. This sort of joins happen when following a to many relationship. For example, group.files() will result in SQL similar to this for each element in files():

SELECT
Panel
Code Block
sql
sql

  SELECT f.*

 
  FROM FILE f,

GROUP g,
 
       GROUP g,
       GROUP_FILE gf

WHERE

  WHERE
        f.PKEY = gf.FILE_PKEY
AND
 AND
        g.PKEY = gf.GROUP_PKEY
AND
 AND
        g.PKEY = 10;

Only the GROUP_PKEY has an index which is usable for optimizing this query. The join on FILE_PKEY (f.PKEY = gf.FILE_PKEY) is not optimized and results in table scans. You need to manually add an index for this:

CREATE INDEX
Panel
Code Block
sql
sql

  CREATE INDEX GROUP_FILE_FILE_PKEY

ON

       ON GROUP_FILE (FILE_PKEY);

This can make a truly dramatic difference if there are a lot of rows in the join table.

...