Marten
05-21-2007, 01:38 AM
Hi,
I accidentally saw a document in the knowledge-gate about ModelManager performance. It appears that indexes on infos-columns didn't have the unique-attribute set, which should have been set for performance reasons. In new databases this attribute is set, but for upgrades, this doesn't get corrected: You have to do it manually.
I didn't see this in the modelmanager documentation (they even don't include an sql-script!), so I thought it was useful to post here.
This is from the knowledge-gate:
CoCreate R&D discovered that performance improved if the Oracle WM_ELEMENTS.INFOS column and the <class_name>.INFOS column had their index dropped, and if a new index was created on those columns which specified that they were unique. Apparently, the Oracle query optimizer is able to do a better job when it knows that the columns involved in a join are uniquely indexed, since that means there will never be more than a 1:1 relationship between records in the two tables.
WORKAROUND
CoCreate R&D changed the database installation process as of Model Manager 14.50 to uniquely index the INFOS columns on all new tables. They did not dare drop the indexes on existing customer databases, because they could not be sure of all the failure modes for index creation on a customer database. Customers can have their database administrator do that manually.
With king regards,
Marten Verhoeven
I accidentally saw a document in the knowledge-gate about ModelManager performance. It appears that indexes on infos-columns didn't have the unique-attribute set, which should have been set for performance reasons. In new databases this attribute is set, but for upgrades, this doesn't get corrected: You have to do it manually.
I didn't see this in the modelmanager documentation (they even don't include an sql-script!), so I thought it was useful to post here.
This is from the knowledge-gate:
CoCreate R&D discovered that performance improved if the Oracle WM_ELEMENTS.INFOS column and the <class_name>.INFOS column had their index dropped, and if a new index was created on those columns which specified that they were unique. Apparently, the Oracle query optimizer is able to do a better job when it knows that the columns involved in a join are uniquely indexed, since that means there will never be more than a 1:1 relationship between records in the two tables.
WORKAROUND
CoCreate R&D changed the database installation process as of Model Manager 14.50 to uniquely index the INFOS columns on all new tables. They did not dare drop the indexes on existing customer databases, because they could not be sure of all the failure modes for index creation on a customer database. Customers can have their database administrator do that manually.
With king regards,
Marten Verhoeven