Scaling application with paginated query using rownum

There was a case where one of the application modules getting slower day by day as the usage and data increased. The module is a mail like system where we show 20 items per page. The initial design was not that good, we fetched all the items from database and then paginated in application layer - 20 list item per page. This caused slower response for loading the inbox, specially for those people who had a large number of items.

Thought this can be improved if we fetch selective number of records from database. We show 20 records per page, so we should only try to bring 20 records, not all at the very beginning. For the first page, we will bring records from 1 to 20, for the next page (2nd page), we will bring records from 21 to 40 and so on. This helped a lot. The initial response time decreased from several minutes to just few seconds! I loved the response time graphs (see below) after we made the changes.


/** Old List query **/
SELECT mbox.id mbox_id, mbox.mailbox_type mbox_mailbox_type, mbox.read_date mbox_read_date, mbox.status mbox_status,...
FROM mailbox mbox
LEFT JOIN message msg on (mbox.message_id = msg.id)
LEFT JOIN user u ON (u.id = msg.sender_id )
LEFT JOIN recipient r on (r.message_id = msg.id AND r.idx=0)
LEFT JOIN user ur ON (ur.id = r.target_user_id)
WHERE mbox.user_id= :user
AND mbox.folder_id= :folder
AND msg.test = :test
AND msg.updated >= :dt
ORDER BY msg_updated DESC;

/** New paginated List query **/
SELECT *
  FROM (select /*+ FIRST_ROWS(20) */
  a.*, ROWNUM rnum
      from ( SELECT mbox.id mbox_id, mbox.mailbox_type mbox_mailbox_type, mbox.read_date mbox_read_date, mbox.status mbox_status,...
              FROM mailbox mbox
              LEFT JOIN message msg on (mbox.message_id = msg.id)
              LEFT JOIN user u ON (u.id = msg.sender_id )
              LEFT JOIN recipient r on (r.message_id = msg.id AND r.idx=0)
              LEFT JOIN user ur ON (ur.id = r.target_user_id)
              WHERE mbox.user_id= :user
              AND mbox.folder_id= :folder
              AND msg.test = :test
              AND msg.updated >= :dt
              ORDER BY msg_updated DESC ) a
      where ROWNUM <= 20 )
WHERE rnum  >= 1;


Similarly, the Count queries were improved fixing the count to 100. In that case, if there is more than 100 new items in inbox, we say 100+.

/** Old Count query **/
SELECT COUNT(id) inbox_lo
FROM mailbox mbx 
JOIN message msg ON (mbx.message_id = msg.id)
LEFT JOIN user l ON (l.id = msg.sender_id)
WHERE mbx.login_id = :login
AND mbx.folder_id = :folder
AND mbx.mailbox_type = :mailbox
AND mbx.status= :status
AND msg.test = :test
AND mbx.created >= :dt;

/** New Count query **/
SELECT /*+ FIRST_ROWS(100) */ COUNT(id) inbox_lo
FROM mailbox mbx 
JOIN message msg ON (mbx.message_id = msg.id)
LEFT JOIN user l ON (l.id = msg.sender_id)
WHERE mbx.login_id = :login
AND mbx.folder_id = :folder
AND mbx.mailbox_type = :mailbox
AND mbx.status= :status
AND msg.test = :test
AND mbx.created >= :dt
AND ROWNUM <= 100;

The response time improved amazingly - users now feel the difference!

***