retrieving single value of nvarchar(max) column takes extremely long

[1knueller]

Environment

64-bit

• Python: 3.7.11

• pyodbc: 4.0.31

• OS: Win10

• DB: SQL Server Management Studio 15.0.18390.0 SQL Server Management Objects (SMO) 16.100.46521.71 Microsoft Analysis Services Client Tools 15.0.19714.0 Microsoft Data Access Components (MDAC) 10.0.19041.1 Microsoft MSXML 3.0 6.0 Microsoft .NET Framework 4.0.30319.42000 Operating System 10.0.19044

• driver: {SQL Server Native Client 11.0}

Issue

Observed behavior:

I am selecting a single value from the db. The db is running local btw. The column typeis of type nvarchar(max) and in this specific record the value is a bit less than 8000 characters. I did not expect this to be performant but it takes 6 Minutes and 26 seconds to retrieve the value. The table only has 1 record at this time.

cnxn = pyodbc.connect(conn_str) cursor = cnxn.cursor() query = f'SELECT json FROM [mydb].[dbo].[Infos] where Code = \'{code}\' and Tag = \'{tag}\'' cursor.execute(query) value = cursor.fetchval()

Expected behavior:

this query should take milliseconds at maximum.

[1knueller]

After restarting my pc and executing only this query on the fresh db/python/jupyter session it takes fractions of a seconds.

I have no idea how this could take so long at some point.

[v-chojas]

Probably running out of memory and swapping heavily.