Mainipulating Selections with Sets
By Jamras Komoncharoensiri, 4D, Inc. Technical Support
Technical Note 01-2
Technical Notes for Technical Notes for 01-01 January 2001
Introduction
Querying is one of the main operations when working with 4D databases. Each time a query is performed, the amount of time that 4D takes to perform the task is determined by the total number of records in a table. If the total number of records is large, the user will have to wait a good amount of time before getting the results. To database users, getting quick results is a high priority. Fortunately, 4D offers the ability to create sets, relate them to the current selection, and store, load, and clear sets.
This Technical Note discusses some of the advantages of using sets. It also demonstrates how to manipulate record selections using sets.
Sets and Selections
Wouldn't it be nice to have several selections ready to be used without having to perform a search? Since we know that each search operation requires some amount of time to complete and it is possible that the same selection can be called several times during the session, it would be best to find a way to save the results of the search and restore it when needed without having to perform the same search again. This idea can be implemented with sets.
We should start off with comparing a set and a non-set approach. Suppose we have three selections, A, B, and C that will be used in a session. With the non-set approach, you are required to perform a query every time when you want to switch from one selection to another. If one session consists of 10 switches among the three selections, your switching time will be at least 10*T (where T is the search time). Instead of performing the query each time we want to switch to a different selection, we save all three selections into three different sets in prior to all operations. These three sets can be switched in a fraction of the time needed to preform the query itself (usually less than a second). When you want to use a different selection, you only need to retrieve the selection by calling the USE SET command with the name of the set. 4D will make the records in the selected set the current selection.
Comparison:
The Non-set approach
Switching time = S*T
(where S is the amount of time to switch and T is the search time)
The Set approach
Switching time = N*T + N*F or N(T + F)
(where N is the number of selections, T is the search time and F is a time to perform the USE SET command ).
Note: The switching time of the Set approach is better than the non-set approach if the amount of time to switch is greater than the number of selections used in a session.
What can you do with sets?
The standard set operations are union, intersection and difference. The definitions of these operations and their usage are relatively simple and can be found in the 4D Language Reference. It would be best not to repeat what already exists. However, with a special technique, we can manipulate the selection even more than what we can normally do with sets. We can even cut the current selection into several partitions. Why would we want to do this? The answer is similar to the reason why we use the REDUCE SELECTION command. But with the use of sets you obtain faster results than with a non-set approach.
Suppose there are 90 records of best athletes in the selection. We want to know which athletes are in the top 30, middle 30 and last 30. It seems like you can get the top 30 records quite quickly with the REDUCE SELECTION command. The middle and the last 30 will involve two more query operations with an additional technique to obtain the selections. As we have stated earlier, the purpose of using sets in this tech note is to reduce the amount of time it takes to return the results of queries. Having to do the query every time we want to obtain a selection will cost the user time.
The following technique is one of the set approach techniques that allows you to obtain all three sub-selections from the current selection without perfoming several queries. The basic idea is to break down the selection into a sub-selection called a partition. Each partition will be saved into a set.
Figure 1: As the right picture shows, each block represents a set. Each set contains a specific partition.
These partitions can be restored and used at any time once it is created.
Procedure
1. Once we have the initial selection, we have to create three sets out of the current selection. The first set contains the first 30 records. The second set contains the first 60 records. The third set contains all records (90). This procedure is done by determining the size of each partition. The records selection that holds the highest number will be the first to be saved into a set. Then we will repeat the following procedure (*) until we have made all sets out of the current selection.
* Reduce the size of the current selection by 30 records and save into a set.
This is performed by the following two methods.
` Method: Initial_Set C_INTEGER($1) ` Number of partitions C_INTEGER($NumberOfPartition;$TotalRecords) $NumberOfPartition:=$1 $TotalRecords:=Records in selection([Table 1]) Make_Initial_Set ($NumberOfPartition;$TotalRecords) ` Method: Make_Initial_Set C_INTEGER($1;$2) C_INTEGER($partition;$TotalRecords;$index;$pos) $partition:=$1 ` Number of partitions $TotalRecords:=$2 ` Total number of records in the table ` The last set is made first to make sure that no record is cut off at the end $Setname:="Set"+String($partition) CREATE SET([Table 1];$Setname) $index:=$partition-1 While ($index>0) $pos:=($TotalRecords/$partition)*$index REDUCE SELECTION([Table 1];$pos) $Setname:="Set"+String($index) CREATE SET([Table 1];$Setname) $index:=$index-1 End while
Figure 2: Once the Make_Initial_Set has finished its execution, you will have three sets with different sizes.
2. The next step is to start the partitioning process. Since all selections are in the sets, we can compare set(n) and set(n-1) and exclude the records from set(n-1). The result of the comparison will be saved into the result set. The procedure will be performed by the following method.
` Method: Start_Partitioning C_INTEGER($1) ` Number of partitions C_INTEGER($NumberOfPartition) $NumberOfPartition:=$1 While ($NumberOfPartition>0) If ($NumberOfPartition#1) $SetName1:="Set"+String($NumberOfPartition) $SetName2:="Set"+String($NumberOfPartition-1) $SetDestination:="dSet"+String($NumberOfPartition) CREATE SET([Table 1];$SetDestination) DIFFERENCE($SetName1;$SetName2;$SetDestination) CLEAR SET($SetName1) Else $SetName:="Set"+String($NumberOfPartition) $SetDestination:="dSet"+String($NumberOfPartition) USE SET($SetName) CREATE SET([Table 1];$SetDestination) CLEAR SET($SetName) End if $NumberOfPartition:=$NumberOfPartition-1 End while
Figure 3: The execution results of the method Start_Partitioning.
Summary
This Technical Note discusses some of the advantages of using sets. It also demonstrates how to manipulate record selections using sets. The methods that are used in this tech note do not apply to all cases, but rather illustrate the concept of what sets can do.
See Also
Technical Note 97-37 "Sets in 4D V6".
Tech Tip: How to use Union / Difference / Intersect with the Userset, 08/06/1999.
Tech Tip: Using Sets correctly in 4D version 6.x, 2/18/2000.
4D Language Reference 6.7: Command and Topics for Sets "Sets".
4D Language Reference 6.7: Command and Topics for Sets "UNION".
4D Language Reference 6.7: Command and Topics for Sets "INTERSECTION".
4D Language Reference 6.7: Command and Topics for Sets "DIFFERENCE".
4D Language Reference 6.7: Command and Topics for Selection "REDUCE SELECTION".