#36143: DatabaseOperations.bulk_batch_size() is overly protective on SQLite in
most
cases
-------------------------------------+-------------------------------------
Reporter: Sarah Boyce | Owner: (none)
Type: | Status: new
Cleanup/optimization |
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Description changed by Sarah Boyce:
Old description:
> `DatabaseOperations.bulk_batch_size()` for SQLite is dependent on
> `DatabaseFeatures.max_query_params` which is currently set as 999.
>
> However, for SQLite versions >= 3.32, this limit is increased to 32,766.
> See: https://www.sqlite.org/limits.html#max_variable_number
>
> > SQLITE_MAX_VARIABLE_NUMBER, which defaults to 999 for SQLite versions
> prior to 3.32.0 (2020-05-22) or 32766 for SQLite versions after 3.32.0.
>
> This means that is some cases (assuming there isn't another database
> limit being hit), we can increase the maximum batch size for SQLite.
>
> For illustration purposes, on SQLite >= 3.32, if the value of
> `DatabaseFeatures.max_query_params` is increased, the following test
> would pass:
>
> {{{#!diff
> diff --git a/tests/bulk_create/tests.py b/tests/bulk_create/tests.py
> index 7b86a2def5..ce9123ce97 100644
> --- a/tests/bulk_create/tests.py
> +++ b/tests/bulk_create/tests.py
> @@ -286,7 +286,7 @@ class BulkCreateTests(TestCase):
> objs = [Country(name=f"Country {i}") for i in range(1000)]
> fields = ["name", "iso_two_letter", "description"]
> max_batch_size = max(connection.ops.bulk_batch_size(fields,
> objs), 1)
> - with self.assertNumQueries(ceil(len(objs) / max_batch_size)):
> + with self.assertNumQueries(1):
> Country.objects.bulk_create(objs, batch_size=max_batch_size
> + 1)
>
> @skipUnlessDBFeature("has_bulk_insert")
> }}}
>
> PR discussion references:
> * https://github.com/django/django/pull/19088#discussion_r1925652660
> * https://github.com/django/django/pull/19088#discussion_r1929940327
New description:
`DatabaseOperations.bulk_batch_size()` for SQLite is dependent on
`DatabaseFeatures.max_query_params` which is currently set as 999.
However, for SQLite versions >= 3.32, this limit is increased to 32,766.
See: https://www.sqlite.org/limits.html#max_variable_number
> SQLITE_MAX_VARIABLE_NUMBER, which defaults to 999 for SQLite versions
prior to 3.32.0 (2020-05-22) or 32766 for SQLite versions after 3.32.0.
This means that is some cases (assuming there isn't another database limit
being hit), we can increase the maximum batch size for SQLite.
For illustration purposes, on SQLite >= 3.32, if the value of
`DatabaseFeatures.max_query_params` is increased, the following test would
pass:
{{{#!diff
diff --git a/tests/bulk_create/tests.py b/tests/bulk_create/tests.py
index 7b86a2def5..ce9123ce97 100644
--- a/tests/bulk_create/tests.py
+++ b/tests/bulk_create/tests.py
@@ -286,7 +286,7 @@ class BulkCreateTests(TestCase):
objs = [Country(name=f"Country {i}") for i in range(1000)]
fields = ["name", "iso_two_letter", "description"]
max_batch_size = max(connection.ops.bulk_batch_size(fields,
objs), 1)
- with self.assertNumQueries(ceil(len(objs) / max_batch_size)):
+ with self.assertNumQueries(1):
Country.objects.bulk_create(objs, batch_size=max_batch_size +
1)
@skipUnlessDBFeature("has_bulk_insert")
}}}
PR discussion references:
* https://github.com/django/django/pull/19088#discussion_r1925652660
* https://github.com/django/django/pull/19088#discussion_r1929940327
Ticket which sparked the discussion/discovery: #36118
--
--
Ticket URL: <https://code.djangoproject.com/ticket/36143#comment:1>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion visit
https://groups.google.com/d/msgid/django-updates/01070194a70345fe-31166e10-6f04-4856-9c28-f7d082012c27-000000%40eu-central-1.amazonses.com.
