0

This code is taking 55 seconds to run, is there any way to optimize this? It is unfeasible

job_list = Job.objects.filter(Q(hiring_manager=emp) | Q( followers=emp) | Q(conductors=emp)).values('pk')
_candidates = Candidate.objects.filter( job__in=job_list, apply_date__range=[date_initial, date_end] ).order_by('-apply_date')
start_time2 = tm.time()
_candidates_ext = _candidates.annotate(month=TruncMonth('apply_date')).values('month').annotate(jcount=Count('pk')).order_by('month')
context['candidates_total'] = len(_candidates)
#context['candidates_total'] = _candidates.count()
context['candidates_ext'] = _candidates_ext
print("--- %s seconds ---" % (tm.time() - start_time2))
print("===== Connections =====")
print(len(connection.queries))
import pdb; pdb.set_trace()

I'm new to the company and would like to start well

Use python 2.7 and DJango 1.8

EDIT I added db_index=True in apply date and this help-me, this line take 0.04s for run:

 _candidates_ext = _candidates.annotate(month=TruncMonth('apply_date')).values('month').annotate(jcount=Count('pk')).order_by('month')

But the assignments are taking about 35s

This my print(connection.queries)

[{u'time': u'0.001', u'sql': u'SELECT @@SQL_AUTO_IS_NULL'}, {u'time': u'0.000', u'sql': u'SELECT VERSION()'}, {u'time': u'0.000', u'sql': u"SELECT `django_session`.`session_key`, `django_session`.`session_data`, `django_session`.`expire_date` FROM `django_session` WHERE (`django_session`.`session_key` = 'd8lx1t4cpa9u9ln2v584skbqh4lypv4y' AND `django_session`.`expire_date` > '2020-01-08 12:20:14.872897')"}, {u'time': u'0.000', u'sql': u'SELECT `auth_user`.`id`, `auth_user`.`password`, `auth_user`.`last_login`, `auth_user`.`is_superuser`, `auth_user`.`username`, `auth_user`.`first_name`, `auth_user`.`last_name`, `auth_user`.`email`, `auth_user`.`is_staff`, `auth_user`.`is_active`, `auth_user`.`date_joined` FROM `auth_user` WHERE `auth_user`.`id` = 9482'}, {u'time': u'0.000', u'sql': u'SELECT `company_company`.`owner_id`, `company_company`.`name`, `company_company`.`cnpj`, `company_company`.`phone`, `company_company`.`website`, `company_company`.`zipcode`, `company_company`.`address`, `company_company`.`adddressnumber`, `company_company`.`adddressnumbercomp`, `company_company`.`neighbourhood`, `company_company`.`state`, `company_company`.`city`, `company_company`.`country`, `company_company`.`size`, `company_company`.`photo`, `company_company`.`date`, `company_company`.`max_users`, `company_company`.`active`, `company_company`.`auto_bill`, `company_company`.`max_cv_import_by_day`, `company_company`.`company_language` FROM `company_company` WHERE `company_company`.`owner_id` = 5705'}, {u'time': u'0.000', u'sql': u'SELECT `company_employee`.`user_id`, `company_employee`.`firm_id`, `company_employee`.`photo`, `company_employee`.`department` FROM `company_employee` WHERE `company_employee`.`user_id` = 9482'}, {u'time': u'0.000', u'sql': u'SELECT `company_company`.`owner_id`, `company_company`.`name`, `company_company`.`cnpj`, `company_company`.`phone`, `company_company`.`website`, `company_company`.`zipcode`, `company_company`.`address`, `company_company`.`adddressnumber`, `company_company`.`adddressnumbercomp`, `company_company`.`neighbourhood`, `company_company`.`state`, `company_company`.`city`, `company_company`.`country`, `company_company`.`size`, `company_company`.`photo`, `company_company`.`date`, `company_company`.`max_users`, `company_company`.`active`, `company_company`.`auto_bill`, `company_company`.`max_cv_import_by_day`, `company_company`.`company_language` FROM `company_company` WHERE `company_company`.`owner_id` = 5705'}, {u'time': u'0.000', u'sql': u'SELECT `company_company`.`owner_id`, `company_company`.`name`, `company_company`.`cnpj`, `company_company`.`phone`, `company_company`.`website`, `company_company`.`zipcode`, `company_company`.`address`, `company_company`.`adddressnumber`, `company_company`.`adddressnumbercomp`, `company_company`.`neighbourhood`, `company_company`.`state`, `company_company`.`city`, `company_company`.`country`, `company_company`.`size`, `company_company`.`photo`, `company_company`.`date`, `company_company`.`max_users`, `company_company`.`active`, `company_company`.`auto_bill`, `company_company`.`max_cv_import_by_day`, `company_company`.`company_language` FROM `company_company` WHERE `company_company`.`owner_id` = 5705'}, {u'time': u'0.000', u'sql': u'SELECT `auth_group`.`id`, `auth_group`.`name` FROM `auth_group` INNER JOIN `auth_user_groups` ON (`auth_group`.`id` = `auth_user_groups`.`group_id`) WHERE `auth_user_groups`.`user_id` = 9482 LIMIT 1'}, {u'time': u'0.546', u'sql': u"SELECT COUNT(*) AS `__count` FROM `combo_job` LEFT OUTER JOIN `combo_job_followers` ON (`combo_job`.`id` = `combo_job_followers`.`job_id`) LEFT OUTER JOIN `combo_job_conductors` ON (`combo_job`.`id` = `combo_job_conductors`.`job_id`) WHERE (`combo_job`.`date_stopped` BETWEEN '2019-01-01 02:00:00' AND '2019-02-01 02:00:00' AND `combo_job`.`sla_stopped` = 1 AND (`combo_job`.`hiring_manager_id` = (SELECT U0.`user_id` AS Col1 FROM `company_employee` U0 WHERE (U0.`firm_id` = 5705 AND U0.`user_id` = 9482)) OR `combo_job_followers`.`user_id` = (SELECT U0.`user_id` AS Col1 FROM `company_employee` U0 WHERE (U0.`firm_id` = 5705 AND U0.`user_id` = 9482)) OR `combo_job_conductors`.`user_id` = (SELECT U0.`user_id` AS Col1 FROM `company_employee` U0 WHERE (U0.`firm_id` = 5705 AND U0.`user_id` = 9482))))"}, {u'time': u'1.290', u'sql': u"SELECT COUNT(*) AS `__count` FROM `combo_job` LEFT OUTER JOIN `combo_job_followers` ON (`combo_job`.`id` = `combo_job_followers`.`job_id`) LEFT OUTER JOIN `combo_job_conductors` ON (`combo_job`.`id` = `combo_job_conductors`.`job_id`) WHERE (`combo_job`.`pub_date` BETWEEN '2019-01-01' AND '2019-02-01' AND (`combo_job`.`hiring_manager_id` = (SELECT U0.`user_id` AS Col1 FROM `company_employee` U0 WHERE (U0.`firm_id` = 5705 AND U0.`user_id` = 9482)) OR `combo_job_followers`.`user_id` = (SELECT U0.`user_id` AS Col1 FROM `company_employee` U0 WHERE (U0.`firm_id` = 5705 AND U0.`user_id` = 9482)) OR `combo_job_conductors`.`user_id` = (SELECT U0.`user_id` AS Col1 FROM `company_employee` U0 WHERE (U0.`firm_id` = 5705 AND U0.`user_id` = 9482))))"}, {u'time': u'0.569', u'sql': u"SELECT COUNT(*) AS `__count` FROM `combo_job` LEFT OUTER JOIN `combo_job_followers` ON (`combo_job`.`id` = `combo_job_followers`.`job_id`) LEFT OUTER JOIN `combo_job_conductors` ON (`combo_job`.`id` = `combo_job_conductors`.`job_id`) WHERE (`combo_job`.`date_stopped` BETWEEN '2019-01-01 02:00:00' AND '2019-02-01 02:00:00' AND `combo_job`.`sla_stopped` = 1 AND (`combo_job`.`hiring_manager_id` = (SELECT U0.`user_id` AS Col1 FROM `company_employee` U0 WHERE (U0.`firm_id` = 5705 AND U0.`user_id` = 9482)) OR `combo_job_followers`.`user_id` = (SELECT U0.`user_id` AS Col1 FROM `company_employee` U0 WHERE (U0.`firm_id` = 5705 AND U0.`user_id` = 9482)) OR `combo_job_conductors`.`user_id` = (SELECT U0.`user_id` AS Col1 FROM `company_employee` U0 WHERE (U0.`firm_id` = 5705 AND U0.`user_id` = 9482))))"}, {u'time': u'0.202', u'sql': u"SELECT COUNT(*) AS `__count` FROM `combo_companycandidate` WHERE (`combo_companycandidate`.`company_id` = 5705 AND `combo_companycandidate`.`date` BETWEEN '2019-01-01 02:00:00' AND '2019-02-01 02:00:00')"}, {u'time': u'49.747', u'sql': u"SELECT COUNT(*) AS `__count` FROM `combo_candidate` WHERE (`combo_candidate`.`job_id` IN (SELECT V0.`id` AS Col1 FROM `combo_job` V0 LEFT OUTER JOIN `combo_job_followers` V2 ON (V0.`id` = V2.`job_id`) LEFT OUTER JOIN `combo_job_conductors` V4 ON (V0.`id` = V4.`job_id`) WHERE (V0.`hiring_manager_id` = (SELECT U0.`user_id` AS Col1 FROM `company_employee` U0 WHERE (U0.`firm_id` = 5705 AND U0.`user_id` = 9482)) OR V2.`user_id` = (SELECT U0.`user_id` AS Col1 FROM `company_employee` U0 WHERE (U0.`firm_id` = 5705 AND U0.`user_id` = 9482)) OR V4.`user_id` = (SELECT U0.`user_id` AS Col1 FROM `company_employee` U0 WHERE (U0.`firm_id` = 5705 AND U0.`user_id` = 9482)))) AND `combo_candidate`.`apply_date` BETWEEN '2019-01-01 02:00:00' AND '2019-02-01 02:00:00')"}]

And i receive this in my terminal:

/home/jobconvo/.virtualenvs/combo/local/lib/python2.7/site-packages/django/db/models/fields/__init__.py:1393: RuntimeWarning: DateTimeField Candidate.status_date received a naive datetime (2019-02-01 00:00:00) while time zone support is active.
  RuntimeWarning)

Edit2: This Sql executed:

SQL Executed
SELECT ••• FROM `combo_candidate` WHERE (`combo_candidate`.`job_id` IN (SELECT ••• FROM `combo_job` V0 LEFT OUTER JOIN `combo_job_followers` V2 ON (V0.`id` = V2.`job_id`) LEFT OUTER JOIN `combo_job_conductors` V4 ON (V0.`id` = V4.`job_id`) WHERE (V0.`hiring_manager_id` = (SELECT ••• FROM `company_employee` U0 WHERE (U0.`firm_id` = 5705 AND U0.`user_id` = 9482)) OR V2.`user_id` = (SELECT ••• FROM `company_employee` U0 WHERE (U0.`firm_id` = 5705 AND U0.`user_id` = 9482)) OR V4.`user_id` = (SELECT ••• FROM `company_employee` U0 WHERE (U0.`firm_id` = 5705 AND U0.`user_id` = 9482)))) AND `combo_candidate`.`apply_date` BETWEEN '2019-01-01 02:00:00' AND '2019-02-01 02:00:00')
Time
45410,722971 ms
GustavoNogueira
  • 389
  • 1
  • 3
  • 16
  • What's that import doing at the bottom there? – AMC Jan 07 '20 at 21:54
  • Just break code and count only this function – GustavoNogueira Jan 07 '20 at 21:56
  • _Just break code and count only this function_ What do you mean? – AMC Jan 07 '20 at 21:57
  • Is your `apply_date` column indexed? – dirkgroten Jan 07 '20 at 22:03
  • Can you add the query django generates so we can take a look? Also remember that before doing a group by in django ORM you should `order_by()` in order to clear order by clauses or it may yield bad results. – ivissani Jan 07 '20 at 22:25
  • I can not post the query at this time but thanks, and tomorrow I can post – GustavoNogueira Jan 07 '20 at 22:47
  • @dirkgroten apply_date = models.DateTimeField(auto_now_add=True) – GustavoNogueira Jan 07 '20 at 22:52
  • add `index=True` and makemigrations/migrate, so that the db indexes the date. – dirkgroten Jan 08 '20 at 10:12
  • Should have been `db_index=True`. Or set `indexes` in your `Meta` class, as explained [here](https://docs.djangoproject.com/en/3.0/ref/models/options/#django.db.models.Options.indexes). Also tell us which db you're using, show the raw SQL query and try running it directly on the db with `EXPLAIN`. – dirkgroten Jan 08 '20 at 10:34
  • @dirkgroten I added connection.queries – GustavoNogueira Jan 08 '20 at 12:24
  • In your python/django shell (manage.py shell) `print(qs.query)` where `qs` is the actual queryset `_candidates.annotate(month=TruncMonth('apply_date')).values('month').annotate(jcount=Count('pk')).order_by('month')`. I don't know what `_candidates` is, maybe another queryset, you'll have to define it as well in your shell. – dirkgroten Jan 08 '20 at 12:33
  • ```_candidates = Candidate.objects.filter( job__in=job_list, apply_date__range=[date_initial, date_end] ).order_by('-apply_date')``` This _candidates – GustavoNogueira Jan 08 '20 at 12:35
  • The line of code defining the queryset `candidates_ext` isn't actually executed in the code you're showing us. It's only executed when the context['candidates_ext'] is printed in your template. The only queryset that is executed is `len(_candidates)`. What is `_candidates`? – dirkgroten Jan 08 '20 at 12:37
  • And what is `job_list`? – dirkgroten Jan 08 '20 at 12:41
  • This my job_list: ``` job_list = Job.objects.filter(Q(hiring_manager=emp) | Q( followers=emp) | Q(conductors=emp)).values('pk')``` – GustavoNogueira Jan 08 '20 at 12:43
  • remove `values('pk')` from `job_list`. Is `emp` an object or also a queryset? – dirkgroten Jan 08 '20 at 12:55
  • emp is another qs: ```obj = self.object emp = Employee.objects.filter(user=self.request.user, firm=obj).values('user')``` – GustavoNogueira Jan 08 '20 at 12:59
  • The problem really is in len (_candidates), job_list in my terminal take a long time too – GustavoNogueira Jan 08 '20 at 13:13
  • In Job, conductor and followers are a ManyToMany fields, hiring_manager is FK – GustavoNogueira Jan 08 '20 at 13:19
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/205604/discussion-between-dirkgroten-and-gustavonogueira). – dirkgroten Jan 08 '20 at 14:03

0 Answers0