Prefetch_related failing against SQL Server

Question

Using Django 2.X, and Pyodbc driver (installed from anaconda conda-forge django-pyodbc-azure) against MS Sql Server (not sure which version), I regularly have bugs using prefetch_related. An example looks simply like:

for obj in MyORMType.objects.prefetch_related('otherormtype_set').all():
    pass

where OtherOrmType has a simple foreign key to MyOrmType, the error is:

...
/opt/conda/lib/python3.6/site-packages/django/db/backends/utils.py in _execute(self, sql, params, *ignored_wrapper_args)
     83                 return self.cursor.execute(sql)
     84             else:
---> 85                 return self.cursor.execute(sql, params)
     86 
     87     def _executemany(self, sql, param_list, *ignored_wrapper_args):

/opt/conda/lib/python3.6/site-packages/sql_server/pyodbc/base.py in execute(self, sql, params)
    544         self.last_params = params
    545         try:
--> 546             return self.cursor.execute(sql, params)
    547         except Database.Error as e:
    548             self.connection._on_error(e)

ProgrammingError: ('The SQL contains -2098 parameter markers, but 128974 parameters were supplied', 'HY000')

I can fall back to the dumb equivalent:

for obj in MyORMType.objects.all():
    other_objs = obj.otherormtype_set.all()

but this is obviously quite slow. This bug occurs regularly for me under many different circumstances in this particular setup (always same Django version, driver, and DB), it's not a one-off annoyance. Is this my fault or an issue with SQL Server or Pyodbc (or Django)? Is there a way to work around the error without having to fetch each obj.otherormtype_set one at a time?

I don't know much about python or django, but this seems related as the error appears related to the parameters: https://stackoverflow.com/questions/43491381/pyodbc-the-sql-contains-0-parameter-markers-but-1-parameters-were-supplied-hy0 — Steve-o169, Jan 10 '19 at 20:48

Simon Charette · Accepted Answer · 2019-01-11T21:03:45.230

1

Under the hood the prefetch_related feature uses IN (...large number of IDs) queries to retrieve objects which seems to be a know issue with the django-pyodbc-azure package you are using.

You can find a bit more details on the Github issues of the package itself.

From a quick investigation it looks like .features.max_query_params will need to be adjusted which is what I suggested doing.

It looks like it could be a limitation similar to the SQLite one with regards to number of allowed parameters.

edited Jan 11 '19 at 21:03

answered Jan 11 '19 at 03:34

Simon Charette

5,009
1
25
33

This definitely seems to be on the right track. I tried implementing this as a hotfix by running ``from sql_server.pyodbc import features``, ``features.max_query_params = 2000`` before anything else, but that doesn't seem to work. I've also tried modifying the ``features`` file to add ``max_query_params = 2000`` to the end, then trying again, and still get the same error. Have you found a way to implement your suggestion which fixes the problem? – scnerd Jan 11 '19 at 19:50
It could be an unfortunate limitation of prefetch related with regards to the number of allowed parameters. See https://code.djangoproject.com/ticket/27833 and https://github.com/django/django/pull/8309. – Simon Charette Jan 11 '19 at 21:05
Looks like the pull request is getting pretty dated at this point. I think that this, combined with the other issue links you provided, give a complete picture of the situation, [though the current situation seems to be](https://github.com/michiya/django-pyodbc-azure/issues/143#issuecomment-453657925) that this won't work right now. I'll accept your answer and award the bounty, but I'd appreciate it if you'd add the details you put in the github issue here as well, and maybe update if/when any of this mess gets cleaned up. Thanks for all your research! – scnerd Jan 11 '19 at 21:21

Prefetch_related failing against SQL Server

1 Answers1