{"action":"opened","issue":{"url":"https://api.github.com/repos/modc08/modc08/issues/89","labels_url":"https://api.github.com/repos/modc08/modc08/issues/89/labels{/name}","comments_url":"https://api.github.com/repos/modc08/modc08/issues/89/comments","events_url":"https://api.github.com/repos/modc08/modc08/issues/89/events","html_url":"https://github.com/modc08/modc08/issues/89","id":83861946,"number":89,"title":"Optimisation for source-heavy views","user":{"login":"franksc","id":8837821,"avatar_url":"https://avatars.githubusercontent.com/u/8837821?v=3","gravatar_id":"","url":"https://api.github.com/users/franksc","html_url":"https://github.com/franksc","followers_url":"https://api.github.com/users/franksc/followers","following_url":"https://api.github.com/users/franksc/following{/other_user}","gists_url":"https://api.github.com/users/franksc/gists{/gist_id}","starred_url":"https://api.github.com/users/franksc/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/franksc/subscriptions","organizations_url":"https://api.github.com/users/franksc/orgs","repos_url":"https://api.github.com/users/franksc/repos","events_url":"https://api.github.com/users/franksc/events{/privacy}","received_events_url":"https://api.github.com/users/franksc/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/modc08/modc08/labels/enhancement","name":"enhancement","color":"84b6eb"},{"url":"https://api.github.com/repos/modc08/modc08/labels/help%20wanted","name":"help wanted","color":"159818"}],"state":"open","locked":false,"assignee":{"login":"franksc","id":8837821,"avatar_url":"https://avatars.githubusercontent.com/u/8837821?v=3","gravatar_id":"","url":"https://api.github.com/users/franksc","html_url":"https://github.com/franksc","followers_url":"https://api.github.com/users/franksc/followers","following_url":"https://api.github.com/users/franksc/following{/other_user}","gists_url":"https://api.github.com/users/franksc/gists{/gist_id}","starred_url":"https://api.github.com/users/franksc/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/franksc/subscriptions","organizations_url":"https://api.github.com/users/franksc/orgs","repos_url":"https://api.github.com/users/franksc/repos","events_url":"https://api.github.com/users/franksc/events{/privacy}","received_events_url":"https://api.github.com/users/franksc/received_events","type":"User","site_admin":false},"milestone":null,"comments":0,"created_at":"2015-06-02T06:06:15Z","updated_at":"2015-06-02T06:06:15Z","closed_at":null,"body":"@craigpatten raised the noticeably slow loading of OAGR views which contain many sources – namely the Source index page and the OAGR home page, which loads all sources to populate them on the map. Some testing with [django-debug-toolbar](https://github.com/django-debug-toolbar/django-debug-toolbar) shows a horrifying number of individual SQL queries for these views.\r\n\r\nThe main pain point seems to be the call of [Source.get_datasets()](https://github.com/modc08/mytardis/blob/modc08/tardis/apps/acad/models.py#L84) for each source object in the database to lookup if the source is associated with a dataset the end user can see for their level of authentication, and only show sources that fall into this category. (This is not just a nicety for end users – showing sources associated with a dataset the end user can't see could expose potentially sensitive details about a currently non-public study.) Because there is no direct relationship between datasets and sources the query needs to traverse many tables (dataset, analysis ... sample, source) to look up required data. With 130-odd sources in the current body of data, this means many expensive and slow queries.\r\n\r\nOne idea that occurs to me is creating a SQL VIEW containing the results of the dataset → source query, and modifying .get_datasets() to use Django's [.raw()](https://docs.djangoproject.com/en/1.5/topics/db/sql/#performing-raw-queries) to query this view directly rather than using the Django ORM.\r\n\r\n@craigpatten @shundezhang @Li-ReDBox I am open to all brilliant ideas ..."}}