On 27 maalis, 10:10, Stefano Crosta <stefano.cro...@gmail.com> wrote: > On Tuesday, March 26, 2013 10:47:34 PM UTC+1, Petite Abeille wrote: > > > On Mar 26, 2013, at 10:03 PM, Alex Gaynor <alex....@gmail.com<javascript:>> > > wrote: > > > > For what it's worth, SQL2011 does define OFFSET, finally. > > > Perhaps worthwhile mentioning as well : > > > "Do not try to implement a scrolling window using LIMIT and OFFSET. Doing > > so will become sluggish as the user scrolls down toward the bottom of the > > list." > > -- Scrolling Cursor, What Not To Do > >http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor > > > Just saying... > > This is already getting slightly off-topic wrt the original discussion on > EXISTS/IN, so I allowed myself to change the subject. > > But this is another important matter I think, and I've already seen mention > of server-side cursors in these threads.
Server side cursor would be useful in many situations. However, it is hard to implement server side cursors in a way the ORM could use them automatically. See https://code.djangoproject.com/ticket/16614 for details. The usual problem with LIMIT/OFFSET is pagination and going deep into pages list. Server side cursors would only help if you could somehow keep the same cursor open between requests for different pages. The solution to large resultset pagination is to sort by some index you have, and then when you go to next page, you do this: SomeModel.objects.filter(indexedcol__gt=prev_pages_last_obj.indexedcol).order_by('indexedcol') [0:PAGE_SIZE] This operation can use the index and is a very efficient way to retrieve pages. The downside is that this only works for unique indexed columns (or "unique enough" that duplicates do not matter in practice). Also, you can't easily give page numbers for pagination, you can only have links "first, previous, next, last". Still, if you have a lot of objects then normal pagination of "count all, give links by page numbers" simply does not scale. The count(*) itself can be too expensive for large resultsets. Having in-built support for this type of pagination is something I would like to see in Django. If you want to implement an automatically refreshed scrolling window using AJAX and want the ability to sort by any column and do so efficiently, then you will need to use server side cursors and have some way to get the same cursor back for different AJAX requests. In practice you would need some sort of connection pool where you could store a connection for reuse, and ask the same connection back when next request arrives. This seems complex to implement correctly and doesn't seem like something that belongs into Django... - Anssi -- You received this message because you are subscribed to the Google Groups "Django developers" group. To unsubscribe from this group and stop receiving emails from it, send an email to django-developers+unsubscr...@googlegroups.com. To post to this group, send email to django-developers@googlegroups.com. Visit this group at http://groups.google.com/group/django-developers?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
