zeitgeist team mailing list archive

[Michal Hruby <michal.mhr@xxxxxxxxx>]

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