Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save peterbe/b2b7ed95d422ab25a65639cb8412e75e to your computer and use it in GitHub Desktop.
Save peterbe/b2b7ed95d422ab25a65639cb8412e75e to your computer and use it in GitHub Desktop.
import os
from django.core.wsgi import get_wsgi_application
os.environ.setdefault("DJANGO_SETTINGS_MODULE", "songsearch.settings")
application = get_wsgi_application()
from songsearch.main.models import *
import hashlib
def f1(a):
names = []
for song in Song.objects.filter(artist=a):
names.append(song.name)
return hashlib.md5("".join(names).encode("utf-8")).hexdigest()
def f2(a):
names = []
for song in Song.objects.filter(artist=a).only("name"):
names.append(song.name)
return hashlib.md5("".join(names).encode("utf-8")).hexdigest()
def f3(a):
names = []
for song in Song.objects.filter(artist=a).values("name"):
names.append(song["name"])
return hashlib.md5("".join(names).encode("utf-8")).hexdigest()
def f4(a):
names = []
for name in Song.objects.filter(artist=a).values_list("name", flat=True):
names.append(name)
return hashlib.md5("".join(names).encode("utf-8")).hexdigest()
from django.contrib.postgres.aggregates import StringAgg
from django.db.models import Func
def f5(a):
# https://www.peterbe.com/plog/django-orm-optimization-story-on-selecting-the-least-possible#c5043c8
return Song.objects.filter(artist=a).aggregate(
names_hash=Func(StringAgg("name", ""), function="md5")
)
def f6(a):
# https://www.peterbe.com/plog/django-orm-optimization-story-on-selecting-the-least-possible#c5043c8
return hashlib.md5(
Song.objects.filter(artist=a)
.aggregate(names_hash=StringAgg("name", ""))["names_hash"]
.encode("utf-8")
).hexdigest()
artist = Artist.objects.get(name="Bad Religion")
print(Song.objects.filter(artist=artist).count())
print(f1(artist) == f2(artist))
print(f2(artist) == f3(artist))
print(f3(artist) == f4(artist))
print(f4(artist) == f5(artist))
print(f5(artist) == f6(artist))
# Reporting
import time
import random
import statistics
functions = f1, f2, f3, f4, f5, f6
times = {f.__name__: [] for f in functions}
for i in range(500):
func = random.choice(functions)
t0 = time.time()
func(artist)
t1 = time.time()
times[func.__name__].append((t1 - t0) * 1000)
for name in sorted(times):
numbers = times[name]
print("FUNCTION:", name, "Used", len(numbers), "times")
print("\tBEST", min(numbers))
print("\tMEDIAN", statistics.median(numbers))
print("\tMEAN ", statistics.mean(numbers))
print("\tSTDEV ", statistics.stdev(numbers))
@peterbe
Copy link
Author

peterbe commented Feb 25, 2019

Output I get as of Feb 25 2019:

276
True
True
True
False
False
FUNCTION: f1 Used 92 times
	BEST 5.928993225097656
	MEDIAN 7.311463356018066
	MEAN   7.594626882801885
	STDEV  2.2027017044658423
FUNCTION: f2 Used 75 times
	BEST 2.878904342651367
	MEDIAN 3.3979415893554688
	MEAN   3.4774907430013022
	STDEV  0.5120246550765524
FUNCTION: f3 Used 88 times
	BEST 0.9310245513916016
	MEDIAN 1.1944770812988281
	MEAN   1.3105544176968662
	STDEV  0.35922655625999383
FUNCTION: f4 Used 71 times
	BEST 0.7879734039306641
	MEDIAN 1.1661052703857422
	MEAN   1.2262606284987758
	STDEV  0.3561764250427344
FUNCTION: f5 Used 90 times
	BEST 0.7929801940917969
	MEDIAN 1.0334253311157227
	MEAN   1.1836051940917969
	STDEV  0.4001442703048186
FUNCTION: f6 Used 84 times
	BEST 0.80108642578125
	MEDIAN 1.1119842529296875
	MEAN   1.2281338373819988
	STDEV  0.37146893005516973

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment