I understand FIELDS option allows you to explicitly specify the fields we want to work with, which can be useful for optimizing performance and reducing unnecessary processing. It's particularly handy when dealing with large records.
But I still have a confusion whether specifying FIELDS can be more efficient than INDEX field or what? Just want to understand when to use FIELDS and INDEX. Below is the sample query.
for first td_det
fields (td_domain td_line td_nbr td_site)
where td_domain = "ASS"
and td_nbr = "123"
and td_line = 1
no-lock:
end. /*for first td_det*/
Note - For field td_site no index defined..td_domain,td_line and td_nbr all together has one unique index field
They are both useful for improving performance but in different ways.
A FIELDS list is used to reduce the amount of data per record, thus reducing the size of data packets being returned to the client. This allows you to pack more data into each message. Fewer messages means less time for the data to get to the client. This is possible because it is very common for the application to only need a subset of the fields in a record. Of course if you really do need the whole record a FIELDS list isn't going to be possible.
Using a good index allows you to scan and select fewer records. Fewer records means fewer messages which means less time.
You can combine using a good index with asking for less data per record.
IOW, fewer records plus less data per record results in better performance.
For example (using the standard sports2000 database...)
for each customer fields( custNum name discount ) no-lock
where custNum >= 10 and custNum < 20:
display custNum name discount.
end.
This snippet uses the custNum index, so it only looks at 10 records, and because the field list has just 3 fields in it the messages will be very efficient and everything will likely be returned in just one message.
The "discount" field does not participate in any index, so if you had a query looking for customers by discount, such as:
for each customer fields( custNum name discount ) no-lock
where discount >= 10:
display custNum name discount.
end.
The would result in a WHOLE-INDEX search, so lots of records are going to be evaluated, but the messages would be more efficient because only the 3 fields are being shipped to the client. Whereas:
for each customer no-lock
where discount >= 10:
display custNum name discount.
end.
ships ALL of the records and ALL of the fields in each record to the client.
Ultimately you usually want both a good index AND an appropriate FIELDS list.
(Although... if your clients are running with shared memory connections a FIELDS list can be slightly counter-productive.)