time_subquerys.py

"""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
        directavg = 0
        subsql = ""
        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

            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("direct", d_time, count)
            subavg += sub_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("--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("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