Toad World® Forums

Poor Performance on Join Suggestions since Toad 15

Hi Guys,

maybe a bit of a duplicate topic, but I'm not sure if it is really tackled (e.g. this Post). With TOAD 15 the Query for the Join-Suggestions has been changed considerably.

As per the resolved issues in this release the query has been changed according tho this Post. The now newly executed query takes up to a few minutes to complete, regardless if it is on our production or test-systems.

Also with renewed data-dictonary and fixed-object statistics the "old" way performs significantly better than the new one. To reproduce the problem I simply need to run the below queries, which are executed during the population of the pick-list for the join-suggestions.

Our current workaround is to deactivate the Join-Suggestions in the "Code Insight" Options. This remedies the situation immediatly but we have to do without the suggestions.

Does anybody have the same experience with these queries? Is there a way to improve the performance of this feature and not turning it off?

-- TOAD 15.0.97.1178 -- sql_id: amgj7m00rusm6
  SELECT 'REF_INTEGRITY'     AS TYPE,
         rel.*,
         cc1.column_name     pk_column,
         cc2.column_name     fk_column,
         cc1.position        column_position
    FROM (SELECT c1.owner                 AS parent_owner,
                 c1.table_name            AS parent_table,
                 c2.r_constraint_name     AS pk_constraint,
                 c2.owner                 AS child_owner,
                 c2.table_name            AS child_table,
                 c2.constraint_name       AS fk_constraint
            FROM sys.DBA_CONSTRAINTS c1, sys.DBA_CONSTRAINTS c2
           WHERE     (   (c1.table_name = :name AND c1.owner = :owner)
                      OR (c2.table_name = :name AND c2.owner = :owner))
                 AND c1.constraint_type IN ('P', 'U')
                 AND c2.constraint_type = 'R'
                 AND c2.r_constraint_name = c1.constraint_name
                 AND c2.r_owner = c1.owner) rel,
         sys.DBA_CONS_COLUMNS cc1,
         sys.DBA_CONS_COLUMNS cc2
   WHERE     cc1.owner = rel.parent_owner
         AND cc1.constraint_name = rel.pk_constraint
         AND cc1.table_name = rel.parent_table
         AND cc2.owner = rel.child_owner
         AND cc2.table_name = rel.child_table
         AND cc2.constraint_name = rel.fk_constraint
         AND cc1.position = cc2.position
ORDER BY 4, 10 
-- TOAD 14.2.104.1069 sql_id: 5whrm028c22yq 
Select 'REF_INTEGRITY' as type,
         rel.parent_owner,
         rel.parent_table,
         rel.pk_constraint,
         rel.child_owner,
         rel.child_table,
         rel.fk_constraint,
         cc1.column_name pk_column,
         cc2.column_name fk_column,
         cc1.position column_position
    FROM (Select c1.owner AS parent_owner,
                 c1.table_name AS parent_table,
                 c2.r_constraint_name AS pk_constraint,
                 c2.owner AS child_owner,
                 c2.table_name AS child_table,
                 c2.constraint_name AS fk_constraint
            FROM sys.DBA_CONSTRAINTS c1, sys.DBA_CONSTRAINTS c2
           WHERE     c1.table_name = :name
                 AND c1.owner = :owner
                 AND c1.constraint_type IN ('P', 'U')
                 AND c2.constraint_type = 'R'
                 AND c2.r_constraint_name = c1.constraint_name
                 AND c2.r_owner = c1.owner) rel,
         sys.DBA_CONS_COLUMNS cc1,
         sys.DBA_CONS_COLUMNS cc2
   WHERE     cc1.owner = rel.parent_owner
         AND cc1.constraint_name = rel.pk_constraint
         AND cc2.owner = rel.child_owner
         AND cc2.constraint_name = rel.fk_constraint
         AND cc1.position = cc2.position
UNION ALL
  Select 'REF_INTEGRITY' as type,
         rel.parent_owner,
         rel.parent_table,
         rel.pk_constraint,
         rel.child_owner,
         rel.child_table,
         rel.fk_constraint,
         cc1.column_name pk_column,
         cc2.column_name fk_column,
         cc1.position column_position
    FROM (Select c1.owner AS parent_owner,
                 c1.table_name AS parent_table,
                 c2.r_constraint_name AS pk_constraint,
                 c2.owner AS child_owner,
                 c2.table_name AS child_table,
                 c2.constraint_name AS fk_constraint
            FROM sys.DBA_CONSTRAINTS c1, sys.DBA_CONSTRAINTS c2
           WHERE     c2.table_name = :name
                 AND c2.owner = :owner
                 AND c1.constraint_type IN ('P', 'U')
                 AND c2.constraint_type = 'R'
                 AND c2.r_constraint_name = c1.constraint_name
                 AND c2.r_owner = c1.owner) rel,
         sys.DBA_CONS_COLUMNS cc1,
         sys.DBA_CONS_COLUMNS cc2
   WHERE     cc1.owner = rel.parent_owner
         AND cc1.constraint_name = rel.pk_constraint
         AND cc2.owner = rel.child_owner
         AND cc2.constraint_name = rel.fk_constraint
         AND cc1.position = cc2.position
ORDER BY 4, 10 

Thanks :slight_smile:

Hi Rudolf,

Thanks for the feedback. To be honest, I wasn't 100% sure that the query would be an improvement (but it sure looks nicer). For me, it performed about the same.

What can you tell me about your database? Which version is it? Does it have a lot of objects? A lot of schemas? A lot of referential integrity?

I tested the change on a lot of different Oracle versions, so I am curious about your scenario to see if I can duplicate it. If so, we can go back to the old query.

Hello John,

thanks for your reply!

We are running Oracle EE 19c. Besides the Oracle provided tables, we are maintaining nearly 1.500 tables distributed over about 20 schemas. We also heavily depend on referential integrity (~1.200 R-constraints).

We are considering making a change such that we load all foreign keys in one go, rather than every time the dialog opens. We don't think it will take much memory, even with a lot of foreign keys. The query is faster and we'd only need to execute it one time. Please try query in the editor, running it with f9, and let me know how long it takes to run.

Thanks.

with ref_cons as
     (select c.owner, c.constraint_name, c.table_name,
             c.r_owner, c.r_constraint_name,
             cc.position, cc.column_name
      from  dba_constraints c, dba_cons_columns cc
      where c.constraint_type = 'R'
      and   c.owner = cc.owner
      and   c.constraint_name = cc.constraint_name),
     pk_cons as 
      (select c.owner, c.constraint_name, c.table_name,
              cc.position, cc.column_name
       from  dba_constraints c, dba_cons_columns cc
       where c.constraint_type in ('P', 'U')
       and   c.owner = cc.owner
       and   c.constraint_name = cc.constraint_name)
select ref_cons.owner, 
       ref_cons.table_name,
       ref_cons.column_name,
       ref_cons.r_owner, 
       pk_cons.table_name as r_table_name,
       pk_cons.column_name as r_column_name
from pk_cons, ref_cons
where ref_cons.r_owner = pk_cons.owner
and   ref_cons.r_constraint_name = pk_cons.constraint_name
and   ref_cons.position = pk_cons.position
order by ref_cons.owner, ref_cons.table_name, ref_cons.constraint_name, ref_cons.position

@rudolf.bittinger - The change @JohnDorlon identified and mentioned is in place for Toad 15.1 and will be present in next week's beta. I have an older XE database where the previous query was pretty slow and the new changes are night and day. It's much faster now.

1 Like

@JohnDorlon, your suggested query is way better than the currently implemented. On our dev system it finishes within 4 sec in an production environment it returns in about 2 sec. I think this would boost the current performance of the code insight popup significantly.

@mstaszew , I'm looking forward to try it in the next beta :-). I come back with results from this test

1 Like

The new version performs super fast now. Thanks for your efforts!

It here a plan when the new release is made publicly available?

1 Like

Yes. I believe it will be before the end of the month. I'm not sure the exact date.

1 Like