Efficient querying with the EventQ Reporting API

Here's a few guidelines for building efficient EventQ Reporting API queries.

Cutting vs Building

Unlike a SQL query, which connects separate tables together to form a result, the EventQ Reporting API starts with the full set of all activities, and is cut down to the size of the result by successively applying query restrictions. This difference makes query optimization quite different between SQL and NoSQL.

Ordering guidelines

Some query commands will result in larger reductions to the than others. This of course also depends on the range applied to the query. In example: Restricting to a week of activities using “after 1.week.ago” will make a very small set of activities to work on, compared to “after 1.year.ago” which will include a large number of activities. In general however, the commands here are listed by most efficient to least efficient in scoping the query result:

Good:

:before ,  type :  :time
:after ,  type :  :time
:restrict_to ,  type :  :choice ,  choices :  %w(builds commits reviews work_items custom)
:in_source ,  type :  :json
:in_repository ,  type :  :string
:in_project ,  type :  :json

Mid:

:associated_to ,  type :  :json
:tagged_as ,  type :  :json
:offset ,  type :  :integer

Poor:

:associations
:field_contains ,  type :  :json
:sort_by ,  type :  :json
:has_any ,  type :  :string
:has_none ,  type :  :string

Returning command (must be run last)

:limit ,  type :  :integer
:timeseries ,  type :  :duration
:group_by_field ,  type :  :string
:fields ,  type :  :json
:count

Use cases and examples

:in_project ["proj1211"]
after 14.days.ago
restrict_to commits
field_contains ["settled",[true]]
has_any "reviews"

Exceptions in ordering and other exceptional behaviours to note

Limit: The limit command is best used at the end of the set. Using it before the end can result in more than the limit of items being returned at the end of the query.

Tagged_as: Is specifically for custom activities.