Hi gents, I just want to show DataGrid with my table to a user with support of sorting and filtering by arbitrary column. As my table has ~200k records I'd like to implement data virtualization (paged access). Please, give me recommendations how to do pagination in the best way.
The following is my diging into it... - OFFSET is slow at the end of the table (I understand why, it is documented, no questions). - I've read this article http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor and want to use WHERE, but what if 'title' is not unique? - This is considered here: http://stackoverflow.com/questions/21082956/sqlite-scrolling-cursor-how-to-scroll-correctly-with-duplicate-names and yes, we can use title+rowid as lasttitle. But... it looks too complex to be 'best practice' pattern. And is x100 slower than simple WHERE. SELECT * FROM "MainBooksView" WHERE (Title = 'fff' AND Id > 101985) OR Title > 'fff' ORDER BY Title LIMIT 30 Query time: 0.102s I have index for the Title column. SELECT * FROM "MainBooksView" WHERE Title > 'fff' ORDER BY Title LIMIT 30 Query time: 0.001s SELECT * FROM "MainBooksView" ORDER BY Title LIMIT 30 OFFSET 120000 Query time: 1.133s So, next what I want is Oracle ROWNUM analog, aka counter() function. Here is a proposed implementation: http://www.sqlite.org/cvstrac/tktview?tn=4004 but it was rejected. If you would say I want something strange or exotic, please give me fast OFFSET implementation and I would be happy :) Thanks, Max _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
