Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
updated for pre-evaluated query
"""time_subquerys.py: A quick task to compare subquerys on represent with."""
__author__ = "Alex 'Chozabu' P-B"
__copyright__ = "Copyright 2016, IAgree"
from django.core.management.base import BaseCommand, CommandError
from represent_app import models
import time
class Command(BaseCommand):
help = 'Runs some API methods'
def add_arguments(self, parser):
pass
def handle(self, *args, **options):
subavg = 0
asubavg = 0
directavg = 0
subsql = ""
asubsql = ""
directsql = ""
for i in range(10):
start_time = time.time()
qs = models.Question.objects.filter(direct_vote_count__gt=1)
us = models.UserExtra.objects.filter(questions_asked__in=qs)
sub_res = len(us)
sub_count = us.count()
sub_time = time.time() - start_time
subsql = us.query
astart_time = time.time()
aqs = list(models.Question.objects.filter(direct_vote_count__gt=1))
aus = models.UserExtra.objects.filter(questions_asked__in=aqs)
asub_res = len(aus)
asub_count = aus.count()
asub_time = time.time() - astart_time
asubsql = aus.query
start_time = time.time()
us = models.UserExtra.objects.filter(questions_asked__direct_vote_count__gt=1)
res = len(us)
count = us.count()
d_time = time.time() - start_time
print("sub", sub_time, sub_count)
print("asub", asub_time, asub_count)
print("direct", d_time, count)
subavg += sub_time
asubavg += asub_time
directavg += d_time
directsql = us.query
print("--subsql--")
print(subsql)
#INNER JOIN "represent_app_question" ON ("represent_app_userextra"."id" = "represent_app_question"."user_id") WHERE "represent_app_question"."id" IN (SELECT U0."id" FROM "represent_app_question" U0 WHERE U0."direct_vote_count" > 1)
print("--asubsql--")
print(asubsql)
#INNER JOIN "represent_app_question" ON ("represent_app_userextra"."id" = "represent_app_question"."user_id") WHERE "represent_app_question"."id" IN (12, 24, 8, 10, 22, 13, 21, 20, 7, 1986, 25, 6, 9, 3, 15, 2109, 4, 19, 5, 34, 27, 37, 50, 52, 61, << another thousand or so ids removed >>)
print("--directsql--")
print(directsql)
#INNER JOIN "represent_app_question" ON ("represent_app_userextra"."id" = "represent_app_question"."user_id") WHERE "represent_app_question"."direct_vote_count" > 1
print("----")
#both querys return 1724 results at time of writing
print("subavg:", subavg*.1)
print("asubavg:", asubavg*.1)
print("directavg:", directavg*.1)
#overall results - subquery seems to be slightly slower to do subquery
# subavg: 0.18400719165802004
# directavg: 0.18088221549987793
#another run
# subavg: 0.17612528800964355
# directavg: 0.16919872760772706
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.