string fields are sent padded to their declared size (FieldsToStream / GetData)

BIZ TMS RemoteDB
1

For non-blob fields, TRemoteStatement.FieldsToStream (RemoteDB.Server.Wrappers.pas) writes Fields[col].DataSize bytes, i.e. the declared buffer size, not the actual value length. If a SQL result column is reported by FireDAC as a string field with a large declared size, every row carries that size in zero padding. We would like to know whether RemoteDB can avoid sending the padding.

Observed

- Query: SELECT id, number, coalesce((SELECT lang_data FROM ...), (SELECT '')) AS f2 ... LIMIT 2500 (lang_data is varchar(255); the coalesce with an untyped literal gives postgreSQL type text, shown by our SQL tool as varchar(10485760))

- On the server FireDAC reports f2 as WideString, Size=8190, DataSize=16382

- Response body for 2,500 rows: 41,337,500 bytes = 16,535 bytes per row, almost all zero padding

- On the wire (deflate, chunked) the same response is only about 259,000 bytes (server side count of bytes written into the HTTP response), so the data is highly compressible padding

- The same query with CAST(... AS varchar(255)) reports Size=255, DataSize=512: response body 1,662,500 bytes (665 bytes per row)

Consequences on the client (measured)

- Client-side memory and CPU cost grow with the padded size, not the payload. For pages of 5,000 to 10,000 rows (82 to 165 MB decompressed) TRemoteDBProvider.Open spent 17 to 27 seconds in TTableData.Write in a debug build (FastMM FullDebugMode), which we believe is caused by the TMemoryStream growing in 8 KB steps; in a release build the same copy loop took 47 to 150 ms.

- Responses above MemoryCacheSize (default 100 MB) go to a temp file; with the padded data we saw about 76 MB written and read back per workflow, with the cast it dropped to a few KB.
Where the code assumes full-size values

- Server: TRemoteStatement.FieldsToStream - non-blob branch: Size := D.Fields[col].DataSize, followed by S.Write(blob[0], Size)

- Client: TRemoteDBProvider.GetData - System.Move(B[0], Buffer[0], Size) with Size = Fields[I].DataSize of the client dataset, i.e. it assumes Length(B) = DataSize

- Client: TBaseXDataset.FillRecordData allocates SetLength(Bytes, DataSize) and sets the last byte (ftString) or last two bytes (ftWideString) to zero

Workaround we use now:

We cast the affected SQL expression back to the declared column length (varchar(255)). This helps for this case but needs the column length to be known in the generator.

Is there an existing or planned option to send ftString / ftWideString values in their actual length (up to and including the terminator) and to pad on the client?

2

Hi,

Thank you for the detailed analysis. Your reading of the code is correct: for string fields, RemoteDB sends the full field buffer, and its size comes from the field size the server-side dataset reports. Here FireDAC reports the coalesce result as a WideString of 8190 characters, so every value takes 16382 bytes.

Sending only the actual length would not solve the problem, for two reasons:

  • Network traffic: compression is enabled by default, and the padding is all zeros, so it already compresses very well. Your 41 MB became 259 KB on the wire.
  • Client memory: TXDataset stores each record in a fixed-size buffer, sized from the declared size of each field. Even with trimmed values, each row would still use about 16 KB of client memory for that column.

So the real fix is a realistic declared size for that column. There are two ways to get it.

1. Cast the expression in SQL (best option)

As you found, CAST(... AS varchar(255)) gives Size=255 and brings the row down to about 665 bytes. The cost depends on the declared size, not on the actual data, so an upper bound works even if the generator can't know the exact length. Note that PostgreSQL silently truncates values longer than the length you cast to, so pick a bound you are sure of.

2. Map large strings to memo on the server

If you can't change the SQL, add a FireDAC mapping rule to the TFDConnection used by the RemoteDB server, so that large WideString columns become WideMemo. RemoteDB sends memo fields with their actual length, and memo values are not part of the fixed-size record buffer.

var
  Rule: TFDMapRule;
begin
  FDConnection1.FormatOptions.OwnMapRules := True;
  Rule := FDConnection1.FormatOptions.MapRules.Add;
  Rule.SourceDataType := dtWideString;
  Rule.SizeMin := 1000;
  Rule.TargetDataType := dtWideMemo;
end;

Things to consider:

  • The column becomes a TWideMemoField on the client. AsString works the same, but you need to recreate persistent fields, and grids may show (WIDEMEMO) instead of the text.
  • OwnMapRules := True makes the connection use only its own rules, so copy over any rules you have set at the manager or driver level.
  • Set SizeMin above your regular varchar sizes, so normal columns stay as strings.

About the open time

The 17-27 seconds only happen in the FastMM FullDebugMode build; the release build takes 47-150 ms. The client keeps the response in a memory stream that grows in small steps, which is slow under FullDebugMode when the response is large. With either option above the response becomes small, and that cost mostly goes away.

If you still see problems after this, please let us know. We can look into improving how the client buffers large responses.

3

As written before we already changed our sql. So this works fine.
Thanks