[Django] #33355: Optimize SQLite backend connection functions

Django

unread,

Dec 10, 2021, 3:08:50 AM12/10/21

to django-...@googlegroups.com

#33355: Optimize SQLite backend connection functions
-------------------------------------+-------------------------------------
Reporter: Adam | Owner: nobody
Johnson |
Type: | Status: assigned
Cleanup/optimization |
Component: Database | Version: dev
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
The SQLite backend registers a bunch of data conversion functions when
creating a new connection, with `conn.create_function`. These functions
use the `@none_guard` decorator to check for `None`s in arguments:

{{{
def none_guard(func):
"""
Decorator that returns None if any of the arguments to the decorated
function are None. Many SQL functions return NULL if any of their
arguments
are NULL. This decorator simplifies the implementation of this for the
custom functions registered below.
"""
@functools.wraps(func)
def wrapper(*args, **kwargs):
return None if None in args else func(*args, **kwargs)
return wrapper
}}}

This decorator is wasteful. At call time, it makes Python coerce arguments
to *args and **kwargs and back , and as a decorator it forces two layers
of functions where one would do.

We can save this time by explicitly "inlining" the `None` checks. This
saves a small amount of time per call, but considering that these
functions can be called thousands of times in a single query, it adds up
fast. (...perhaps millions on larger data sets or suboptimal queries!)

Additionally, time and memory is wasted on every new SQLite connection
applying the decorator to create new copies of the same function. (And
`list_aggregate()` creates the exact same classes each time.) Considering
that Django creates a new connection per request by default (and per
test), this adds up.

A small benchmark with one of the shorter functions (Python 3.10.0):

{{{
In [1]: import functools

In [2]: def none_guard(func):
...: """
...: Decorator that returns None if any of the arguments to the
decorated
...: function are None. Many SQL functions return NULL if any of
their arguments
...: are NULL. This decorator simplifies the implementation of this
for the
...: custom functions registered below.
...: """
...: @functools.wraps(func)
...: def wrapper(*args, **kwargs):
...: return None if None in args else func(*args, **kwargs)
...: return wrapper
...:

In [3]: @none_guard
...: def _sqlite_rpad(text, length, fill_text):
...: return (text + fill_text * length)[:length]
...:

In [4]: def _sqlite_rpad_inlined(text, length, fill_text):
...: if text is None or length is None or fill_text is None:
...: return None
...: return (text + fill_text * length)[:length]
...:

In [5]: %timeit _sqlite_rpad("Hello world", 20, " ")
534 ns ± 30.4 ns per loop (mean ± std. dev. of 7 runs, 1000000 loops each)

In [6]: %timeit _sqlite_rpad_inlined("Hello world", 20, " ")
328 ns ± 26.5 ns per loop (mean ± std. dev. of 7 runs, 1000000 loops each)
}}}

~40% reduction.

--
Ticket URL: <https://code.djangoproject.com/ticket/33355>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

Django

unread,

Dec 10, 2021, 3:29:01 AM12/10/21

to django-...@googlegroups.com

#33355: Optimize SQLite backend connection functions
-------------------------------------+-------------------------------------

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* cc: Nick Pope (added)

Comment:

This would force us to create small wrappers for many (~20) functions from
the `math` module.

--
Ticket URL: <https://code.djangoproject.com/ticket/33355#comment:1>

Django

unread,

Dec 10, 2021, 4:20:43 AM12/10/21

to django-...@googlegroups.com

#33355: Optimize SQLite backend connection functions
-------------------------------------+-------------------------------------

Reporter: Adam Johnson | Owner: nobody
Type: | Status: assigned
Cleanup/optimization |

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Adam Johnson):

* has_patch: 0 => 1

Comment:

Yes. Luckily I've got the patience to do that.

--
Ticket URL: <https://code.djangoproject.com/ticket/33355#comment:2>

Django

unread,

Dec 10, 2021, 5:02:10 AM12/10/21

to django-...@googlegroups.com

#33355: Optimize SQLite backend connection functions
-------------------------------------+-------------------------------------

Reporter: Adam Johnson | Owner: nobody
Type: | Status: assigned
Cleanup/optimization |

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Carlton Gibson):

* cc: Carlton Gibson (added)

--
Ticket URL: <https://code.djangoproject.com/ticket/33355#comment:3>

Django

unread,

Dec 13, 2021, 6:45:42 AM12/13/21

to django-...@googlegroups.com

#33355: Optimize SQLite backend connection functions
-------------------------------------+-------------------------------------

Reporter: Adam Johnson | Owner: Adam
Type: | Johnson
Cleanup/optimization | Status: assigned

Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:

Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* owner: nobody => Adam Johnson
* stage: Unreviewed => Accepted

--
Ticket URL: <https://code.djangoproject.com/ticket/33355#comment:4>

Django

unread,

Dec 13, 2021, 7:07:03 AM12/13/21

to django-...@googlegroups.com

#33355: Optimize SQLite backend connection functions
-------------------------------------+-------------------------------------

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak <felisiak.mariusz@…>):

In [changeset:"5111b636d9b63535fa8990142f3b5c756d08c1b6" 5111b63]:
{{{
#!CommitTicketReference repository=""
revision="5111b636d9b63535fa8990142f3b5c756d08c1b6"
Refs #33355 -- Fixed Trunc() with years < 1000 on SQLite.

Thanks to Nick Pope for spotting the bug in Code Review.

Co-Authored-By: Nick Pope <ni...@nickpope.me.uk>
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/33355#comment:6>

Django

unread,

Dec 13, 2021, 7:07:06 AM12/13/21

to django-...@googlegroups.com

#33355: Optimize SQLite backend connection functions
-------------------------------------+-------------------------------------

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak <felisiak.mariusz@…>):

In [changeset:"c66ecc556883e569fb328b5d7258b17d660c2266" c66ecc5]:
{{{
#!CommitTicketReference repository=""
revision="c66ecc556883e569fb328b5d7258b17d660c2266"
Refs #33355 -- Moved Trunc() assertions for invalid arguments and ISO 8601
week to separate tests.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/33355#comment:5>

Django

unread,

Dec 17, 2021, 5:49:38 AM12/17/21

to django-...@googlegroups.com

#33355: Optimize SQLite backend connection functions
-------------------------------------+-------------------------------------

Keywords: | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Nick Pope):

* stage: Accepted => Ready for checkin

Comment:

Thanks Adam. I think this is ready.

--
Ticket URL: <https://code.djangoproject.com/ticket/33355#comment:7>

Django

unread,

Dec 22, 2021, 9:20:06 AM12/22/21

to django-...@googlegroups.com

#33355: Optimize SQLite backend connection functions
-------------------------------------+-------------------------------------

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak <felisiak.mariusz@…>):

In [changeset:"c4328c2f4e958c13dbe1ff47368f06982dfa59b2" c4328c2f]:
{{{
#!CommitTicketReference repository=""
revision="c4328c2f4e958c13dbe1ff47368f06982dfa59b2"
Refs #33355 -- Optimized Trunc() on SQLite by using f-strings.

Co-Authored-By: Nick Pope <ni...@nickpope.me.uk>
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/33355#comment:9>

Django

unread,

Dec 22, 2021, 9:20:06 AM12/22/21

to django-...@googlegroups.com

#33355: Optimize SQLite backend connection functions
-------------------------------------+-------------------------------------

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak <felisiak.mariusz@…>):

In [changeset:"a8fa3e5cd77416b9e4a5b28b216fb3e19609a37d" a8fa3e5c]:
{{{
#!CommitTicketReference repository=""
revision="a8fa3e5cd77416b9e4a5b28b216fb3e19609a37d"
Refs #33355 -- Added missing tests for database functions and expression
on null values.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/33355#comment:8>

Django

unread,

Dec 22, 2021, 9:22:04 AM12/22/21

to django-...@googlegroups.com

#33355: Optimize SQLite backend connection functions
-------------------------------------+-------------------------------------

Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* needs_better_patch: 0 => 1
* stage: Ready for checkin => Accepted

--
Ticket URL: <https://code.djangoproject.com/ticket/33355#comment:10>

Django

unread,

Dec 23, 2021, 1:35:13 AM12/23/21

to django-...@googlegroups.com

#33355: Optimize SQLite backend connection functions
-------------------------------------+-------------------------------------

Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak <felisiak.mariusz@…>):

In [changeset:"fa4b2c15f27edf8d6b8c88b451e23824ddec928c" fa4b2c15]:
{{{
#!CommitTicketReference repository=""
revision="fa4b2c15f27edf8d6b8c88b451e23824ddec928c"
Refs #33355 -- Optimized LPad() database function on SQLite.

Co-Authored-By: Nick Pope <ni...@nickpope.me.uk>
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/33355#comment:11>

Django

unread,

Dec 23, 2021, 6:33:40 AM12/23/21

to django-...@googlegroups.com

#33355: Optimize SQLite backend connection functions
-------------------------------------+-------------------------------------

Keywords: | Triage Stage: Ready for
| checkin

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* needs_better_patch: 1 => 0

* stage: Accepted => Ready for checkin

--
Ticket URL: <https://code.djangoproject.com/ticket/33355#comment:12>

Django

unread,

Dec 23, 2021, 7:36:47 AM12/23/21

to django-...@googlegroups.com

#33355: Optimize SQLite backend connection functions
-------------------------------------+-------------------------------------

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak <felisiak.mariusz@…>):

In [changeset:"2d991ff661f72195f3a57be66d2bbc761c923f7e" 2d991ff6]:
{{{
#!CommitTicketReference repository=""
revision="2d991ff661f72195f3a57be66d2bbc761c923f7e"
Refs #33355 -- Moved SQLite functions to separate module.

Co-Authored-By: Nick Pope <ni...@nickpope.me.uk>
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/33355#comment:13>

Django

unread,

Dec 23, 2021, 7:36:47 AM12/23/21

to django-...@googlegroups.com

#33355: Optimize SQLite backend connection functions
-------------------------------------+-------------------------------------

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak <felisiak.mariusz@…>):

In [changeset:"ec7554f1c28256fbe0e23fab452e7e9b96711230" ec7554f1]:
{{{
#!CommitTicketReference repository=""
revision="ec7554f1c28256fbe0e23fab452e7e9b96711230"
Refs #33355 -- Removed @none_guard from SQLite functions.

Co-Authored-By: Nick Pope <ni...@nickpope.me.uk>
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/33355#comment:15>

Django

unread,

Dec 23, 2021, 7:36:47 AM12/23/21

to django-...@googlegroups.com

#33355: Optimize SQLite backend connection functions
-------------------------------------+-------------------------------------

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak <felisiak.mariusz@…>):

In [changeset:"deec15a9a613558b35024637d02d348a8a95deb1" deec15a9]:
{{{
#!CommitTicketReference repository=""
revision="deec15a9a613558b35024637d02d348a8a95deb1"
Refs #33355 -- Made trunc functions raise ValueError on invalid lookups on
SQLite.

Co-Authored-By: Nick Pope <ni...@nickpope.me.uk>
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/33355#comment:14>

Django

unread,

Dec 23, 2021, 7:36:48 AM12/23/21

to django-...@googlegroups.com

#33355: Optimize SQLite backend connection functions
-------------------------------------+-------------------------------------

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak <felisiak.mariusz@…>):

In [changeset:"5f6a727a6a06437c8dbafaa18817235c0ed658ca" 5f6a727a]:
{{{
#!CommitTicketReference repository=""
revision="5f6a727a6a06437c8dbafaa18817235c0ed658ca"
Refs #33355 -- Constructed SQLite list aggregate types once.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/33355#comment:16>

Django

unread,

Dec 23, 2021, 7:37:06 AM12/23/21

to django-...@googlegroups.com

#33355: Optimize SQLite backend connection functions
-------------------------------------+-------------------------------------

Reporter: Adam Johnson | Owner: Adam
Type: | Johnson

Cleanup/optimization | Status: closed

Component: Database layer | Version: dev
(models, ORM) |

Severity: Normal | Resolution: fixed

Keywords: | Triage Stage: Ready for
| checkin

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------