pyodbc -> mssql-python, setting chunksize does not stream results

[aa-matthias]

Describe the bug

Migrating from pyodbc and trying to fetch a huge table via pandas.read_sql_query by setting chunksize
It looks like the whole result set is loaded into memory which definitely was not the case with pyodbc.
Is that supported already?

To reproduce

        import mssql_python
        import pandas as pd
        import logging

        connection_string = f"SERVER=tcp:{server},1433;DATABASE={database};UID={username};PWD={password};Encrypt=no;"
        conn_MSSQL = mssql_python.connect(connection_string)

        for chunk in pd.read_sql_query(query, conn_MSSQL, chunksize=50000):
            logging.info(f"Fetched {chunk.shape[0]} Results from MSSQL")

Expected behavior

results should be streamed

Further technical details

Python version: 3.12.0
SQL Server version: SQL Server 2019
Operating system: Windows Server 2019

Additional context
Add any other context about the problem here.

[swiffer]

Related to #346 ?

[gordthompson]

I'm not seeing a significant difference between pyodbc and mssql-python:

pyodbc

import pyodbc
import pandas as pd
import psutil

process = psutil.Process()


def printmem(msg):
    mb = process.memory_info().vms / 1048576
    print(f"{msg}: {mb:0.1f} MiB")


driver = "ODBC Driver 18 for SQL Server"
server = "192.168.0.199"
database = "mydb"
username = "scott"
password = "tiger^5HHH"
connection_string = f"DRIVER={driver};SERVER=tcp:{server},1433;DATABASE={database};UID={username};PWD={password};Encrypt=no;"
conn_MSSQL = pyodbc.connect(connection_string)

query = "select * from million_rows order by id"
for chunk in pd.read_sql_query(query, conn_MSSQL, chunksize=50000):
    printmem(f"Fetched {chunk.shape[0]} rows from MSSQL")
"""
Fetched 50000 rows from MSSQL: 273.0 MiB
Fetched 50000 rows from MSSQL: 272.7 MiB
Fetched 50000 rows from MSSQL: 274.0 MiB
Fetched 50000 rows from MSSQL: 273.9 MiB
Fetched 50000 rows from MSSQL: 275.3 MiB
Fetched 50000 rows from MSSQL: 275.7 MiB
Fetched 50000 rows from MSSQL: 276.7 MiB
Fetched 50000 rows from MSSQL: 276.0 MiB
Fetched 50000 rows from MSSQL: 277.7 MiB
Fetched 50000 rows from MSSQL: 277.8 MiB
Fetched 50000 rows from MSSQL: 278.8 MiB
Fetched 50000 rows from MSSQL: 278.0 MiB
Fetched 50000 rows from MSSQL: 279.0 MiB
Fetched 50000 rows from MSSQL: 279.0 MiB
Fetched 50000 rows from MSSQL: 280.8 MiB
Fetched 50000 rows from MSSQL: 280.8 MiB
Fetched 50000 rows from MSSQL: 281.8 MiB
Fetched 50000 rows from MSSQL: 281.0 MiB
Fetched 50000 rows from MSSQL: 282.0 MiB
Fetched 50000 rows from MSSQL: 282.8 MiB
"""

mssql-python

import mssql_python
import pandas as pd
import psutil

process = psutil.Process()


def printmem(msg):
    mb = process.memory_info().vms / 1048576
    print(f"{msg}: {mb:0.1f} MiB")


server = "192.168.0.199"
database = "mydb"
username = "scott"
password = "tiger^5HHH"
connection_string = f"SERVER=tcp:{server},1433;DATABASE={database};UID={username};PWD={password};Encrypt=no;"
conn_MSSQL = mssql_python.connect(connection_string)

query = "select * from million_rows order by id"
for chunk in pd.read_sql_query(query, conn_MSSQL, chunksize=50000):
    printmem(f"Fetched {chunk.shape[0]} rows from MSSQL")
"""
Fetched 50000 rows from MSSQL: 265.4 MiB
Fetched 50000 rows from MSSQL: 268.5 MiB
Fetched 50000 rows from MSSQL: 267.7 MiB
Fetched 50000 rows from MSSQL: 270.7 MiB
Fetched 50000 rows from MSSQL: 269.7 MiB
Fetched 50000 rows from MSSQL: 273.5 MiB
Fetched 50000 rows from MSSQL: 271.8 MiB
Fetched 50000 rows from MSSQL: 274.8 MiB
Fetched 50000 rows from MSSQL: 273.8 MiB
Fetched 50000 rows from MSSQL: 277.8 MiB
Fetched 50000 rows from MSSQL: 276.6 MiB
Fetched 50000 rows from MSSQL: 279.2 MiB
Fetched 50000 rows from MSSQL: 278.2 MiB
Fetched 50000 rows from MSSQL: 281.6 MiB
Fetched 50000 rows from MSSQL: 280.2 MiB
Fetched 50000 rows from MSSQL: 283.6 MiB
Fetched 50000 rows from MSSQL: 281.8 MiB
Fetched 50000 rows from MSSQL: 284.4 MiB
Fetched 50000 rows from MSSQL: 283.6 MiB
Fetched 50000 rows from MSSQL: 284.8 MiB
"""

[aa-matthias]

ok, have the repro example here - should be caused / fixed by #346 from what i understand:

SELECT TOP 50000 CONVERT(VARCHAR(max), binaryColumn, 2), * FROM table

pyodbc
Fetched 5000 rows from MSSQL: 992.5 MiB
Fetched 5000 rows from MSSQL: 1079.2 MiB
Fetched 5000 rows from MSSQL: 1092.5 MiB
Fetched 5000 rows from MSSQL: 1113.5 MiB
Fetched 5000 rows from MSSQL: 1111.5 MiB
Fetched 5000 rows from MSSQL: 1114.5 MiB
Fetched 5000 rows from MSSQL: 1115.5 MiB
Fetched 5000 rows from MSSQL: 1086.0 MiB
Fetched 5000 rows from MSSQL: 1110.1 MiB
Fetched 5000 rows from MSSQL: 1121.3 MiB

mssql_python
Fetched 50000 rows from MSSQL: 2161.2 MiB

changing the query to avoid VARCHAR(max) fixes the issue as well:

mssql_python with modified query
Fetched 5000 rows from MSSQL: 1155.5 MiB
Fetched 5000 rows from MSSQL: 1176.5 MiB
Fetched 5000 rows from MSSQL: 1158.5 MiB
Fetched 5000 rows from MSSQL: 1186.4 MiB
Fetched 5000 rows from MSSQL: 1170.4 MiB
Fetched 5000 rows from MSSQL: 1184.4 MiB
Fetched 5000 rows from MSSQL: 1172.4 MiB
Fetched 5000 rows from MSSQL: 1186.4 MiB
Fetched 5000 rows from MSSQL: 1175.4 MiB
Fetched 5000 rows from MSSQL: 1166.6 MiB

[gargsaumya]

Hi @aa-matthias
Thanks for trying out mssql-python driver and for everyone who took the time to test and share detailed results.
We’ll take a closer look at the behavior described here and follow up with an update.

Thanks!

[gargsaumya]

Hi,
Sharing an update: the issue was caused by fetchmany() ignoring the 'size' parameter. The LOB fetch path used a (while (true)) loop that fetched all remaining rows instead of respecting the requested batch size.
PR #346 has been merged and fixes this issue by properly limiting the fetch loop to the requested size.

The fix will be available in the next release. Thanks again for reporting this!