Agilito, queries and sqlite

[httpdss]

Hi ! i have a question regarding queries.
i've created a project, 1 iteration, 1 story and 1 task inside that
story and i get 39 queries executed by going to http://127.0.0.1:8000/agilito/1/iteration/
and most of them are repeated ... i know that you work with postgresql
as your dev database so the impact may not be as great as when using
sqlite..
here go the queries that where executed:

SELECT "account_account"."id", "account_account"."user_id",
"account_account"."timezone", "account_account"."language" FROM
"account_account" WHERE "account_account"."user_id" = 1
SELECT "agilito_iteration"."id", "agilito_iteration"."name",
"agilito_iteration"."description", "agilito_iteration"."start_date",
"agilito_iteration"."end_date", "agilito_iteration"."release_id",
"agilito_iteration"."project_id" FROM "agilito_iteration" WHERE
"agilito_iteration"."id" = 1 ORDER BY "agilito_iteration"."start_date"
ASC
SELECT "agilito_iteration"."id", "agilito_iteration"."name",
"agilito_iteration"."description", "agilito_iteration"."start_date",
"agilito_iteration"."end_date", "agilito_iteration"."release_id",
"agilito_iteration"."project_id" FROM "agilito_iteration" WHERE
"agilito_iteration"."project_id" = 1 ORDER BY
"agilito_iteration"."start_date" ASC
SELECT "agilito_iteration"."id", "agilito_iteration"."name",
"agilito_iteration"."description", "agilito_iteration"."start_date",
"agilito_iteration"."end_date", "agilito_iteration"."release_id",
"agilito_iteration"."project_id" FROM "agilito_iteration" WHERE
"agilito_iteration"."project_id" = 1 ORDER BY
"agilito_iteration"."start_date" ASC
SELECT "agilito_iteration"."id", "agilito_iteration"."name",
"agilito_iteration"."description", "agilito_iteration"."start_date",
"agilito_iteration"."end_date", "agilito_iteration"."release_id",
"agilito_iteration"."project_id" FROM "agilito_iteration" WHERE
"agilito_iteration"."project_id" = 1 ORDER BY
"agilito_iteration"."start_date" ASC
SELECT "agilito_iteration"."id", "agilito_iteration"."name",
"agilito_iteration"."description", "agilito_iteration"."start_date",
"agilito_iteration"."end_date", "agilito_iteration"."release_id",
"agilito_iteration"."project_id" FROM "agilito_iteration" WHERE
"agilito_iteration"."project_id" = 1 ORDER BY
"agilito_iteration"."start_date" ASC LIMIT 1
SELECT "agilito_iteration"."id", "agilito_iteration"."name",
"agilito_iteration"."description", "agilito_iteration"."start_date",
"agilito_iteration"."end_date", "agilito_iteration"."release_id",
"agilito_iteration"."project_id" FROM "agilito_iteration" WHERE
("agilito_iteration"."end_date" <= 2009-10-14 AND
"agilito_iteration"."project_id" = 1 ) ORDER BY
"agilito_iteration"."start_date" DESC LIMIT 1
SELECT "agilito_project"."id", "agilito_project"."name",
"agilito_project"."description" FROM "agilito_project" WHERE
"agilito_project"."id" = 1 ORDER BY "agilito_project"."id" ASC
SELECT "agilito_task"."id", "agilito_task"."name",
"agilito_task"."description", "agilito_task"."estimate",
"agilito_task"."remaining", "agilito_task"."state",
"agilito_task"."category", "agilito_task"."owner_id",
"agilito_task"."user_story_id", "agilito_task"."tags" FROM
"agilito_task" INNER JOIN "agilito_userstory" ON
("agilito_task"."user_story_id" = "agilito_userstory"."id") WHERE
("agilito_task"."state" IN (1, 10, 20, 30) AND
"agilito_task"."user_story_id" = 1 ) ORDER BY
"agilito_userstory"."rank" ASC, "agilito_task"."user_story_id" ASC,
"agilito_task"."id" ASC
SELECT "agilito_task"."id", "agilito_task"."name",
"agilito_task"."description", "agilito_task"."estimate",
"agilito_task"."remaining", "agilito_task"."state",
"agilito_task"."category", "agilito_task"."owner_id",
"agilito_task"."user_story_id", "agilito_task"."tags" FROM
"agilito_task" INNER JOIN "agilito_userstory" ON
("agilito_task"."user_story_id" = "agilito_userstory"."id") WHERE
("agilito_task"."state" IN (1, 10, 20, 30) AND
"agilito_task"."user_story_id" = 1 ) ORDER BY
"agilito_userstory"."rank" ASC, "agilito_task"."user_story_id" ASC,
"agilito_task"."id" ASC
SELECT "agilito_task"."id", "agilito_task"."name",
"agilito_task"."description", "agilito_task"."estimate",
"agilito_task"."remaining", "agilito_task"."state",
"agilito_task"."category", "agilito_task"."owner_id",
"agilito_task"."user_story_id", "agilito_task"."tags" FROM
"agilito_task" INNER JOIN "agilito_userstory" ON
("agilito_task"."user_story_id" = "agilito_userstory"."id") WHERE
("agilito_task"."state" IN (1, 10, 20, 30) AND
"agilito_task"."user_story_id" = 1 ) ORDER BY
"agilito_userstory"."rank" ASC, "agilito_task"."user_story_id" ASC,
"agilito_task"."id" ASC
SELECT "agilito_task"."id", "agilito_task"."name",
"agilito_task"."description", "agilito_task"."estimate",
"agilito_task"."remaining", "agilito_task"."state",
"agilito_task"."category", "agilito_task"."owner_id",
"agilito_task"."user_story_id", "agilito_task"."tags" FROM
"agilito_task" INNER JOIN "agilito_userstory" ON
("agilito_task"."user_story_id" = "agilito_userstory"."id") WHERE
("agilito_task"."state" IN (1, 10, 20, 30) AND
"agilito_task"."user_story_id" = 1 ) ORDER BY
"agilito_userstory"."rank" ASC, "agilito_task"."user_story_id" ASC,
"agilito_task"."id" ASC
SELECT "agilito_task"."id", "agilito_task"."name",
"agilito_task"."description", "agilito_task"."estimate",
"agilito_task"."remaining", "agilito_task"."state",
"agilito_task"."category", "agilito_task"."owner_id",
"agilito_task"."user_story_id", "agilito_task"."tags" FROM
"agilito_task" INNER JOIN "agilito_userstory" ON
("agilito_task"."user_story_id" = "agilito_userstory"."id") WHERE
("agilito_task"."state" IN (1, 10, 20, 30) AND
"agilito_task"."user_story_id" = 1 ) ORDER BY
"agilito_userstory"."rank" ASC, "agilito_task"."user_story_id" ASC,
"agilito_task"."id" ASC
SELECT "agilito_task"."id", "agilito_task"."name",
"agilito_task"."description", "agilito_task"."estimate",
"agilito_task"."remaining", "agilito_task"."state",
"agilito_task"."category", "agilito_task"."owner_id",
"agilito_task"."user_story_id", "agilito_task"."tags" FROM
"agilito_task" INNER JOIN "agilito_userstory" ON
("agilito_task"."user_story_id" = "agilito_userstory"."id") WHERE
("agilito_task"."state" IN (1, 10, 20, 30) AND
"agilito_task"."user_story_id" = 1 ) ORDER BY
"agilito_userstory"."rank" ASC, "agilito_task"."user_story_id" ASC,
"agilito_task"."id" ASC
SELECT "agilito_task"."id", "agilito_task"."name",
"agilito_task"."description", "agilito_task"."estimate",
"agilito_task"."remaining", "agilito_task"."state",
"agilito_task"."category", "agilito_task"."owner_id",
"agilito_task"."user_story_id", "agilito_task"."tags" FROM
"agilito_task" INNER JOIN "agilito_userstory" ON
("agilito_task"."user_story_id" = "agilito_userstory"."id") WHERE
("agilito_task"."state" IN (1, 10, 20, 30) AND
"agilito_task"."user_story_id" = 1 ) ORDER BY
"agilito_userstory"."rank" ASC, "agilito_task"."user_story_id" ASC,
"agilito_task"."id" ASC
SELECT "agilito_task"."id", "agilito_task"."name",
"agilito_task"."description", "agilito_task"."estimate",
"agilito_task"."remaining", "agilito_task"."state",
"agilito_task"."category", "agilito_task"."owner_id",
"agilito_task"."user_story_id", "agilito_task"."tags" FROM
"agilito_task" INNER JOIN "agilito_userstory" ON
("agilito_task"."user_story_id" = "agilito_userstory"."id") WHERE
("agilito_task"."state" IN (1, 10, 20, 30) AND
"agilito_userstory"."iteration_id" = 1 ) ORDER BY
"agilito_userstory"."rank" ASC, "agilito_task"."user_story_id" ASC,
"agilito_task"."id" ASC
SELECT "agilito_task"."id", "agilito_task"."name",
"agilito_task"."description", "agilito_task"."estimate",
"agilito_task"."remaining", "agilito_task"."state",
"agilito_task"."category", "agilito_task"."owner_id",
"agilito_task"."user_story_id", "agilito_task"."tags" FROM
"agilito_task" INNER JOIN "agilito_userstory" ON
("agilito_task"."user_story_id" = "agilito_userstory"."id") WHERE
("agilito_task"."state" IN (1, 10, 20, 30) AND
"agilito_userstory"."iteration_id" = 1 ) ORDER BY
"agilito_userstory"."rank" ASC, "agilito_task"."user_story_id" ASC,
"agilito_task"."id" ASC
SELECT "agilito_tasklog"."id", "agilito_tasklog"."task_id",
"agilito_tasklog"."time_on_task", "agilito_tasklog"."summary",
"agilito_tasklog"."date", "agilito_tasklog"."iteration_id",
"agilito_tasklog"."owner_id", "agilito_tasklog"."old_remaining" FROM
"agilito_tasklog" WHERE "agilito_tasklog"."task_id" = 1
SELECT "agilito_testcase"."id", "agilito_testcase"."name",
"agilito_testcase"."description", "agilito_testcase"."user_story_id",
"agilito_testcase"."priority", "agilito_testcase"."precondition",
"agilito_testcase"."steps", "agilito_testcase"."postcondition" FROM
"agilito_testcase" WHERE "agilito_testcase"."user_story_id" = 1 ORDER
BY "agilito_testcase"."priority" DESC
SELECT "agilito_testcase"."id", "agilito_testcase"."name",
"agilito_testcase"."description", "agilito_testcase"."user_story_id",
"agilito_testcase"."priority", "agilito_testcase"."precondition",
"agilito_testcase"."steps", "agilito_testcase"."postcondition" FROM
"agilito_testcase" WHERE "agilito_testcase"."user_story_id" = 1 ORDER
BY "agilito_testcase"."priority" DESC
SELECT "agilito_testcase"."id", "agilito_testcase"."name",
"agilito_testcase"."description", "agilito_testcase"."user_story_id",
"agilito_testcase"."priority", "agilito_testcase"."precondition",
"agilito_testcase"."steps", "agilito_testcase"."postcondition" FROM
"agilito_testcase" WHERE "agilito_testcase"."user_story_id" = 1 ORDER
BY "agilito_testcase"."priority" DESC
SELECT "agilito_testcase"."id", "agilito_testcase"."name",
"agilito_testcase"."description", "agilito_testcase"."user_story_id",
"agilito_testcase"."priority", "agilito_testcase"."precondition",
"agilito_testcase"."steps", "agilito_testcase"."postcondition" FROM
"agilito_testcase" WHERE "agilito_testcase"."user_story_id" = 1 ORDER
BY "agilito_testcase"."priority" DESC
SELECT "agilito_userstory"."id", "agilito_userstory"."name",
"agilito_userstory"."description", "agilito_userstory"."project_id",
"agilito_userstory"."iteration_id", "agilito_userstory"."rank",
"agilito_userstory"."state", "agilito_userstory"."size",
"agilito_userstory"."created", "agilito_userstory"."closed",
"agilito_userstory"."tags", "agilito_userstory"."copied_from_id" FROM
"agilito_userstory" WHERE "agilito_userstory"."id" = 1 ORDER BY
"agilito_userstory"."rank" ASC, "agilito_userstory"."id" ASC
SELECT "agilito_userstory"."id", "agilito_userstory"."name",
"agilito_userstory"."description", "agilito_userstory"."project_id",
"agilito_userstory"."iteration_id", "agilito_userstory"."rank",
"agilito_userstory"."state", "agilito_userstory"."size",
"agilito_userstory"."created", "agilito_userstory"."closed",
"agilito_userstory"."tags", "agilito_userstory"."copied_from_id" FROM
"agilito_userstory" WHERE "agilito_userstory"."id" = 1 ORDER BY
"agilito_userstory"."rank" ASC, "agilito_userstory"."id" ASC
SELECT "agilito_userstory"."id", "agilito_userstory"."name",
"agilito_userstory"."description", "agilito_userstory"."project_id",
"agilito_userstory"."iteration_id", "agilito_userstory"."rank",
"agilito_userstory"."state", "agilito_userstory"."size",
"agilito_userstory"."created", "agilito_userstory"."closed",
"agilito_userstory"."tags", "agilito_userstory"."copied_from_id" FROM
"agilito_userstory" WHERE ("agilito_userstory"."state" IN (10, 15, 20,
30, 40, 50) AND "agilito_userstory"."iteration_id" = 1 ) ORDER BY
"agilito_userstory"."rank" ASC
SELECT "agilito_userstory"."id", "agilito_userstory"."name",
"agilito_userstory"."description", "agilito_userstory"."project_id",
"agilito_userstory"."iteration_id", "agilito_userstory"."rank",
"agilito_userstory"."state", "agilito_userstory"."size",
"agilito_userstory"."created", "agilito_userstory"."closed",
"agilito_userstory"."tags", "agilito_userstory"."copied_from_id" FROM
"agilito_userstory" WHERE ("agilito_userstory"."state" IN (10, 15, 20,
30, 40, 50) AND "agilito_userstory"."iteration_id" = 1 ) ORDER BY
"agilito_userstory"."rank" ASC, "agilito_userstory"."id" ASC
SELECT "agilito_userstory"."id", "agilito_userstory"."name",
"agilito_userstory"."description", "agilito_userstory"."project_id",
"agilito_userstory"."iteration_id", "agilito_userstory"."rank",
"agilito_userstory"."state", "agilito_userstory"."size",
"agilito_userstory"."created", "agilito_userstory"."closed",
"agilito_userstory"."tags", "agilito_userstory"."copied_from_id" FROM
"agilito_userstory" WHERE ("agilito_userstory"."state" IN (10, 15, 20,
30, 40, 50) AND "agilito_userstory"."iteration_id" = 1 ) ORDER BY
"agilito_userstory"."rank" ASC, "agilito_userstory"."id" ASC
SELECT "agilito_userstory"."id", "agilito_userstory"."name",
"agilito_userstory"."description", "agilito_userstory"."project_id",
"agilito_userstory"."iteration_id", "agilito_userstory"."rank",
"agilito_userstory"."state", "agilito_userstory"."size",
"agilito_userstory"."created", "agilito_userstory"."closed",
"agilito_userstory"."tags", "agilito_userstory"."copied_from_id" FROM
"agilito_userstory" WHERE ("agilito_userstory"."state" IN (10, 15, 20,
30, 40, 50) AND "agilito_userstory"."iteration_id" = 1 AND
"agilito_userstory"."state" = 40 ) ORDER BY "agilito_userstory"."rank"
ASC, "agilito_userstory"."id" ASC
SELECT "announcements_announcement"."id",
"announcements_announcement"."title",
"announcements_announcement"."content",
"announcements_announcement"."creator_id",
"announcements_announcement"."creation_date",
"announcements_announcement"."site_wide",
"announcements_announcement"."members_only" FROM
"announcements_announcement" WHERE
"announcements_announcement"."site_wide" = True ORDER BY
"announcements_announcement"."creation_date" DESC
SELECT "auth_message"."id", "auth_message"."user_id",
"auth_message"."message" FROM "auth_message" WHERE
"auth_message"."user_id" = 1
SELECT "django_openid_useropenidassociation"."id",
"django_openid_useropenidassociation"."user_id",
"django_openid_useropenidassociation"."openid",
"django_openid_useropenidassociation"."created" FROM
"django_openid_useropenidassociation" WHERE
"django_openid_useropenidassociation"."user_id" = 1
SELECT COUNT(*) FROM "agilito_impediment" INNER JOIN
"agilito_impediment_tasks" ON ("agilito_impediment"."id" =
"agilito_impediment_tasks"."impediment_id") INNER JOIN "agilito_task"
ON ("agilito_impediment_tasks"."task_id" = "agilito_task"."id") WHERE
("agilito_impediment"."resolved" IS NULL AND
"agilito_task"."user_story_id" = 1 )
SELECT COUNT(*) FROM "agilito_impediment" INNER JOIN
"agilito_impediment_tasks" ON ("agilito_impediment"."id" =
"agilito_impediment_tasks"."impediment_id") WHERE
("agilito_impediment"."resolved" IS NULL AND
"agilito_impediment_tasks"."task_id" = 1 )
SELECT COUNT(*) FROM "agilito_iteration" WHERE
"agilito_iteration"."project_id" = 1
SELECT COUNT(*) FROM "agilito_iteration" WHERE
("agilito_iteration"."project_id" = 1 AND
"agilito_iteration"."end_date" >= 2009-10-14 AND
"agilito_iteration"."start_date" <= 2009-10-14 )
SELECT COUNT(*) FROM "agilito_project" INNER JOIN
"agilito_project_project_members" ON ("agilito_project"."id" =
"agilito_project_project_members"."project_id") WHERE
"agilito_project_project_members"."user_id" = 1
SELECT COUNT(*) FROM "agilito_userstory" WHERE
("agilito_userstory"."state" IN (10, 15, 20, 30, 40, 50) AND
"agilito_userstory"."iteration_id" = 1 AND "agilito_userstory"."rank"
< 1 )
SELECT COUNT(*) FROM "notification_notice" WHERE
("notification_notice"."archived" = False AND
"notification_notice"."user_id" = 1 AND "notification_notice"."unseen"
= True AND "notification_notice"."on_site" = True )
select distinct i.id from agilito_impediment i join
agilito_impediment_tasks it on it.impediment_id = i.id join
agilito_task t on it.task_id = t.id join agilito_userstory s on
t.user_story_id = s.id where s.iteration_id=1 and i.resolved is NULL
and not s.state = 1 and not t.state = 1

is this a bug? can this be made more performant ? can some of the
queries be postponed for later or made smaller ?

thats all thanks!
httpdss

[Emiliano Heyns]

Hi,

On 15 okt, 03:46, httpdss <ke...@belitzky.com> wrote:
> Hi ! i have a question regarding queries.
> i've created a project, 1 iteration, 1 story and 1 task inside that

> story and i get 39 queries executed by going tohttp://127.0.0.1:8000/agilito/1/iteration/

> and most of them are repeated ... i know that you work with postgresql
> as your dev database so the impact may not be as great as when using
> sqlite..
> here go the queries that where executed:

How do I enable query logging for sqlite? I'd love to be able to
replay this.

I'll have a peek at this, I'm working on the iteration screen right
now. For the short term, enabling caching will make sure this batch of
queries isn't repeated unnecesarily.

> is this a bug? can this be made more performant ? can some of the
> queries be postponed for later or made smaller ?

It's not a bug perse; young django applications like agilito are
likely to have query batches like this because they rely heavily on
the ORM. Can it be made more performant? Yes, certainly. Working on
this particular screen right now, so your input is nicely timed.

Emile

[Emiliano Heyns]

On Oct 15, 9:02 am, Emiliano Heyns <emiliano.he...@gmail.com> wrote:
> Hi,
>
> On 15 okt, 03:46, httpdss <ke...@belitzky.com> wrote:
>
> > Hi ! i have a question regarding queries.
> > i've created a project, 1 iteration, 1 story and 1 task inside that
> > story and i get 39 queries executed by going tohttp://127.0.0.1:8000/agilito/1/iteration/
> > and most of them are repeated ... i know that you work with postgresql
> > as your dev database so the impact may not be as great as when using
> > sqlite..
> > here go the queries that where executed:

The current checkout from github should fix a major part of this
problem. There's more efficiency changes coming up for the iteration
screen, but this should have a significant impact.

Regards,
Emile

[httpdss]

Hi Emile! somehow i didnt get the notification from google groups of
your replies so i just saw your replies :S
Thats great news !! i'll take a look at the github repo see what
happens...

BTW, the query logging i pasted is taken using the django-debug-
toolbar by robhudson (http://github.com/robhudson/django-debug-
toolbar)

Cheers,
Kenny

On Oct 22, 4:48 pm, Emiliano Heyns <emiliano.he...@gmail.com> wrote:
> On Oct 15, 9:02 am, Emiliano Heyns <emiliano.he...@gmail.com> wrote:
>
> > Hi,
>

[Emiliano Heyns]

On Oct 24, 7:56 pm, httpdss <ke...@belitzky.com> wrote:
> Hi Emile! somehow i didnt get the notification from google groups of
> your replies so i just saw your replies :S
> Thats great news !! i'll take a look at the github repo see what
> happens...
>
> BTW, the query logging i pasted is taken using the django-debug-
> toolbar by robhudson (http://github.com/robhudson/django-debug-
> toolbar)

I got the debug toolbar working, and _wow_. There's still some work to
do there. Thanks for pointing this out, I'm on it.

Emile

[Emiliano Heyns]

> I got the debug toolbar working, and _wow_. There's still some work to
> do there. Thanks for pointing this out, I'm on it.

This problem has been addressed in full now.

Full message at http://groups.google.com/group/ajellito-dev/browse_thread/thread/7cb6c3bc8c1c3184

I would kindly, _again_, request the group owners to step up and
remove the spam that's been flowing in. Alternately, I would request
members of this group that still have an actual interest in this
project subscribe to http://groups.google.com/group/ajellito-dev -- I
will be monitoring that group, mainly, and will be removing the spam
there myself until Google fixes the spam problem on Groups.

Regards,
Emile

[Christopher Johnson]

Hi Emiliano,

Sorry for delay in response. I was traveling for extended period and wasn't monitoring the list. I wish Google would get its act together - I am on at least half a dozen google groups and same story on every one...

I have changed the moderation settings (new member messages are moderated) and also added you as a moderator.

Hopefully this will slow or stop the spamming.

-c

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Agilito development" group.
To post to this group, send email to agili...@googlegroups.com
To unsubscribe from this group, send email to agilito-dev...@googlegroups.com
For more options, visit this group at http://groups.google.com/group/agilito-dev?hl=en
-~----------~----~----~----~------~----~------~--~---

--
Cofounder and CEO
ifPeople - Innovation for People
t: 678-608-3408 x16
www.ifpeople.net

** Check out our Plone demos! Get to know Plone or see how Plone integrates with salesforce.com CRM: http://www.ifpeople.net/learn/training **

[James Tauber]

On Dec 15, 2009, at 4:05 AM, Christopher Johnson wrote:
> I have changed the moderation settings (new member messages are moderated) and also added you as a moderator.
>
> Hopefully this will slow or stop the spamming.

I did this for all my lists and it works wonders.

James

[Emiliano Heyns]

On Mon, Dec 14, 2009 at 21:05, Christopher Johnson <c...@ifpeople.net> wrote:

Hi Emiliano,

Sorry for delay in response. I was traveling for extended period and wasn't monitoring the list. I wish Google would get its act together - I am on at least half a dozen google groups and same story on every one...

I have changed the moderation settings (new member messages are moderated) and also added you as a moderator.

Hopefully this will slow or stop the spamming.

Now if only someone could go through the list and actually delete the spam, which is currently the bulk of the group contents. As a mere moderator, I cannot do this. I need to be able to delete existing spam, or I'd rather not be moderator either; otherwise I'll still just encourage people to subscribe to the ajellito-dev list I'm managing.

Emile