[SOLVED] How much would it hamper performance loading more fields?

[egsuh]

Let's say one table has 20 fields. But only 7 fields are used in most cases. Other fields are used very infrequently. So, my pascal program retrieves only those 7 fields, and other fields only when necessary (different Pascal function/procedure).

How much would it hamper to retrieve all 20 fields?

The retrieved datasets are short-lived. They are closed as soon as the fields' contents are copied to objects' fields/properties.

[Thaddy]

I would not worry about the fields, but I would worry about the number of rows.
Of course there is likely a small performance penalty, but even that depends on the database engine and connection to server.
What really does hurt is retrieve too many rows in one go: usually there are more rows than columns.

[tonyw]

For most cases, I would agree with Thaddy. However, it is worth being aware of the issues involved, especially when dealing with large to very large datasets.

In the case of IBX, the additional per field overhead is the space allocated in each row buffer to hold the field and the cost of copying the field data from the comms buffer to the row buffer. If you never access it then that's the limit. However, if the field is (e.g.) a large VarChar (we are talking many KB here) then as each row is retained in its own row buffer, the memory overhead can quickly multiply out (field size in bytes times number of rows retrieved). This can be bigger than you think if UTF8 encoding is used given that the field data size is then set at four times the character size (almost always total overkill but you cannot make such an assumption when allocating the row buffer).

IBX used also to have a performance issue every time it needed to extend the internal set of row buffers (another good reason to avoid unused fields). However, that issue was resolved when the improved buffer management scheme was introduced in IBX 2.6.0.

In these cases, using a character Blob can be advantageous over a large VarChar. Firebird returns a 64-bit Blob ID for each Blob field and the row buffers are thus kept compact. Only when you access the field do you start incurring the overhead of Blob access. Note that depending on the database page size, there may also be trade-offs in the size of the database itself when you weigh the pros and cons of large VarChars versus Blobs (each Blob typically requires at least one page, while many rows may be held by the same page).

Query complexity can also be an issue with unused fields, and is something you may overlook when querying a View. Some fields can be very expensive to compute and this in turn affects the time taken to open a query. If you don't include in your query (e.g. on a view) an expensive to compute field then database engines are often good at optimising them out and hence reducing the time it takes to open a query.

[Martin_fr]

As tonyw said, it depends on the content size of those fields. And with variable sized fields that could be worst case the maximum size or some large value.

With fields holding "short data" only, the overhead is usually small. (If they are from the same table...)

That assumes that you haven't done optimizations that conflict with this. Seeing your question, I assume you haven't.

E.g.
- vertical partitioning. If fields are from different partitions it will act like a table join.
- or index only lookups, if you have a special index that contains not only the lookup, but also the fields, then as soon as you use fields outside that index then speed drops.

Of course all that depends on which database you are using.

[egsuh]

Many thanks to the advises. I'll review the uses of records, and search for the optimal solution.