I'm creating a view for the media browser (Media module) and this requires me to sort the files on the newest first (desc). Using the performance statistics I found out this view had a huge view render time, around 5 seconds.
To exclude any possible problems caused by the media module I made a new view that simply shows files and just their title. Sorting this view asc the view render takes 13.14 ms, sorting it desc view render takes 37693.03 ms.
When I make a view displaying normal nodes, showing only the title, sorting it desc or asc doesn't make any real difference.

For completeness sake, the query for the desc ordered view

SELECT file_managed.filename AS file_managed_filename, file_managed.uri AS file_managed_uri, file_managed.timestamp AS file_managed_timestamp
FROM
{file_managed} file_managed
ORDER BY file_managed_timestamp DESC
LIMIT 100 OFFSET 0

The asc query:
SELECT file_managed.filename AS file_managed_filename, file_managed.uri AS file_managed_uri, file_managed.timestamp AS file_managed_timestamp
FROM
{file_managed} file_managed
ORDER BY file_managed_timestamp ASC
LIMIT 100 OFFSET 0

How can I improve the view render time for a desc sorted file view?

Comments

If this is unique to items provided by media, then this ticket probably needs to be filed against media, since presumably its handlers are what are causing the slowdown.

You should provide the very simplest view you can come up with that exhibits the problem and attach an export.

My goal is to use a desc ordered list of files in a Media Browser, but this problem also occurs when just showing files. Since it only query's core's file_managed table I can't see how that has anything to do with contrib modules like Media. Please correct me if I'm assuming wrong.

The export of my simple test view:

$view = new view();
$view->name = 'media_browser_test';
$view->description = '';
$view->tag = 'default';
$view->base_table = 'file_managed';
$view->human_name = 'Media browser test';
$view->core = 7;
$view->api_version = '3.0';
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */
/* Display: Master */
$handler = $view->new_display('default', 'Master', 'default');
$handler->display->display_options['title'] = 'Media browser test';
$handler->display->display_options['use_more_always'] = FALSE;
$handler->display->display_options['access']['type'] = 'perm';
$handler->display->display_options['cache']['type'] = 'none';
$handler->display->display_options['query']['type'] = 'views_query';
$handler->display->display_options['exposed_form']['type'] = 'basic';
$handler->display->display_options['exposed_form']['options']['reset_button_label'] = 'Opnieuw instellen';
$handler->display->display_options['pager']['type'] = 'full';
$handler->display->display_options['pager']['options']['items_per_page'] = '100';
$handler->display->display_options['pager']['options']['tags']['first'] = '« eerste';
$handler->display->display_options['pager']['options']['tags']['previous'] = '‹ vorige';
$handler->display->display_options['pager']['options']['tags']['next'] = 'volgende ›';
$handler->display->display_options['pager']['options']['tags']['last'] = 'laatste »';
$handler->display->display_options['style_plugin'] = 'grid';
$handler->display->display_options['row_plugin'] = 'fields';
/* Veld: Bestand: Naam */
$handler->display->display_options['fields']['filename']['id'] = 'filename';
$handler->display->display_options['fields']['filename']['table'] = 'file_managed';
$handler->display->display_options['fields']['filename']['field'] = 'filename';
$handler->display->display_options['fields']['filename']['label'] = '';
$handler->display->display_options['fields']['filename']['alter']['word_boundary'] = FALSE;
$handler->display->display_options['fields']['filename']['alter']['ellipsis'] = FALSE;
$handler->display->display_options['fields']['filename']['link_to_file'] = TRUE;
/* Sort criterion: Bestand: Upload date */
$handler->display->display_options['sorts']['timestamp']['id'] = 'timestamp';
$handler->display->display_options['sorts']['timestamp']['table'] = 'file_managed';
$handler->display->display_options['sorts']['timestamp']['field'] = 'timestamp';
/* Display: Page */
$handler = $view->new_display('page', 'Page', 'page');
$handler->display->display_options['defaults']['hide_admin_links'] = FALSE;
$handler->display->display_options['path'] = 'media-browser-test';
$translatables['media_browser_test'] = array(
  t('Master'),
  t('Media browser test'),
  t('more'),
  t('Apply'),
  t('Opnieuw instellen'),
  t('Sort by'),
  t('Asc'),
  t('Desc'),
  t('Items per page'),
  t('- All -'),
  t('Offset'),
  t('« eerste'),
  t('‹ vorige'),
  t('volgende ›'),
  t('laatste »'),
  t('Page'),
);

Please let me know if I need to share more information.

I've bin playing around with my modules, Media: Flickr seems to be the culprit. This makes the render time around 41384.54 ms instead of 45.21 ms.
I've made a issue there: MediaFlickrStreamWrapper causes huge view render time when ordering files desc

Status:Active» Fixed

So basically this isn't a problem of views.

Correct, it's the Media module or possibly more specifically the Media:Flickr module.

Status:Fixed» Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

Issue summary:View changes

Added query's