Avoiding Temporary Database Tables

This section provides an overview of temporary database tables and discusses how to:

  • Use and sort arrays.

  • Use and sort flat files.

Programs often use temporary database tables to hold intermediate results. Creating, updating, and deleting temporary tables is a resource consuming task, however, and can slow program performance. SQR provides two alternatives to using temporary database tables:

  • Store intermediate results in an SQR array.

  • Store intermediate results in a local flat file.

Both techniques can yield a significant performance gain. Use the SQR language to manipulate the data that is stored in an array or a flat file.

An SQR array can hold as many records as can fit in memory. During the first pass, when records are retrieved from the database, you can store them in the array. Subsequent passes on the data can be made without additional database access.

The following code example retrieves records, prints them, and saves them to an array named customer_array:

create-array name=customer_array size=1000
   field=state:char   field=city:char
   field=name:char    field=phone:char
let #counter = 0
begin-select
state (,1)
city  (,7)
name  (,24)
phone (,55)
   position (+1)
   put &state &city &name &phone into customer_array(#counter)
   add 1 to #counter
from customers
end-select

The customer_array array has four fields that correspond to the four columns selected from the customer’s table, and it can hold up to 1,000 rows. If the customer’s table had more than 1,000 rows, you would need to create a larger array.

The select paragraph prints the data. The PUT command then stores the data in the array. You could use the LET command to assign values to array fields; however, the PUT command performs the same work with fewer lines of code. With PUT, you can assign all four fields in one command.

The #counter variable serves as the array subscript. It starts with zero and maintains the subscript of the next available entry. At the end of the select paragraph, the value of #counter is the number of records in the array.

The next code example retrieves the data from customer_array and prints it:

let #i = 0
while #i < #counter
   get $state $city $name $phone from customer_array(#i)
   print $state (,1)
   print $city  (,7)
   print $name  (,24)
   print $phone (,55)
   position (+1)
   add 1 to #i
end-while

In this code example, #i goes from 0 to #counter–1. The fields from each record are moved into the corresponding variables: $name, $city, $state, and $phone. These values are then printed.

Sorting Arrays

In many cases, intermediate results must be sorted by a different field. The following sample program shows how to sort customer_array by name. The sample program uses a well-known sorting algorithm called QuickSort. You can copy this code into your program, make appropriate changes, and use it to sort your array:

Program ex24a.sqr
#define MAX_ROWS 1000
begin-setup
create-array name=customer_array size={MAX_ROWS}
   field=state:char     field=city:char
   field=name:char      field=phone:char
!
! Create a helper array that is used in the sort
!
create-array name=QSort size={MAX_ROWS}
      field=n:number    field=j:number
end-setup
begin-program
  do main
end-program
begin-procedure main
let #counter = 0
!
! Print customers sorted by state
!
begin-select
state (,1)
city  (,7)
name  (,24)
phone (,55)
   position (+1)
   ! Put data in the array
   put &state &city &name &phone into customer_array(#counter)
   add 1 to #counter
from customers
order by state
end-select
position (+2)
!
! Sort customer_array by name
!
let #last_row = #counter - 1
do QuickSort(0, 0, #last_row)
!
! Print customers (which are now sorted by name)
!
let #i = 0
while #i < #counter
   ! Get data from the array
   get $state $city $name $phone from customer_array(#i)
   print $state (,1)
   print $city  (,7)
   print $name  (,24)
   print $phone (,55)
   position (+1)
   add 1 to #i
end-while
end-procedure ! main
!
! QuickSort
!
! Purpose: Sort customer_array by name.
! This is a recursive function. Since SQR does not allocate
! local variables on a stack (they are all static), this
! procedure uses a helper array.
!
! #level - Recursion level (used as a subscript to the helper 
! array)
! #m     - The "m" argument of the classical QuickSort
! #n     - The "n" argument of the classical QuickSort
!
begin-procedure QuickSort(#level, #m, #n)
   if #m < #n
      let #i = #m
      let #j = #n + 1
      ! Sort key is "name"
      let $key = customer_array.name(#m)
      while 1
         add 1 to #i
         while #i <= #j and customer_array.name(#i) < $key
             add 1 to #i
         end-while
         subtract 1 from #j
         while #j >= 0 and customer_array.name(#j) > $key
            subtract 1 from #j
         end-while
         if #i < #j
            do QSortSwap(#i, #j)
         else
            break
         end-if
      end-while
      do QSortSwap(#m, #j)
      add 1 to #level
      ! Save #j and #n
      let QSort.j(#level - 1) = #j
      let QSort.n(#level - 1) = #n
      subtract 1 from #j
      do QuickSort(#level, #m, #j)
      ! restore #j and #n
      let #j = QSort.j(#level - 1)
      let #n = QSort.n(#level - 1)
      add 1 to #j
      do QuickSort(#level, #j, #n)
      subtract 1 from #level
   end-if
end-procedure ! QuickSort
!
!
! QSortSwap
!
! Purpose: Swaps records #i and #j of customer_array
 !
! #i     - Array subscript
! #j     - Array subscript
!
begin-procedure QSortSwap(#i, #j)
   get $state $city $name $phone from customer_array(#i)
   let customer_array.state(#i) = customer_array.state(#j)
   let customer_array.city(#i)  = customer_array.city(#j)
   let customer_array.name(#i)  = customer_array.name(#j)
   let customer_array.phone(#i) = customer_array.phone(#j)
   put $state $city $name $phone into customer_array(#j)
end-procedure ! QSortSwap

The QuickSort algorithm uses a recursive procedure, which means that it calls itself. SQR maintains only one copy of the local variables of the procedure. In QuickSort, the #j and #n variables are overwritten when QuickSort calls itself.

For the algorithm to work properly, the program must save the values of these two variables before making the recursive call, and then it must restore those values when the call finishes. QuickSort can call itself recursively many times, so the program may need to save many copies of #j and #n. To have the program do this, add a #level variable that maintains the depth of recursion. In this example, a helper array, Qsort, is used to hold multiple values of #j and #n.

The QuickSort procedure takes three arguments. The first is the recursion level (or depth), which is #level, as previously described. The second and third arguments are the beginning and end of the range of rows to be sorted. Each time QuickSort calls itself, the range becomes smaller. The main procedure starts QuickSort by calling it with the full range of rows.

The QSortSwap procedure swaps two rows in customer_array. Typically, rows with a lower key value are moved up.

The QuickSort and QSortSwap procedures in ex24a.sqr refer to customer_array and its fields. If you plan to use these procedures to sort an array in your applications, you must change these references to the applicable array and fields. The QuickSort procedure sorts in ascending order.

SQR and Language-Sensitive Sorting

SQR does not support National Language Sensitive sorting natively. SQR compares characters based on Unicode codepoint, and sorting based on Unicode codepoint does not correctly sort order language-sensitive data.

See Understanding the SQR Command Line.

The QuickSort procedure does not support National Language Sensitive character string sorting. The comparisons are simple string comparisons based on Unicode codepoint used internally in SQR to represent string data. For instance, the following code lines from the preceding code sample would sort data in Unicode codepoint order. Unicode codepoints are not ordered to make a correct sorting order of any language.

while #i <= #j and customer_array.name(#i) < $key
and
while #j >= 0 and customer_array.name(#j) > $key

If you want to sort string data in SQR, you may need to write a National Language Sensitive character string comparison and add that to SQR. The QuickSort procedure will then be modified in the following way:

while #i <= #j and NLS_STRING_COMPARE(customer_array.name(#i),$key)
while #j >= 0 and NLS_STRING_COMPARE($key,customer_array.name(#j))

An alternative to an array is a flat file. You can use a flat file when the required array size exceeds the available memory.

The code example in the previous section can be rewritten to use a file instead of an array. The advantage of using a file is that the program is not constrained by the amount of memory that is available. The disadvantage of using a file is that the program performs more input and output (I/O). However, it may still be faster than performing another SQL statement to retrieve the same data.

This program uses the UNIX/Linux sort utility to sort the file by name. This example can be extended to include other operating systems.

The following code example is rewritten to use the cust.dat file instead of the array:

Program ex24b.sqr
begin-program
  do main
end-program
begin-procedure main
!
! Open cust.dat
!
open 'cust.dat' as 1 for-writing record=80:vary
begin-select
state (,1)
city  (,7)
name  (,24)
phone (,55)
   position (+1)
   ! Put data in the file
   write 1 from &name:30 &state:2 &city:16 &phone:10
from customers
order by state
end-select
position (+2)
!
! Close cust.dat
close 1
! Sort cust.dat by name
!
call system using 'sort cust.dat > cust2.dat' #status
if #status <> 0
    display 'Error in sort'
    stop
end-if
!
! Print customers (which are now sorted by name)
!
open 'cust2.dat' as 1 for-reading record=80:vary
while 1  ! loop until break
   ! Get data from the file
   read 1 into $name:30 $state:2 $city:16 $phone:10
   if #end-file
      break   ! End of file reached
   end-if
   print $state (,1)
   print $city  (,7)
   print $name  (,24)
   print $phone (,55)
   position (+1)
end-while
!
! close cust2.dat
close 1
end-procedure ! main

The program starts by opening a cust.dat file:

open 'cust.dat' as 1 for-writing record=80:vary

The OPEN command opens the file for writing and assigns it file number 1. You can open as many as 12 files in one SQR program. The file is set to support records of varying lengths with a maximum of 80 bytes (characters). For this example, you can also use fixed-length records.

As the program selects records from the database and prints them, it writes them to cust.dat:

write 1 from &name:30 &state:2 &city:16 &phone:10

The WRITE command writes the four columns into file number 1, the currently open cust.dat. It writes the name first, which simplifies sorting the file by name. The program writes fixed-length fields. For example, &name:30 specifies that the name column uses exactly 30 characters. If the actual name is shorter, it is padded with blanks. When the program has finished writing data to the file, it closes the file by using the CLOSE command.

The file is sorted with the UNIX sort utility:

call system using 'sort cust.dat > cust2.dat' #status

The sort cust.dat > cust2.dat command is sent to the UNIX system. It invokes the UNIX sort command to sort cust.dat and direct the output to cust2.dat. The completion status is saved in #status; a status of 0 indicates success. Because name is at the beginning of each record, the file is sorted by name.

Next, open cust2.dat for reading. The following command reads one record from the file and places the first 30 characters in $name:

read 1 into $name:30 $state:2 $city:16 $phone:10

The next two characters are placed in $state, and so on. When the end of the file is encountered, the #end-file reserved variable is automatically set to 1 (true). The program checks for #end-file and breaks out of the loop when the end of the file is reached. Finally, the program closes the file by using the CLOSE command.