Performance

Connector Caching

Most connectors are optimized for repeated calls using the same parameters, but different data values. This generally applies to Fetch, Insert, Update, Remove, and Select operations. When these methods are called with the same fieldlist more than once, they are able to avoid certain steps such as field mapping and type checking. This optimized functionality is available as follows:

Select: METADATA property and KeyFieldlist are the same.

Fetch: Result set and DestFieldlist are the same.

Insert: METADATA property and SrcFieldlist are the same.

Update and Remove: WRITEBACK and METADATA properties and SrcFieldlist are the same.

Calls to one type of method generally do not affect others, so the following pseudo-code obtains the optimal use of this caching:

Connect to Connection CONN
Set Metadata
Generate result set, producing Fieldlist FL
Loop until done:
Fetch into FL
Insert from FL
Exit

If this were changed to fetch the data and then inserted into one of two tables depending on some condition, then every time the table changed, new field mapping and type checking would be performed. In such a case, establishing two connections for Insert would be optimal.

Locate Fields Before Looping

If you're reading or writing a large number of results, avoid using the LCFieldlist.fieldname syntax. Each time you use an expression of the form LCFieldlist.fieldname, LC must scan through the fields in the list looking for one with a matching name. It's better to create separate a LCField object for each field, and use Lookup to assign them before entering the loop, so that the work of searching for the field name can be done just once. Sample code is shown as follows.

    Dim address as LCField
    Dim city as LCField
    Dim state as LCField
    Dim phone as LCField

    Set address = fieldlist.Lookup ("Address")
    Set city = fieldlist.Lookup ("City")
    Set state = fieldlist.Lookup ("State")
    Set phone = fieldlist.Lookup ("OfficePhone")
    while (src.Fetch (fields) > 0) Then
        Call uidoc.FieldAppendText ("Address", address.Text(0))
        Call uidoc.FieldAppendText ("City", city.Text(0))
        Call uidoc.FieldAppendText ("State", state.Text(0))
        Call uidoc.FieldAppendText ("Phone", phone.Text(0))
    Wend

Tight Loops

Another way to optimize LC LSX code is to produce tight loops, moving as much code out of the loop as possible. Anything inside such a loop must be done for every iteration of the loop, and can therefore become costly with large data sets. For example, inside a loop which transfers 10,000 records, you may need to access a field in a fieldlist. Instead of using the property as Fieldlist.Fieldname, which will locate the field by name every iteration, you could instead use Field = Fieldlist.Fieldname outside the loop and then simply refer to Field inside the loop. This will remove 10,000 property accesses for the fieldlist.

Multiple Row Operations

Some databases are capable of "array" operations that fetch or insert multiple rows of data with a single call. One call to return ten elements of a result set simultaneously is substantially faster than ten fetches of one element each. You can take advantage of this by writing your code to use multi-value LCFieldlist objects.

If the database doesn't support array operations, you can still Fetch or Insert with multiple rows, but you won't see any performance improvement from doing so, because the connector code implements the multiple operation by issuing repeated calls to read or write one record at a time.