Pagination help in SQL

Question

Hi,

The below inner SELECT returns huge amount of rows (1000000+) and the outer SELECTs(alpha BETWEEN #startRec# AND #endRec#) is used for PAGINATION to display data with 25 in each page.

Issue is:-This PAGINATION done below is very slow and slows the entire display of data.So could all please help me on doing this below pagination in a BETTER WAY? COde about pagination would be best.

**I am very sorry to put in this way but i am very new to Pagination concepts and so need your help.

/*********ORIGINAL QUERY ****/
SELECT
*
FROM 
(
SELECT
beta.*, rownum as alpha
FROM
(
SELECT 
p.lastname, p.firstname, porg.DEPARTMENT,
porg.org_relationship,
porg.enterprise_name,
(
SELECT 
count(*) 
FROM 
test_person p, test_contact c1, test_org_person porg 
WHERE 
p.p_id = c1.ref_id(+) 
AND p.p_id = porg.o_p_id 
$where_clause$


) AS results
FROM 
test_person p, test_contact c1, test_org_person porg
WHERE 
p.p_id = c1.ref_id(+) 
AND p.p_id = porg.o_p_id 
$where_clause$ 


ORDER BY 
upper(p.lastname), upper(p.firstname)
) beta
)
WHERE
alpha BETWEEN #startRec# AND #endRec#

---

My tried implementation below

---

(1)The inner most query..is the 1st QUERY fetching the data. (2)Then,we do a total COUNT on the above data.

Now,main issue is running the query goes on forever....and finally i have to forcibly cancel it. I feel there is something missing in the below query for which it hangs off.

Also,I came to know doing the COUNT outside is the best approach for performance.So,could you please correct the query below so that I am able return the COUNT *** DATA using Pagination,rownum etc.Mainly with the aliases below,rownum and getting data.

select * from 
( select x.* ,rownum rnum 

from ( SELECT 
count(*) as results /****2nd QUERY is OUTSIDE to get total count**/

Question is here,how do i access the data selected inside the 1st query below

from ( /****1st query to SELECT data***/


SELECT 
p.lastname, p.firstname, porg.DEPARTMENT,
porg.org_relationship,
porg.enterprise_name

FROM 
t_person p, t_contact c1, t_o_person porg 
WHERE rownum <10
and
p.person_id = c1.ref_id(+) 
AND p.person_id = porg.o_person_id 



ORDER BY 
upper(p.lastname), upper(p.firstname)



) y ------------------>alias defined Y from data of the 1st query


)x ------------------>alias defined X 
where rownum <= 20 )
where rnum >= 1

Answer 1

I usually do this as two separate queries, e.g.,:

-- get page of data
SELECT *
FROM
(
    SELECT 
     p.lastname, p.firstname, porg.DEPARTMENT,
     porg.org_relationship,
     porg.enterprise_name
    FROM 
     test_person p, test_contact c1, test_org_person porg
    WHERE 
     p.p_id = c1.ref_id(+) 
     AND p.p_id = porg.o_p_id 
     $where_clause$ 
    ORDER BY 
    upper(p.lastname), upper(p.firstname)
) beta
WHERE
rownum BETWEEN #startRec# AND #endRec#

--get total count
SELECT count(*) as Count
FROM 
    test_person p, test_contact c1, test_org_person porg
WHERE 
    p.p_id = c1.ref_id(+) 
    AND p.p_id = porg.o_p_id 
    $where_clause$

You could also return the total count in the first row of data in your results, like this:

SELECT null, null, null, null, null, count(*) as Count
FROM 
    test_person p, test_contact c1, test_org_person porg
WHERE 
    p.p_id = c1.ref_id(+) 
    AND p.p_id = porg.o_p_id 
    $where_clause$ 

UNION ALL

SELECT *
FROM
(
    SELECT 
     p.lastname, p.firstname, porg.DEPARTMENT,
     porg.org_relationship,
     porg.enterprise_name, null
    FROM 
     test_person p, test_contact c1, test_org_person porg
    WHERE 
     p.p_id = c1.ref_id(+) 
     AND p.p_id = porg.o_p_id 
     $where_clause$ 
    ORDER BY 
    upper(p.lastname), upper(p.firstname)
) beta
WHERE
rownum BETWEEN #startRec# AND #endRec#

Answer 2

To do pagination quickly, you need to limit the query results returned. eg. in mysql you can use limit and calc_rows.

You'd have to check your DB, however it'd be easier to break those 2 into separate queries if you don't have those helper functions.

Answer 3

Maybe I've missed something, but have you looked into use the LIMIT and OFFSET clauses? http://www.sql.org/sql-database/postgresql/manual/queries-limit.html

Answer 4

These are specifically intended for ASP, but can be adapted without much trouble: http://databases.aspfaq.com/database/how-do-i-page-through-a-recordset.html Personally, I implemented the "#Temp table" stored procedure method when I recently needed a paging solution.

Answer 5

My suggestion is :

• Create an index on test_person by lastname + firstname (in this order)
• If possible, remove the upper functions (some DBs allow creating indexes using functions)
• Remove the external SELECT and do the pagination in the client (not in DB)

I suspect that the internal subquery must be resolved first, and that's costly if there are no proper indexes. Usually ordering by computed columns do not use indexes, temporal tables are created etcetera.

Cheers

Answer 6

What database are you using? If he is using Oracle the above ideas will not work, Oracle does not support the LIMIT syntax for SQL.

For Oracle you wrap your query in this syntax: SELECT * FROM (SELECT a.*, ROWNUM rnum FROM ( [your query] ) a WHERE ROWNUM <= [endRow] ) WHERE rnum >= [startRow]

Answer 7

In Oracle there are a couple of options:

1. Using ROWNUM in an inner query with a wrapping to get the pagination (as you've tried)
2. Using analytic functions.

Both approaches have been described well by Tom Kyte:

http://www.oracle.com/technology/oramag/oracle/07-jan/o17asktom.html

Hope this helps.