Repository navigation
Optimization: Can we use chunking for String ( CHAR,VARCAR,NVARCHAR) data type to remove extra memory allocation #455
Description
Activity
Hi Subrata (@subrata-ms), thank you for opening this issue!
Our team will review it shortly. We aim to triage all new issues within 24-48 hours and get back to you.
If you have additional information to share, please feel free to update the issue.
Thank you for your patience!
We are facing the same issue. We are trying to load some unbounded VARCHAR columns and using mssql, it requires a huge amount of pre-allocated memory which is much larger than the actual memory required to load the column.
Unfortunately, we do not control the data schema so optimizing the column type is not an option. Optimizing the memory requirements would be a huge improvement to the library for us.
I ran into this issue while fetching results from a SQL Server view containing several
VARCHAR(8000)columns. These columns were generated bySTRING_AGG, which commonly producesVARCHAR(8000)metadata when aggregatingVARCHAR(n)values, even when the actual strings are very short.I compared
mssql-pythonagainstpyodbcusing the same view andfetchall(). Despite returning identical results,mssql-pythonconsistently used nearly 2x the peak memory ofpyodbc, with slightly slower fetch times.After looking through
ddbc_bindings.cpp, I noticed a few additional issues that seem to amplify the allocation problem described here:-
Eager allocation for 1,000 rows:
FetchAll_wrapdefaults to afetchSizeof 1,000 for smaller estimated row sizes, allocating buffers before knowing how many rows the query actually returns. Even a query returning only a handful of rows can therefore allocate buffers for 1,000 rows. -
1 GiB memory budget: The
memoryLimitused to calculatefetchSizeis set to 1 GiB. This seems quite large for a single cursor, particularly in applications executing multiple concurrent queries. It also appears to be a sizing heuristic rather than a strict memory limit, since the estimated row size doesn't necessarily reflect the actual allocated buffer sizes. -
Allocation based on declared rather than actual lengths: This is particularly problematic for
STRING_AGGcolumns. A column declared asVARCHAR(8000)might contain only a few characters, but the driver still allocates space based on the full declared width for every row in the fetch batch.
It might be worth looking at how other SQL Server drivers handle this. For example, go-mssqldb uses reusable per-column buffers for variable-length types and reads values based on their actual lengths, rather than preallocating a full-width buffer for every row.
pyodbcalso usesSQLGetDatafor variable-length data, which might be a more directly applicable reference given that both libraries use ODBC.I realize that changing the buffer allocation strategy may involve some tradeoffs with batched fetching, but I wonder whether a combination of smaller initial batches, a substantially lower default memory budget, and dynamically sized or reusable buffers could help.
Even independently of the string-buffer optimization proposed in this issue, adjusting the default
fetchSizeandmemoryLimitbehavior seems worthwhile to prevent excessive memory usage for small result sets.-
Describe the bug
Currently we allocate fixed size memory ( coulumnsize+1) to handle string data type. This was causing problem for CP1252 character set. We have fix this issue under reported SQLAlchemy bug ( #435 ).
However, we do see an opportunity improve the memory allocation for the string data type. One of the consideration/approach could be chunking.
This will help to reduce unnecessary memory allocation considerably during multithreaded execution and for large dataset.
To reproduce
This is considered as code optimization. Below is the related bug -
#435
Expected behavior
All string data type should handle CP1252 character set.