Comment 1 for bug 844877

Wow that is VERY SLOW...
Maybe Markus can help us look into the DB structure again :)

On Thu, Sep 8, 2011 at 4:59 PM, Michal Hruby <email address hidden> wrote:

> Public bug reported:
>
> Synapse is using various not-so-complex find_events queries to display
> recent activities, and it seems that especially a query for "All"
> category is taking much longer than any other query (~750ms with a DB
> with 200k events).
>
> Here's the output from zg with the debug_sql extension:
>
> [DEBUG - root] # parameters: {'event_templates':
> [Event([dbus.Array([dbus.String(u''), '1315492708863', dbus.String(u''),
> dbus.String(u''), dbus.String(u''), ''], signature=dbus.Signature('s')),
> [Subject([dbus.String(u''), dbus.String(u''), dbus.String(u''),
> dbus.String(u''), dbus.String(u''), dbus.String(u''), dbus.String(u''),
> dbus.String(u'')]), Subject([dbus.String(u''), dbus.String(u'!
> http://www.semanticdesktop.org/ontologies/2007/03/22/nfo#Folder'),
> dbus.String(u''), dbus.String(u''), dbus.String(u''), dbus.String(u''),
> dbus.String(u''), dbus.String(u'')]), Subject([dbus.String(u''),
> dbus.String(u'!
> http://www.semanticdesktop.org/ontologies/2007/03/22/nfo#Software'),
> dbus.String(u''), dbus.String(u''), dbus.String(u''), dbus.String(u''),
> dbus.String(u''), dbus.String(u'')])], dbus.Array([],
> signature=dbus.Signature('y'))])],
> 'max_events': dbus.UInt32(96L),
> 'order': dbus.UInt32(2L),
> 'return_mode': 1,
> 'self': <_zeitgeist.engine.main.ZeitgeistEngine instance at 0x1b66248>,
> 'sender': ':1.3604',
> 'storage_state': dbus.UInt32(2L),
> 'time_range': [1300977508860, 9223372036854775807]}
>
> [DEBUG - root] # database size: {'actor': 129,
> 'event': 192746,
> 'interpretation': 68,
> 'manifestation': 27,
> 'mimetype': 353,
> 'uri': 60265}
> [DEBUG - root] # BEGIN SQL QUERY #
> no pretty sql: SELECT id FROM event_view
> NATURAL JOIN (
> SELECT subj_id,
> max(timestamp) AS timestamp
>
> FROM event_view WHERE (+timestamp
> >= ? AND ((NOT (subj_interpretation = ?) AND NOT (subj_interpretation = ? OR
> subj_interpretation = ? OR subj_interpretation = ?))))
> GROUP BY subj_id)
> GROUP BY subj_id
> ORDER BY timestamp DESC
> LIMIT 96 : [1300977508860, 45, 46, 47, 43]
> took 0.712120s
> # END SQL QUERY
> [DEBUG - zeitgeist.engine] Found 96 events IDs in 0.723284s
>
> ** Affects: zeitgeist
> Importance: Undecided
> Status: New
>
> --
> You received this bug notification because you are a member of Zeitgeist
> Framework Team, which is subscribed to Zeitgeist Framework.
> https://bugs.launchpad.net/bugs/844877
>
> Title:
> Query execution slow
>
> Status in Zeitgeist Framework:
> New
>
> Bug description:
> Synapse is using various not-so-complex find_events queries to display
> recent activities, and it seems that especially a query for "All"
> category is taking much longer than any other query (~750ms with a DB
> with 200k events).
>
> Here's the output from zg with the debug_sql extension:
>
> [DEBUG - root] # parameters: {'event_templates':
> [Event([dbus.Array([dbus.String(u''), '1315492708863', dbus.String(u''),
> dbus.String(u''), dbus.String(u''), ''], signature=dbus.Signature('s')),
> [Subject([dbus.String(u''), dbus.String(u''), dbus.String(u''),
> dbus.String(u''), dbus.String(u''), dbus.String(u''), dbus.String(u''),
> dbus.String(u'')]), Subject([dbus.String(u''), dbus.String(u'!
> http://www.semanticdesktop.org/ontologies/2007/03/22/nfo#Folder'),
> dbus.String(u''), dbus.String(u''), dbus.String(u''), dbus.String(u''),
> dbus.String(u''), dbus.String(u'')]), Subject([dbus.String(u''),
> dbus.String(u'!
> http://www.semanticdesktop.org/ontologies/2007/03/22/nfo#Software'),
> dbus.String(u''), dbus.String(u''), dbus.String(u''), dbus.String(u''),
> dbus.String(u''), dbus.String(u'')])], dbus.Array([],
> signature=dbus.Signature('y'))])],
> 'max_events': dbus.UInt32(96L),
> 'order': dbus.UInt32(2L),
> 'return_mode': 1,
> 'self': <_zeitgeist.engine.main.ZeitgeistEngine instance at 0x1b66248>,
> 'sender': ':1.3604',
> 'storage_state': dbus.UInt32(2L),
> 'time_range': [1300977508860, 9223372036854775807]}
>
> [DEBUG - root] # database size: {'actor': 129,
> 'event': 192746,
> 'interpretation': 68,
> 'manifestation': 27,
> 'mimetype': 353,
> 'uri': 60265}
> [DEBUG - root] # BEGIN SQL QUERY #
> no pretty sql: SELECT id FROM event_view
> NATURAL JOIN (
> SELECT subj_id,
> max(timestamp) AS timestamp
>
> FROM event_view WHERE (+timestamp
> >= ? AND ((NOT (subj_interpretation = ?) AND NOT (subj_interpretation = ? OR
> subj_interpretation = ? OR subj_interpretation = ?))))
> GROUP BY subj_id)
> GROUP BY subj_id
> ORDER BY timestamp DESC
> LIMIT 96 : [1300977508860, 45, 46, 47, 43]
> took 0.712120s
> # END SQL QUERY
> [DEBUG - zeitgeist.engine] Found 96 events IDs in 0.723284s
>
> To manage notifications about this bug go to:
> https://bugs.launchpad.net/zeitgeist/+bug/844877/+subscriptions
>
> _______________________________________________
> Mailing list: https://launchpad.net/~zeitgeist
> Post to : <email address hidden>
> Unsubscribe : https://launchpad.net/~zeitgeist
> More help : https://help.launchpad.net/ListHelp
>