Looks like SQLite's optimizer can't handle the subqueries in event_view, so we should restrict their usage in WHERE clauses.
Bluebird revision 271 fixes this (please confirm). -- 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 : zeitgeist@lists.launchpad.net Unsubscribe : https://launchpad.net/~zeitgeist More help : https://help.launchpad.net/ListHelp
