Re: FreeTDS question about text size

Il giorno sab 6 ott 2018 alle ore 18:35 Jacopille, David
<[email protected]> ha scritto:
>
> General question for a FreeTDS guru:
>
> We saw the warning in the freetds.conf file:
>
>         # If you get out-of-memory errors, it may mean that your client
>         # is trying to allocate a huge buffer for a TEXT field.
>         # Try setting 'text size' to a more reasonable limit
>         text size = 64512
>
> We needed more but we still tried to be reasonable:
>
>         #text size = 64512
>         client charset = UTF-8
>         text size = 200000
>
> Of course we encountered a nvarchar(max) column that is holding way more than 200000 bytes and it seems to be getting truncated.
>
> What is the impact of increasing text size to 10 MB?
>
> Are any of these three scenarios true?
>
> A.   Every column, even an nvarchar(8), is allocated 10 MB
> B.   The memory utilization is only what the column actually holds...up to 10 MB
> C.   The memory utilization is determined by table design: an nvarchar(8) col would be allocated 16 bytes, and an nvarchar(max) would be allocated 10 MB
>
> Thanks,
> Dave
>

Hi,
  yes, the message is quite scaring but actually is not much true
anymore. Memory to store BLOBs is allocated dynamically, if
TEXT/IMAGE/VARCHAR(MAX) or similar are used more or less the memory
required to store column data used (not surely the maximum) is used.
So:
A) no, NVARCHAR(8) should allocate maximum 32 bytes (to take into
account character encoding conversions, like to UTF-8)
B) yes, we can say that, TEXTSIZE is send to server so is the server
that do the truncation. Considering characters conversion (for
instance for nvarchar(max)) can take more (potentially 30mb for a 10mb
column written in Klingon).
C) no, as said BLOBs use the amount of memory specific column data requires
In the past the full size (so like 10MB for an IMAGE column) were
used, so the worrying comment.

Regards,
  Frediano