0

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
Improve this question
5
  • What platform (MySQL, SQL Server 2000, SQL Server 2005, SQL Server 2008)? The reason I ask is that there are better ways to do pagination in the more modern databases. Commented Feb 18, 2009 at 20:16
  • I believe this is Oracle because it's a duplicate of a couple of OTN threads forums.oracle.com/forums/message.jspa?messageID=3284391#3284391 and forums.oracle.com/forums/message.jspa?messageID=3278422#3278422 Commented Feb 18, 2009 at 20:26
  • You are losing the row data, when you don't select it out in from Y, but then the total count would be wrong. Commented Feb 18, 2009 at 22:02
  • eg. select x.* FROM ( select y.*, count( * ) from y ) x Commented Feb 18, 2009 at 22:04
  • What indexes are in place? You should likely be indexing p.lastname and p.firstname as well as the fields you are joining on. Commented Feb 18, 2009 at 22:10

7 Answers 7

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

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

Improve this answer

1 Comment

In oracle you can't. See stackoverflow.com/questions/1986998/resultset-to-pagination/…
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#
Improve this answer

Comments

1

What database are you using? If Oracle the ideas suggested by others 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]
Improve this answer

Comments

0

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.

Improve this answer

Comments

0

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

Improve this answer

Comments

0

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.

Improve this answer

Comments