Tech Tip: Find All Unique Fields With No Index

PRODUCT: 4D | VERSION: 11 | PLATFORM: Mac & Win

Published On: September 2, 2010

4D v11 SQL introduced the ability to have fields marked Unique without the requirement of having an index. However in some cases this can have a tremendously negative effect on performance.

If a field is set Unique, but not indexed, every time 4D creates a record it must sequentially scan every other record in order to ensure uniqueness. The more records in the table, the longer it will take to create a new record.

For small tables this may not be an issue and it may not be worth it to add another index to the database. But for a table with more than ~1,000,000 records the delay can be perceptible to the user. Also keep in mind that in a client-server environment this sort of sequential operation can monopolize the cache and affect the performance for other users.

Also keep in mind that any time there is a "batch" creation of records, an index should be used because the time it takes to create new records will be noticeably slow after only ~50,000-100,000 records.

The following 4D code can be used to identify all unique fields in the database that do not have an index, with the goal in mind to identify cases were this is not appropriate. The output is a text file in the Logs folder named "UniqueNotIndexed.txt".

C_LONGINT($maxTableNumber_l;$currentTable_l)
C_LONGINT($maxFieldCount_l;$currentField_l)
C_LONGINT($dontCare_l)  ` For GET FIELD PROPERTIES values that are not used.
C_BOOLEAN($dontCare_f;$isIndexed_f;$isUnique_f)
C_TEXT($logHeader_t;$logRecord_t;$logfile_t)
C_TEXT($delim_t;$lf_t)
C_TIME($logfile_h)
C_TEXT($tableName_t;$fieldName_t;$note_t)
C_LONGINT($recIsInTable_l)

$delim_t:=Char(Tab )
$lf_t:=Char(Line feed )

` Column names for the log file, tab delimited.
$logHeader_t:="Table"+$delim_t
$logHeader_t:=$logHeader_t+"Field"+$delim_t
$logHeader_t:=$logHeader_t+"Records"+$delim_t
$logHeader_t:=$logHeader_t+"Note"+$lf_t

$logfile_t:=Get 4D folder(Logs Folder )+"UniqueNotIndexed.txt"

$logfile_h:=Create document($logfile_t)

If (OK=1)

SEND PACKET($logfile_h;$logHeader_t)

$maxTableNumber_l:=Get last table number

For ($currentTable_l;1;$maxTableNumber_l)
  If (Is table number valid($currentTable_l))
   $maxFieldCount_l:=Get last field number(Table($currentTable_l))
   For ($currentField_l;1;$maxFieldCount_l)
    If (Is field number valid($currentTable_l;$currentField_l))

       ` Note the following line breaks over two lines in text,
       ` it is one statement in the method:
     GET FIELD PROPERTIES($currentTable_l;$currentField_l;$dontCare_l;$dontCare_l;$isIndexed_f;$isUnique_f;$dontCare_f)

     If (($isUnique_f) & (Not($isIndexed_f)))

      $tableName_t:=Table name(Table($currentTable_l))
      $fieldName_t:=Field name(Field($currentTable_l;$currentField_l))
      $recIsInTable_l:=Records in table(Table($currentTable_l)->)

      Case of
       : ($recIsInTable_l<=10000)
        $note_t:="Indexing optional."

       : (($recIsInTable_l>10000) & ($recIsInTable_l<=50000))
        $note_t:="Indexing recommended."

       : ($recIsInTable_l>50000)
        $note_t:="Indexing requried."
      End case

      $logRecord_t:=$tableName_t+$delim_t
      $logRecord_t:=$logRecord_t+$fieldName_t+$delim_t
      $logRecord_t:=$logRecord_t+String($recIsInTable_l)+$delim_t
      $logRecord_t:=$logRecord_t+$note_t+$lf_t

      SEND PACKET($logfile_h;$logRecord_t)

     End if
    End if
   End for
  End if
End for

CLOSE DOCUMENT($logfile_h)
End if

Here is an example of the output:

Table	Field	Records	Note
Table_1	Field_1	3	Indexing optional.
Table_3	Field_1	15506	Indexing recommended.
Table_15	Field_5	12344876	Indexing requried.