Opened 7 years ago
Closed 2 years ago
#28459 closed Cleanup/optimization (fixed)
Improve performance of QuerySet
Reported by: | Sergey Fedoseev | Owned by: | nobody |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | dev |
Severity: | Normal | Keywords: | |
Cc: | Triage Stage: | Accepted | |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | yes |
Easy pickings: | no | UI/UX: | no |
Description
Before:
In [2]: %timeit for x in City.objects.values_list('id'): pass 106 ms ± 2.88 ms per loop (mean ± std. dev. of 7 runs, 10 loops each) In [3]: %timeit for x in City.objects.values_list('id', 'id'): pass 124 ms ± 846 µs per loop (mean ± std. dev. of 7 runs, 10 loops each)
After:
In [2]: %timeit for x in City.objects.values_list('id'): pass 77.7 ms ± 1.49 ms per loop (mean ± std. dev. of 7 runs, 10 loops each) In [3]: %timeit for x in City.objects.values_list('id', 'id'): pass 95.9 ms ± 1.71 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)
Change History (37)
comment:1 by , 7 years ago
Resolution: | → fixed |
---|---|
Status: | new → closed |
comment:2 by , 7 years ago
Summary: | Improve performance of ValuesListIterable → Improve performance of QuerySet |
---|
PR (Improved performance of Model.from_db() when fields are deferred)
Before:
In [3]: %timeit for x in City.objects.only('id'): pass 1.07 s ± 10.1 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
After:
In [2]: %timeit for x in City.objects.only('id'): pass 961 ms ± 5.68 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
comment:3 by , 7 years ago
PR (Improved performance of select_related() when model is prefetched from its parent.)
Before:
In [2]: %timeit for x in Human.objects.select_related('person'): pass 196 ms ± 2.05 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)
After:
In [2]: %timeit for x in Human.objects.select_related('person'): pass 186 ms ± 1.59 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)
comment:5 by , 7 years ago
PR (Improved performance of SQLCompiler.results_iter())
Before:
In [3]: %timeit for x in City.objects.values_list('id'): pass 74 ms ± 1.45 ms per loop (mean ± std. dev. of 7 runs, 10 loops each) In [2]: %timeit for x in City.objects.annotate(v=models.Value(1, output_field=models.IntegerField())).values_list('v'): pass 343 ms ± 2.62 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
After:
In [4]: %timeit for x in City.objects.values_list('id'): pass 69.1 ms ± 744 µs per loop (mean ± std. dev. of 7 runs, 10 loops each) In [3]: %timeit for x in City.objects.annotate(v=models.Value(1, output_field=models.IntegerField())).values_list('v'): pass 264 ms ± 3.26 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
comment:6 by , 7 years ago
PR (Optimized ModelState instantiation)
Before:
In [5]: %timeit PersonSkill() 8.9 µs ± 13 ns per loop (mean ± std. dev. of 7 runs, 100000 loops each) In [2]: %timeit for x in PersonSkill.objects.all(): pass 83 ms ± 628 µs per loop (mean ± std. dev. of 7 runs, 10 loops each) # skill is None for all objects In [13]: %timeit for x in PersonSkill.objects.select_related('skill'): pass 111 ms ± 446 µs per loop (mean ± std. dev. of 7 runs, 10 loops each) # skill is not None for all objects In [10]: %timeit for x in PersonSkill.objects.select_related('skill'): pass 187 ms ± 735 µs per loop (mean ± std. dev. of 7 runs, 10 loops each)
After:
In [18]: %timeit PersonSkill() 8.54 µs ± 23.8 ns per loop (mean ± std. dev. of 7 runs, 100000 loops each) In [10]: %timeit for x in PersonSkill.objects.all(): pass 75.1 ms ± 825 µs per loop (mean ± std. dev. of 7 runs, 10 loops each) # skill is None for all objects In [5]: %timeit for x in PersonSkill.objects.select_related('skill'): pass 108 ms ± 342 µs per loop (mean ± std. dev. of 7 runs, 10 loops each) # skill is not None for all objects In [7]: %timeit for x in PersonSkill.objects.select_related('skill'): pass 178 ms ± 817 µs per loop (mean ± std. dev. of 7 runs, 10 loops each)
comment:10 by , 7 years ago
PR (Improved performance of ValuesIterable)
Before:
In [6]: %timeit -n 10 for x in City.objects.values('id', 'b'): pass 212 ms ± 4.3 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)
After:
In [5]: %timeit for x in City.objects.values('id', 'b'): pass 151 ms ± 850 µs per loop (mean ± std. dev. of 7 runs, 10 loops each)
comment:11 by , 7 years ago
PR (Improved performance of BaseExpression.convert_value())
Before:
In [2]: %timeit for x in City.objects.annotate(v=models.Value(1, output_field=models.IntegerField())).values_list('v'): pass 262 ms ± 3.42 ms per loop (mean ± std. dev. of 7 runs, 1 loop each) In [3]: %timeit for x in City.objects.annotate(v=models.Value('1', output_field=models.TextField())).values_list('v'): pass 243 ms ± 3.77 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
After:
In [2]: %timeit for x in City.objects.annotate(v=models.Value(1, output_field=models.IntegerField())).values_list('v'): pass 191 ms ± 2.08 ms per loop (mean ± std. dev. of 7 runs, 1 loop each) In [3]: %timeit for x in City.objects.annotate(v=models.Value('1', output_field=models.TextField())).values_list('v'): pass 65.6 ms ± 878 µs per loop (mean ± std. dev. of 7 runs, 10 loops each)
comment:13 by , 7 years ago
PR (Improved performance of sql.compiler.cursor_iter())
Before:
In [6]: %timeit for x in City.objects.values_list('id'): pass 67.2 ms ± 1.03 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)
After:
In [6]: %timeit for x in City.objects.values_list('id'): pass 58 ms ± 580 µs per loop (mean ± std. dev. of 7 runs, 10 loops each)
comment:16 by , 7 years ago
PR (Improved performance of loading DecimalField on SQLite)
Before:
In [5]: %timeit for x in Test.objects.values_list('decimal'): pass 150 ms ± 1.76 ms per loop (mean ± std. dev. of 7 runs, 10 loops each) In [6]: %timeit for x in Test.objects.values_list(models.F('decimal') + 1): pass 123 ms ± 1.08 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)
After:
In [2]: %timeit for x in Test.objects.values_list('decimal'): pass 28.8 ms ± 1.05 ms per loop (mean ± std. dev. of 7 runs, 10 loops each) In [3]: %timeit for x in Test.objects.values_list(models.F('decimal') + 1): pass 84.6 ms ± 541 µs per loop (mean ± std. dev. of 7 runs, 10 loops each)
comment:20 by , 7 years ago
PR (Improved performance of FlatValuesListIterable)
Before:
In [3]: %timeit -n 100 for x in City.objects.values_list('id', flat=True): pass 61.2 ms ± 719 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)
After:
In [30]: %timeit -n 100 for x in City.objects.values_list('id', flat=True): pass 58.8 ms ± 514 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)
comment:21 by , 7 years ago
PR (Improved performance of SQLCompiler.apply_converters())
Before:
In [3]: %timeit for x in City.objects.annotate(v=models.Value(1, output_field=models.IntegerField())).values_list('v'): pass 176 ms ± 725 µs per loop (mean ± std. dev. of 7 runs, 10 loops each) In [6]: %timeit for x in City.objects.annotate(v=models.Value(1, output_field=models.IntegerField())).values('v'): pass 257 ms ± 2.18 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
After:
In [3]: %timeit for x in City.objects.annotate(v=models.Value(1, output_field=models.IntegerField())).values_list('v'): pass 170 ms ± 1.25 ms per loop (mean ± std. dev. of 7 runs, 10 loops each) In [5]: %timeit for x in City.objects.annotate(v=models.Value(1, output_field=models.IntegerField())).values('v'): pass 224 ms ± 1.88 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
comment:24 by , 7 years ago
PR (Improved performance of loading models from DB)
class TestModel(models.Model): field1 = models.IntegerField(null=True) field2 = models.IntegerField(null=True) field3 = models.IntegerField(null=True) field4 = models.IntegerField(null=True) field5 = models.IntegerField(null=True)
TestModel.objects.count() == 20000
, values for all fields is None
.
Before:
In [7]: %timeit for x in TestModel.objects.all(): pass 163 ms ± 1.41 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)
After:
In [7]: %timeit for x in TestModel.objects.all(): pass 143 ms ± 1.07 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)
comment:25 by , 7 years ago
Resolution: | fixed |
---|---|
Status: | closed → new |
There's a patch so reopening the card.
comment:26 by , 7 years ago
PR (Improved performance of loading DecimalField on SQLite)
Before:
In [2]: %timeit for x in DecimalModel.objects.values_list('d'): pass 128 ms ± 531 µs per loop (mean ± std. dev. of 7 runs, 10 loops each) In [3]: %timeit for x in DecimalModel.objects.values_list(models.F('d') + 1): pass 39.5 ms ± 542 µs per loop (mean ± std. dev. of 7 runs, 10 loops each)
After:
In [2]: %timeit for x in DecimalModel.objects.values_list('d'): pass 31.8 ms ± 490 µs per loop (mean ± std. dev. of 7 runs, 10 loops each) In [3]: %timeit for x in DecimalModel.objects.values_list(models.F('d') + 1): pass 20.3 ms ± 579 µs per loop (mean ± std. dev. of 7 runs, 10 loops each)
comment:28 by , 7 years ago
PR (Improved performance of loading DurationField on SQLite and MySQL.)
Before:
%timeit for x in DurationModel.objects.values_list('d'): pass 168 ms ± 678 µs per loop (mean ± std. dev. of 7 runs, 10 loops each)
After:
%timeit for x in DurationModel.objects.values_list('d'): pass 23.1 ms ± 243 µs per loop (mean ± std. dev. of 7 runs, 10 loops each)
comment:30 by , 7 years ago
PR (Used default date converter on SQLite for better performance)
Before:
%timeit for x in TestModel.objects.values_list('date'): pass 83.3 ms ± 829 µs per loop (mean ± std. dev. of 7 runs, 10 loops each)
After:
%timeit for x in TestModel.objects.values_list('date'): pass 40.7 ms ± 613 µs per loop (mean ± std. dev. of 7 runs, 10 loops each)
comment:32 by , 7 years ago
PR (Improved performance of duration expressions on SQLite)
Before:
%timeit for x in Date.objects.annotate(d=models.ExpressionWrapper(models.F('date') + models.F('duration'), models.DateTimeField())).values_list('d'): pass 591 ms ± 21.8 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
After:
%timeit for x in Date.objects.annotate(d=models.ExpressionWrapper(models.F('date') + models.F('duration'), models.DateTimeField())).values_list('d'): pass 390 ms ± 2.4 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
comment:34 by , 7 years ago
PR (Improved performance of time difference expressions on MySQL)
Before:
%timeit bool(TestModel.objects.values_list(models.ExpressionWrapper(models.F('time') - models.F('time'), output_field=models.DurationField()))) 35.4 ms ± 901 µs per loop (mean ± std. dev. of 7 runs, 10 loops each)
After:
%timeit bool(TestModel.objects.values_list(models.ExpressionWrapper(models.F('time') - models.F('time'), output_field=models.DurationField()))) 30 ms ± 810 µs per loop (mean ± std. dev. of 7 runs, 10 loops each)
comment:36 by , 7 years ago
Patch needs improvement: | set |
---|
comment:37 by , 2 years ago
Resolution: | → fixed |
---|---|
Status: | new → closed |
I think we can close this one as fixed now. There is nothing actionable here and separate tickets can be created if further performance improvement opportunities are identified.
In 2d136ede: