Serverside pageing results in full tablescan

4 posts, 0 answers
  1. Richard Koslik
    Richard Koslik avatar
    71 posts
    Member since:
    Apr 2010

    Posted 11 Nov 2010 Link to this post

    Hello,

    i am using a oracle db.
    During testing of my serverside sorting and pageing functionality of the RadGridView i have found that the orm does generate a select statement which results in a full tablescan.

    Following the serverside linq statement to get one page of the dictionary table:
    query = from c in this.DataContext.TXTs
                             .Skip((iPage - 1) * 20)
                             .Take(20)
                             .OrderBy(sSortOrder)
                            select c;

    Following the SQL statement from the database session view in the oracle enterprise manager:
    SELECT *
        FROM (SELECT a.*, ROWNUM oa_rownum
                  FROM (SELECT a.txt_s_key col1, a.txt_i_lng_nr col2, 
                               a.txt_i_tag col3, a.txt_s_text col4
                            FROM txt a
                            ORDER BY a.txt_s_key) a
                  WHERE ROWNUM <= :take)
        WHERE oa_rownum > :skip

    This is a very time consuming way if my dictionary have millions of rows and each page request makes a full table scan.

    My target is to fetch only 20 records per page and have the page switch take less than 1 second.
    Is there any other way to do the pageing, or do is something wrong?

    Have anyone a solution?
    Thanks Richard.
  2. Jan Blessenohl
    Admin
    Jan Blessenohl avatar
    707 posts

    Posted 11 Nov 2010 Link to this post

    Hello Richard Koslik,
    Yes, looks like our generated sql is not ideal for bigger tables. Can you tell me the optimal query in your case? Is is someting liks:

    select * from (select rownum, e.empno from scott.emp e) where rownum between(1) and (3) order by rownum

    Best wishes,
    Jan Blessenohl
    the Telerik team
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
  3. DevCraft banner
  4. Richard Koslik
    Richard Koslik avatar
    71 posts
    Member since:
    Apr 2010

    Posted 15 Nov 2010 Link to this post

    Hello,

    i have done some benchmark test with 3 different methods but it seems there is no better way to do...
    Your version:
    SELECT *
        FROM (SELECT a.*, ROWNUM oa_rownum
                  FROM (SELECT TXT_S_TEXT
                            FROM TXT a
                            ORDER BY TXT_S_KEY ASC , ROWNUM ASC) a
                  WHERE ROWNUM <= 1400020)
        WHERE oa_rownum >= 1400000;
    takes about 6 secods...
    Other Version 1:
    SELECT * FROM (
                SELECT TXT_S_TXT,
                       RANK() OVER (ORDER BY TXT_S_KEY ASC , ROWNUM ASC )  RN
                FROM TXT )
        WHERE RN between 1400000 and 1400020;
    takes about 7 seconds...
    Other Version 2:
    SELECT * FROM (
      SELECT
        ROW_NUMBER() OVER (ORDER BY TXT_S_KEY ASC) AS rn,
        TXT_S_TXT
      FROM TXT
    )
    WHERE rn >= 1400000 AND rn <= 1400020;
    takes about 6 seconds.

    I have alo tried to use the rowid in the inner select and then use it to join with but i think it was not faster...
    I have also looked at the source code of the open source orm's, but the make it the same way for oracle.

    So if you dont have an flash of genius, we have to work this way...

    regards Richard.
  5. Jan Blessenohl
    Admin
    Jan Blessenohl avatar
    707 posts

    Posted 15 Nov 2010 Link to this post

    Hello Richard Koslik,
    Thanks for your effort, this was our feeling so far as well, but there are always people around with more specific database know how.

    7 seconds is very bad, if your ids are sequencial and without holes you might consider to implement the paging by yourself base on id values.

    All the best,
    Jan Blessenohl
    the Telerik team
    See What's New in Telerik OpenAccess ORM in Q3 2010!
    Monday, November 15, 11 am Eastern Time: Register here>>
    Monday, November 15, 10 pm Eastern Time: Register here>>
Back to Top