Why is it always hard work!!

[mousematt]

Hi, Need some help with the speed that my results are coming back from a query. Basically the below take around 350seconds to bring back 20 rows. The main table 'stillinger' has 100,000 rows in total and if I didn't limit the results on this query there could be 7000 rows. Any ideas?!

SELECT * FROM (
SELECT a.*, ROWNUM rnum FROM (
SELECT foretak.namn, stillinger.orgnr, kode_stillinger.navn, stillinger.fra_dato, stillinger.til_dato
FROM stillinger
INNER JOIN foretak ON (stillinger.orgnr=foretak.org_nummer)
INNER JOIN kode_stillinger ON (stillinger.stillkode=kode_stillinger.stillkode)
WHERE stillinger.pnr = '100513011'
AND flag='C'
ORDER BY stillinger.orgnr DESC
) a WHERE ROWNUM <= 20
)WHERE rnum >= 1;

 

[Dagon]

It's generally not hard work if your table design is correct and you have appropriate cardinality in join conditions. The fact that you are incorporating lots of rownum conditions to limit the data set implies you may have many to many join conditions and are getting more rows returned than you really need. That, coupled with the order by in the inline view (which forces Oracle to sort the entire data set before it get give you any rows at all) would make for a poor query.

Review your table join conditions. At least one of the joins from Stillinger to foretak and Stillinger to kode_stillinger should be unique. If they are both non-unique, then you will have a cartesian product. Also, what is the point of the "rnum >= 1" condition ? Rnum derives from rownum, so will always be >= 1.

Other advice:

a) check stats on all the tables
b) get explain plan of the query (or sql*trace)
c) an index on stillinger.pnr may help if the number of rows in the table with pnr = '100513011' is low

 

[mousematt]

Hi just one point before I think hard about what you said, the reason I'm limiting the rows is so that i can setup paging, I need all 7000 results but only 20 at a time, this was the only way I could find to do it.

I'm doing this for an ASP.Net page, I'm a .Net/MS SQL developer no idea about Oracle -- new job new challenges though!

 

[Dagon]

That means you would have to run the query 350 times to get the 7000 rows 20 rows at a time. That is completely ridiculous. I don't know anything about .net, but there has to be a better way than that. You should be able to do something like get the rows back through a cursor and just display them 20 at a time.

 

[Beilstwh]

SEE THE FOLLOWING LINK

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

Bill
Oracle DBA/Developer
New York State, USA

 

[mousematt]

Dagon, if I pulled back all 7000 rows into a .net page it would keep all of that in memeory, times that by the 300-400 concurrent users and our performance will suffer. This is exactually why I'm trying to do this. Also our live oracle server is slow as hell anyway, I don't think our DBA knows what he is doing and my knowledge of Oracle is almost 0!

I followed Beilstwh's link and have got this so far:

select *
from (
select /*+ first_rows(25) */
stillinger.orgnr, stillinger.fra_dato, stillinger.til_dato, stillinger.pnr,
row_number() over
(order by stillinger.pnr) rn
from stillinger
)
where rn between 21 and 40
order by rn;


but now I need to work out how to add in "where stillinger.pnr = '100513011'" and also don't know if this would be possible but I need to have to two joins as above. Sorry for my complete and utter lack of knowledge.