High memory usage with MSSQL ntext about turbodbc HOT 4 CLOSED

Without closer inspection, my best guess would be that the ODBC driver returns a very large field size for NTEXT columns.

If the returned size is something like 1 billion, this means that 1,000,000,000 * 2 (for unicode) bytes are required to buffer a single value. Because the default read buffer size is set to 20 MB and this field alone exceeds it, it will create a buffer with just one row. For asynchronous IO, a second buffer is needed. Usually, the buffer is halved in order to keep the total read buffer size constant. But a buffer of just 1 row cannot be halved, thus leading to doubling the required memory to 4 GB.

This would be consistent with the information given here: https://docs.microsoft.com/en-us/sql/t-sql/data-types/ntext-text-and-image-transact-sql.

After confirmation, I would have to see whether there is a more clever way to transfer text data than doing the same thing as for VARCHAR.

from turbodbc.

Ok, so I did some reading on retrieving large data from the database. Basically, there seem to be the following options to alleviate your problem:

• Option 1: Allow turbodbc users to configure a maximum of characters to retrieve, even if the database indicates that this might not be sufficient for all data.
• Option 2: Use the ODBC function SQLGetData() to retrieve long data. This seems to have some issues attached, such that it may not be possible to read batches larger than single rows.
• Option 3: Use a reasonably sized (configurable) buffer for results. For each row in a batch, check whether warnings are present for each fetched row. Refetch rows with warnings attached using larger buffers. This sets higher requirements on the database driver, since it needs to support cursor scrolling. It may also not play well with asynchronous I/O.

Though options 2 and 3 are nice to have, I feel that option 1 is the only one that would really be in line with turbodbc's target audience of data scientists. They typically have to deal with numbers, dates, and strings of limited length. For them, bulk transfer as currently implemented is ideal.

Option 1 would allow to extend this approach to more extensive fields while keeping memory consumption acceptable. The price would be possible truncation of data. I think that is okay for turbodbc's target audience.

Options 2 and 3 could be implemented at a (very) later stage.

For the time being, you could use the following workarounds:

• Do not select long columns.
• When you do select long columns, cast them into types of limited length such as VARCHAR(1000).

from turbodbc.

Thank you for the analysis.

Yes, option 1 would probably make the most sense. I know ceODBC uses this approach via setoutputsize, but it does not truncate, just throws an exception if the limit is exceeded. It also seem to have a small default setoutputsize.

I discovered this with a legacy database, which might not be turbodbcs target audience, but multi GB allocations even when the table was empty was a bit surprising.

from turbodbc.

At the same go, one should also fix the handling of VARCHAR(MAX), which is currently handled like a VARCHAR of length 0. The maximum length should be used for such fields as well. See issue #81 for more details.

from turbodbc.