Serverside pageing results in full tablescan

[Richard Koslik]

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.

[Jan Blessenohl]

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

[Richard Koslik]

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.

[Jan Blessenohl]

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