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
        asubavg = 0
        directavg = 0
        app_avg = 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()
            subq = models.Question.objects.filter(direct_vote_count__gt=1)
            app_start_time = time.time()
            aqs = list(subq)
            app_time = time.time() - app_start_time
            aus = models.UserExtra.objects.filter(questions_asked__in=aqs)
            asub_res = len(aus)
            asub_count = aus.count()
            asub_time = time.time() - astart_time - app_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, "plus app time", app_time)
            print("direct", d_time, count)
            subavg += sub_time
            asubavg += asub_time
            directavg += d_time
            app_avg += app_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, "plus", app_avg*.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