Question:
SQL Pagination Help needed?
rohit d
2009-02-16 08:55:25 UTC
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


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#
Three answers:
2009-02-16 09:03:48 UTC
The only thing I can see without going extremely deep into it is becareful with you do a select * from anything. The more results you want pulled back from the database, the longer its going to take.



Also, make sure you put your where clauses in order for how simple it would be for the database to compare. Varchars are the slowest comparision and bits are the fastest. If you can put your bits at the beginning of the where clauses, then it should run faster.



1 more thing, when you use derived tables, its going to have to execute that select statement for each row of the parent. So if your first select statement returned 10 rows, then the next derived table's select statement would have been called 10 times, but if that ones returned 10 more rows, then its derived table must be called. It gets really big, really quick. So if each of the select statements returned 10 rows, they query would have selected over 1000 rows.



You might want to declare some temporary table to use.
AK
2009-02-16 09:02:45 UTC
You need to clean up your query BIG time. You have 4 inner queries. Try and make them all into one query
Neeraj Yadav♥
2009-02-16 09:37:00 UTC
I think oracle has something called "dual"



Use it for deciding number of pages to show.I will save your execution time and the no. code lines.



forth:

http://zacster.blogspot.com/2008/09/efficently-using-oracle-sequences-which.html



Can modify and use according to your own logics



Cheers:)


This content was originally posted on Y! Answers, a Q&A website that shut down in 2021.
Loading...