Optional indexed link table structure for heavily used linked fields
Contents
If you have records with hundreds or thousands of reverse or hierarchical links to other records, like a loan record with many links to object records, or archive records with many child records for example, then opening, editing and saving such a record can take a very long time. To solve that problem, an optional SQL table structure has been designed, especially for heavily used linked fields, which can be implemented from Designer 7.7.3. If implemented in your database tables this will lead to an enormous improvement in performance when it comes to processing such records: a requirement for this performance improvement is that such linked field groups - for this functionality it is a requirement that fields on both sides of the link are part of a data dictionary field group - are displayed in so-called table grids (via the Edit link group on demand setting for a field group box on a screen), because only then is it possible for Collections to resolve only the visible part of the displayed list instead of the whole list that needs to be resolved when field group occurrences must be displayed in normal fields.
Note that importing data via Designer or import.exe does not support this table structure, only importing through Collections, importtool.exe or Axiell Migration is supported.
This needs to be set up per desired linked field, after which for reversely linked fields the reindexing of a relevant index is enough to create the proper tables and convert existing data, while for hierarchically linked fields you must then use the Axiell ConvertIndexedLink tool instead, to create the proper tables and convert existing data for hierarchical relations to populate those tables and to update the records themselves. So both types of links can be enriched by an indexed link table when the database tables already contain data: just the procedure to get there is different.
This table structure can only be implemented for reversely linked fields (with or without metadata) and for hierarchically (internally) linked fields (with or without metadata). (The relevant options are greyed out for other types of linked fields.)
Hierarchically (internally) linked fields
In thesau.inf, hierarchical links which are suitable for the indexed link structure, are the broader_term <-> term <-> narrower_term relationship only while part_of_reference <-> object_number <-> parts_reference would be applicable in collect.inf, for example. Especially the latter is a good candidate because in archives this internal link is used for the entire hierarchy and records can often have many child records. Let's implement the new table structure for this internal link.
For internal links, the relevant Indexed link properties can be found on the Internal link properties tab of a selected internal link definition.
| 1. | Make a backup of your SQL database and your Axiell application files, just in case something doesn't work out right. Also note you need a backup to go back to if you'd like to undo this new setup because the relevant settings and table structure break compatibility with older versions of Designer and versions of Collections older than 1.11. |
| 2. | Mark the Indexed link checkbox on the Internal link properties tab of the selected internal link definition. Note that if the field group to which this field belongs also contains other reversely or hierarchically linked fields, then you are not allowed to mark the Indexed link checkbox for such fields too: only one linked field in a field group may be assigned the indexed link properties. |
| 3. | Of broader and narrower field occurrences, the indexed link table structure no longer saves the occurrence numbers in which data was originally entered, so the preferred sort order (for display only, so you can change it later at will) must be set here. So select a Broader sort field and a Narrower sort field from the drop-downs. You can only pick from merged-in fields in the same relevant field group of which the source fields in the linked database table have a non-Free Text index. Source fields which are linked fields themselves have an index on their link reference field, so you can also pick those to sort on. Also set the sort Order for both. For example: |
| 4. | Saves your changes to the .inf and recycle the application pool. Proceed with step 5 only if the collect database table is already empty! Otherwise, skip that step and use the Axiell ConvertIndexedLink tool to create the proper tables and convert existing data for hierarchical relations to populate those tables and update the records themselves. |
| 5. | Only if the database table for which you are implementing the indexed link structure, collect in this example, is still empty (or has worthless data), you can now create the relevant table structure for this internal link, by just clearing the collect tables from Designer by right-clicking collect and selecting Clear database in the pop-up menu. This will not only delete all data in the collect tables but will also recreate all required SQL tables, including the new one. The new table name has the format dbo.<table_name>_<tag1>_<tag2>_lrel, in this case dbo.collect_bt_nt_lrel. Below you can see the new table structure. Each row represents a single hierarchical link from both the parent as well as the child perspective. Column bt contains the record number of the linked parent record (from the child's perspective) while column nt contains the record number of the linked child record (from the parent's perspective). The meta column will contain 0 if for this hierarchical link no metadata fields have been set up, while it will contain the primary reference of a metadata record (which is still stored in its own metadata table) if metadata fields for this link have been set up indeed. The bt_data column contains all (unresolved) data (formatted as record XML) of the parent (Part of) field group as registered in the child record, while the nt_data column contains all (unresolved) data of the child (Parts) field group as registered in the parent record. The prikey column finally, provides a primary key for each link registered in this table, which comes in handy for table replication which is desired by some customers to create copied identical tables for a publicly accessible Axiell Internet Server. An example of bt_data of prikey 1 would be: <record priref="0" creation="2021-03-25T13:45:59" modification="2021-03-25T13:45:59" selected="False"> <field tag="B5" occ="1">We only recently found out the relation with the parent record</field> <field tag="1l" occ="1">2</field> </record> Note that the link reference also appears in the data itself. An interesting consequence of the fact that this data is now stored in this new table, is that it won't be present in the normal record XML in the base collect table anymore, not even the parent or child link references. Of course, you won't notice any of this underlying structure as a user in Collections, apart from the fact that it increases performance. Also interesting to note is that the index definitions for tags 1L and 1l still need to be present in collect.inf. but they won't have matching SQL tables anymore after you cleared the database: even reindexing these indexes from within Designer won't create the index tables but will just reindex the dbo.collect_bt_nt_lrel table instead (which can do no harm). So leave the relevant link reference index definitions be, otherwise you'll get Object reference not set to an instance of an object errors in Collections and users won't see any values in the Parts and Part of field groups anymore. |
Reversely linked fields
Reversely linked fields (either internally linked or to a field in a different .inf) have options for the indexed link table structure on the Linked field properties tab, in the Indexed link properties box. Let's assume we have two reversely, internally linked fields in collect.inf: copied_to (cT with linkref tag Ct) and copied_from (cf with linkref tag cF).
| 1. | Make a backup of your SQL database and your Axiell application files, just in case something doesn't work out right. Also note you need a backup to go back to if you'd like to undo this new setup because the relevant settings and indexed link table structure break compatibility with older versions of Designer and versions of Collections older than 1.11. |
| 2. | Mark the Indexed link checkbox on the Linked field properties tab of one of the reversely linked fields. Then automatically the checkbox will also get marked for the reversely linked field. (This works with deselecting the checkbox too.) Note that if the field group to which this field belongs also contains other reversely or hierarchically linked fields, then you are not allowed to mark the Indexed link checkbox for such fields too: only one linked field in a field group may be assigned the indexed link properties. |
| 3. | When occurrences of the current linked field are displayed in a reversely linked record, Collections needs to know how to sort them because the new table structure no longer saves the occurrence numbers in which data was originally entered, so the preferred sort order (for display only, so you can change it later at will) must be set here. So select a Sort field from the drop-down and set the sort Order: do the same for the reversely linked field. You can only pick from indexed fields in the same relevant field group. For example: |
| 4. | Saves your changes to the .inf and recycle the application pool. |
| 5. | If the database table in which you have made the above indexed link setup already contains data, you'll now only still have to reindex one of the two original linkref indexes to create the new table structure for this internal link and to automatically adjust all record data accordingly. In our example that would be the index for Ct or cF. And that's all there's to it! If your SQL database is still empty and only then (!), you may just clear the relevant tables (here the collect tables), instead of reindexing a relevant linkref index, from Designer by right-clicking e.g. collect and selecting Clear database in the pop-up menu. This will not only delete all data in the collect tables but will also recreate all required SQL tables, including the new one. The new indexed link table name has the format dbo.<table1_name>_<tag1>_<table2_name>_<tag2>, in this case dbo.collect_Ct_collect_cF. Below you can see the new table structure. Each row represents a single reverse link from both the perspective of both linked records. Column collect_cF contains the record number of the linked record originally registered in cf/cF while column collect_Ct contains the record number of the linked record originally registered in cT/Ct in the relevant records. The meta column will contain 0 if for this reverse link no metadata fields have been set up, while it will contain the primary reference of a metadata record (which is still stored in its own metadata table) if metadata fields for this reverse link have been set up indeed. The collect_cF_data column contains all (unresolved) data (formatted as record XML) of the linked field group as registered in the record where cF is filled, while the collect_Ct_data column contains all (unresolved) data of the linked field group as registered in the record where Ct is filled. The prikey column finally, provides a primary key for each link registered in this table, which comes in handy for table replication which is desired by some customers to create copied identical tables for a publicly accessible AIS.  An example of collect_Ct_data would be: <record priref="0" creation="2021-03-04T12:12:23" modification="2021-03-04T12:12:23" selected="False"> <field tag="Ct" occ="1">200000003</field> <field tag="5Q" occ="1">4</field> </record> while an example of collect_cF_data would be: <record priref="0" creation="2021-03-04T12:12:23" modification="2021-03-04T12:12:23" selected="False"> <field tag="cF" occ="1">200000002</field> <field tag="3Q" occ="1">4</field> </record> Note that the link reference also appears in the data itself, as well as the primary reference of the metadata record (stored in tags 5Q and 3Q respectively, in this example). An interesting consequence of the fact that this data is now stored in this new table, is that it won't be present in the normal record XML in the base collect table anymore, not even the link references of the reversely linked records. Of course, you won't notice any of this underlying structure as a user in Collections, apart from the fact that it increases performance. Also interesting to note is that the index definitions for tags Ct and cF still need to be present in collect.inf. but they won't have matching SQL tables anymore after you cleared* the database: even reindexing these indexes from within Designer won't create the index tables but will just reindex the dbo.collect_Ct_collect_cF table instead (which can do no harm). So leave the relevant link reference index definitions be, otherwise you'll get Object reference not set to an instance of an object errors in Collections and users won't see any values in the reversely linked fields anymore. * If you didn't clear the database because you were working on an already filled database so you used the reindexing method instead, you may find that the individual SQL tables for the linkref tags still exist, but those will be ignored by Collections and won't be updated anymore. A similar example of a reverse relation (without metadata) between collect (loan.in.number, with link reference tag lK) and loans (object-in.object_number, with link reference tag lL) will result in a collect_lK_loans_lL SQL table for example (either database table name could be mentioned first). An example of a single registered link in this table would look as follows:  |