Clarification on indexing

If you have questions or if you want to share your opinion about Aware IM post your message on this forum
Post Reply
ask180
Posts: 160
Joined: Thu Oct 04, 2012 11:40 pm

Clarification on indexing

Post by ask180 »

Hi,

Assume I have two business objects, A and B, in a one-to-many relationship (ie: one instance of A can relate to multiple instances of B). The relationship was set up by creating an attribute on B of type A. All basic stuff.

If A has 1 million rows, and B has 5 million rows, I want the attribute on B that points to A to be indexed, so that I can quickly retrieve all B rows that relate to a selected A instance. Again, normal administrative system behaviour.

The problem is that I can't see where in the configuration tool to set the attribute on B that points to A (a foreign key basically) to be indexed. For other types of attributes, there is a check box called 'Indexed', but I can't find it for this one.

Do I have to go into the database and manually create an index via SQL?

Any advice would be appreciated.
BobK
Posts: 544
Joined: Thu Jan 31, 2008 2:14 pm
Location: Cincinnati, Ohio, USA

Re: Clarification on indexing

Post by BobK »

ask180 wrote: Tue Nov 02, 2021 7:52 pm The problem is that I can't see where in the configuration tool to set the attribute on B that points to A (a foreign key basically) to be indexed. For other types of attributes, there is a check box called 'Indexed', but I can't find it for this one.
AwareIM automatically creates and maintains the necessary attributes for all relationship. Since there should not be any reason for the user to touch these attributes, they do not show up in the config tool

ask180 wrote: Tue Nov 02, 2021 7:52 pm Do I have to go into the database and manually create an index via SQL?
No, you do not need to create any index for relationships. AwareIM creates and maintains the indexes for all relationships.

Having said the above, there is one issue you should be aware of.

Depending how you create the relationship, AwareIM may either
1) create a reference table called A_ref that is used to connect table A with table B (using your example tables A and B)
2) add 3 new attributes to table B. These attributes end in _REN, _RID and _RMA and point back to table A

I believe the consensus of users is that 2 above is more efficient. There is a posting somewhere on this forum that goes into more details about how to control which method AwareIM uses.
Bob
PointsWell
Posts: 1457
Joined: Tue Jan 24, 2017 5:51 am
Location: 'Stralya

Re: Clarification on indexing

Post by PointsWell »

BobK wrote: Wed Nov 03, 2021 2:44 pm I believe the consensus of users is that 2 above is more efficient. There is a posting somewhere on this forum that goes into more details about how to control which method AwareIM uses.
Always add the reference from the singular side. Obviously does not work on m-n relationships or simple multiple lists

For example: Companies have Employees, but an Employee only works at one Company, create Employee.psEmployer will result in the _REN, _RID and _RMA to be on the Employee table. If you alternatively create Company.pmEmployees first as a multiple attribute then you will end up with the Company_ref table.
Jaymer
Posts: 2430
Joined: Tue Jan 13, 2015 10:58 am
Location: Tampa, FL
Contact:

Re: Clarification on indexing

Post by Jaymer »

HERE IS the discussion BobK mentioned about Ref tables and relationships.
Click Here to see a collection of my tips & hacks on this forum. Or search for "JaymerTip" in the search bar at the top.

Jaymer
Aware Programming & Consulting - Tampa FL
ask180
Posts: 160
Joined: Thu Oct 04, 2012 11:40 pm

Re: Clarification on indexing

Post by ask180 »

Guys,

Thanks for the clarification. I will proceed with confidence that performance won't be an issue.
Post Reply