Home » SQL & PL/SQL » SQL & PL/SQL » performance tuning on paging result set
performance tuning on paging result set [message #18435] Thu, 31 January 2002 00:55 Go to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
I have a table MEMBERSHIP which consist of 440000 record and the sql below to provide subset of result by paging function while the sql takes me 22 sec to finish on a SUN E450 with 2G RAM config machine. Is ther any other better method to achieve the same function ?

select MMBR_NUM,
MMBR_NAME,
from ( select a.MMBR_NUM,a.MMBR_NAME,ROWNUM rn
from MEMBERSHIP a
where a.mmbr_lang= 'en'
) where rn > 20
and rn <= 40

and upper(a.MMBR_NUM) like '2%'
;

After several try I have a trick which is to put the rownum upperbound condition inside the subquery as below while it gives me thundering performance on several pages in the front while the performance degrade dramatically when the number goes larger. Pls help

select MMBR_NUM,MMBR_NAME,
from ( select a.MMBR_NUM,a.MMBR_NAME,ROWNUM rn
from MEMBERSHIP a
where a.mmbr_lang= 'en' and ROWNUM <= 40)
where rn > 20 and rn <= 40
and upper(a.MMBR_NUM) like '2%'
;
Re: performance tuning on paging result set [message #18437 is a reply to message #18435] Thu, 31 January 2002 01:18 Go to previous messageGo to next message
pratap kumar tripathy
Messages: 660
Registered: January 2002
Senior Member
hi,

try this

select MMBR_NUM,MMBR_NAME,
from
(select a.MMBR_NUM,a.MMBR_NAME,ROWNUM rn
from MEMBERSHIP a
where a.mmbr_lang= 'en' and
and upper(a.MMBR_NUM) like '2%'
...put all other condition here......
and ROWNUM <= 40)
where rn > 20 ;

cheers
pratap
Re: performance tuning on paging result set [message #18439 is a reply to message #18435] Thu, 31 January 2002 03:01 Go to previous messageGo to next message
Mike
Messages: 417
Registered: September 1998
Senior Member
For what do you need this?
Is there any performance difference without the upper function (which is not needed)?
How many data fit the criteria MMBR_NUM like '2%' and mmbr_lang= 'en' ?
Are there any indexes?
Re: performance tuning on paging result set [message #18445 is a reply to message #18435] Thu, 31 January 2002 04:54 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
What ?

I wanna retrieve record page (per 20 record each) by page which fulfill my criteria.

or you can treat no criteria at all but just showing all record page by page.
Re: performance tuning on paging result set [message #18447 is a reply to message #18435] Thu, 31 January 2002 05:58 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
no. I am writing java program
Re: performance tuning on paging result set [message #18449 is a reply to message #18447] Thu, 31 January 2002 06:35 Go to previous message
Mike
Messages: 417
Registered: September 1998
Senior Member
Why don't you solve the problem in java?
Wouldn't it be easier to use the next() and previous() functions of the class ResultSet to get the next/previous 20 records?

Maybe have also a look at:
http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/java.817/a83724/resltse5.htm#1023642
Previous Topic: REFERENCES and FOREIGN KEY
Next Topic: I can't have my SQL prompt after excuting my stored procedure
Goto Forum:
  


Current Time: Thu Mar 28 16:51:29 CDT 2024