Re: [Django] #35904: Speed up fixture loading by adding options bulk insert/create

[Django]

#35904: Speed up fixture loading by adding options bulk insert/create
-----------------------------------+--------------------------------------
Reporter: JorisBenschop | Owner: (none)
Type: New feature | Status: new
Component: Testing framework | Version: 5.0
Severity: Normal | Resolution:
Keywords: | Triage Stage: Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-----------------------------------+--------------------------------------
Changes (by JorisBenschop):

* has_patch: 0 => 1
* resolution: wontfix =>
* status: closed => new


Old description:

> I noticed that loading many fixtures is fairly inefficient, in that it
> tries an update and then an insert for each record. I would propose an
> additional option to the command line (—bulk-insert) that only runs the
> insert queries, but also detects if subsequent items in the fixtures are
> from the same model, so they may be inserted using bulk_insert. If there
> is genuine interest in this feature i will develop it and submit. I have
> this running in my own codebase already.
> Please also indicate if there is no interest, and i spend my energy
> elsewhere =)

New description:

As per ​this forum discussion, I have created a patch to improve load
times for the loaddata command under some circumstances.

Currently the “loaddata” management command uses the obj.save() method for
each deserialized object within a fixture. This function first tries an
UPDATE statement and, if that fails, tries an INSERT statement. By using
the --force_insert a reduction of 50% of queries is achieved.

A second option is to use bulk_create for insertion of multiple records.
This improves insertion speed by (n-1/n), or ~99% for insertion of 100
records.

These options are not meant to cover each use case, and therefore are set
to optional.

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

[Django]

#35904: Speed up fixture loading by adding options bulk insert/create
-----------------------------------+--------------------------------------
Reporter: JorisBenschop | Owner: (none)
Type: New feature | Status: new
Component: Testing framework | Version: 5.0
Severity: Normal | Resolution:
Keywords: | Triage Stage: Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-----------------------------------+--------------------------------------

Description changed by JorisBenschop:

Old description:

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

[Django]

As per [https://forum.djangoproject.com/t/feature-proposal-faster-
fixture-loading-via-loaddata-command/36972 ​this forum discussion], I

have created a patch to improve load times for the loaddata command under
some circumstances.

Currently the “loaddata” management command uses the obj.save() method for
each deserialized object within a fixture. This function first tries an
UPDATE statement and, if that fails, tries an INSERT statement. By using
the --force_insert a reduction of 50% of queries is achieved.

A second option is to use bulk_create for insertion of multiple records.
This improves insertion speed by (n-1/n), or ~99% for insertion of 100
records.

These options are not meant to cover each use case, and therefore are set
to optional.

--
--

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

[Django]

#35904: Speed up fixture loading by adding options bulk insert/create
-----------------------------------+--------------------------------------
Reporter: JorisBenschop | Owner: (none)
Type: New feature | Status: new
Component: Testing framework | Version: 5.0
Severity: Normal | Resolution:
Keywords: | Triage Stage: Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-----------------------------------+--------------------------------------

Comment (by JorisBenschop):

As requested by Simon, I have re-opened the ticket and specified the
expected improvements in a more exact manner. Steps to reproduce are
covered in the tests that are in the PR. I am open to add code to the
serde testing, if there is interest.
--
Ticket URL: <https://code.djangoproject.com/ticket/35904#comment:6>

[Django]

#35904: Speed up fixture loading by adding options bulk insert/create
-----------------------------------+--------------------------------------
Reporter: JorisBenschop | Owner: (none)
Type: New feature | Status: new
Component: Testing framework | Version: 5.0
Severity: Normal | Resolution:
Keywords: | Triage Stage: Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-----------------------------------+--------------------------------------

Description changed by JorisBenschop:

Old description:

Benchmark results
===============
test is to insert 1000 records from a single fixture
current: 0.116s
with --force_insert: 0.066s
with --bulk_create: 0.01s

test is to insert 10000 records from a single fixture
current: 1.07s
with --force_insert: 0.39s
with --bulk_create: 0.010s

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

[Django]

test to insert 1000 records from a single fixture (using the Article
model on Sqlite)

current: 0.116s
with --force_insert: 0.066s

with --bulk_create: 0.010s

test to insert 10000 records from a single fixture

current: 1.07s
with --force_insert: 0.39s

with --bulk_create: 0.104s

I expect larger models to have a more significant improvement even.

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

[Django]

#35904: Speed up fixture loading by adding options bulk insert/create
-----------------------------------+--------------------------------------
Reporter: JorisBenschop | Owner: (none)
Type: New feature | Status: new
Component: Testing framework | Version: 5.0
Severity: Normal | Resolution:
Keywords: | Triage Stage: Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-----------------------------------+--------------------------------------

Comment (by JorisBenschop):

Is there any way i can progress this ticket? I addressed all the issues in
the pr to my knowledge
--
Ticket URL: <https://code.djangoproject.com/ticket/35904#comment:9>

[Django]

#35904: Speed up fixture loading by adding options bulk insert/create
-----------------------------------+--------------------------------------
Reporter: JorisBenschop | Owner: (none)
Type: New feature | Status: new

Component: Testing framework | Version: dev

Severity: Normal | Resolution:
Keywords: | Triage Stage: Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-----------------------------------+--------------------------------------

Changes (by JorisBenschop):

* version: 5.0 => dev

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

[Django]

#35904: Speed up fixture loading by adding options bulk insert/create
-----------------------------------+--------------------------------------
Reporter: JorisBenschop | Owner: (none)
Type: New feature | Status: new
Component: Testing framework | Version: dev
Severity: Normal | Resolution:
Keywords: | Triage Stage: Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-----------------------------------+--------------------------------------

Comment (by Jacob Walls):

Hi Joris. The beige notice at the top of ticket advises the next step is
for someone besides the author to accept the ticket. This is a new
feature, so some engagement on the forum thread is desired and expected.
It's been less than two weeks since the forum post was raised, which is in
most cases a window too short to allow all voices to participate. I would
advise allowing a little more time, especially around the holidays. Thanks
for your dedication.
--
Ticket URL: <https://code.djangoproject.com/ticket/35904#comment:11>

[Django]

#35904: Speed up fixture loading by adding options bulk insert/create
-----------------------------------+--------------------------------------
Reporter: JorisBenschop | Owner: (none)
Type: New feature | Status: new
Component: Testing framework | Version: dev
Severity: Normal | Resolution:
Keywords: | Triage Stage: Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-----------------------------------+--------------------------------------

Comment (by JorisBenschop):

Hi Jacob, thank you so much for explaining this. I understand there are
many tickets that ask for your attention. As a submitter, there is always
a fine line between allowing time and losing momentum. By no means am i
trying to rush you in any way, so i highly appreciate that you explain the
expected timelines on this process.
--
Ticket URL: <https://code.djangoproject.com/ticket/35904#comment:12>

[Django]

#35904: Speed up fixture loading by adding options bulk insert/create
-----------------------------------+--------------------------------------
Reporter: JorisBenschop | Owner: (none)

Type: New feature | Status: closed

Component: Testing framework | Version: dev

Severity: Normal | Resolution: wontfix

Keywords: | Triage Stage: Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-----------------------------------+--------------------------------------

Changes (by Natalia Bidart):

* resolution: => wontfix
* status: new => closed

Comment:

Closing as per my latest comment in the forum post, there is a lack of
community traction for this and a third party app seems like the best next
step for this feature request.
--
Ticket URL: <https://code.djangoproject.com/ticket/35904#comment:13>