General discussion

Locked

How to improve the query speed?

By Jason Dong ·
How should I consider to improve query speed in oracle9i.
Thanks for the suggestion!

This conversation is currently closed to new comments.

4 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

How long have you got ?

by Tony Hopkinson In reply to How to improve the query ...

They write books about this topic.
Your going to have to hand out a bit more info, otherwise I'm going to wear the letters off my keyboard.
Like what query, table structures, available indexes, sorting, any use of functions packages et al. Triggers to cope with. That's just the language side you'd have to talk to someone else about optimising oracle's performance itself, and they'd need to know the OS

Collapse -

Comments about my question.

by Jason Dong In reply to How long have you got ?

Thanks for your suggestion.
Actually,I am a junior java programmer.I use JDBC to connect oracle 9i datebase frequently.
Here is more info about my question:

OS:RedHat Linux9.0

Datebase:oracle 9i

table structures:

CUSTOMERID NUMBER(10)(pk),
FIRST_NAME VARCHAR2(100),
LAST_NAME VARCHAR2(100),
PASSWORD VARCHAR2(100),
EMAIL VARCHAR2(100),
COMPANY VARCHAR2(100),
JOB_ROLE NUMBER(FK),
CITY NUMBER(FK),
ADDRESS VARCHAR2(200),
ZIPCODE NUMBER,
COUNTRY NUMBER(FK),
TELPHONE VARCHAR2(100),
CELLPHONE NUMBER,
FAX VARCHAR2(50),
BUDGET NUMBER,
EMPCOUNT NUMBER,
PURCHASE_SCOPE NUMBER

indexes on: customerid.

my question is that I have to get all records and two means I have,but I don't know which one is better.
The first way is that I load all records by sql statment "select * from customers" and then make up every record as a javabean then store them into a vector.At last,I use iterator to iterate the vector to get each element in JSP.

And the second way is that I load all records by sql statement "select customerid from customers" and then store them into a long type array.At last,I use another method which load a special record by sql statement "select * from customers where customer = ?" to load every record in JSP.

So I need some suggestion about which one is better Or you have other perfect means.

Thanks for your suggestions!

Collapse -

Well not a structural problem then

by Tony Hopkinson In reply to Comments about my questio ...

My prefered method if I can afford it is, would be a collection of items with one populate method in the list class, that instantiates an item for each record in the result. There is an overhead on the server for parsing the query and then initiating controlling the comms to and from the server. You can measure that though reasonably easily, but less queries equals less parsing.

However a very wide record, or a great many records might be too much of hit on start up, also there may be a question of how often the data you've loaded up is out of date. Insert & Deletes vs updates. If the only normal way of changing the content of the table is through your classes then that is not an issue.
The second method you mention does attack the width (data per record) problem, but at the cost of issuing a second query.
If you've got all the customers, how often will you require all the data on all the customers at once is the question to be answered.
I've had some good results in the past with a combination of the methods.
Load up say customerID and Last_Name in one pass, then have a method to get the rest of the data when it's required on each item.
If Not FullyPopulated then getrestofdata
sort of thing.
Another way round any problems would be a front end interface ,say alpha name search or select company from a drop down.
You'll want to add some more indexes for that.

Last but not least , ordering. The above is almost certainly going to come out in customer_ID order.
If you wanted it by Last_Name, First_name. Index those fields and oracle will use those indexes as the sort. No indexes and you get two passes one to get the data in customer_id order and then one to sort it by the Order By clause. Very expensive that and done before you have a bean to your name.

There's no simple answer to your question. Do a couple of barebones attempts one for each method. Do enough in them to give it a bit of hammer and record how long it takes. Once you have your average read time off you go.

P.S. Do this in the 'real' environment. With the machine your client is going to use with the database where it's expected to be. I've seen a few people surprised at the dramatic drop in performance, when they ran their code on a slow machine with a quarter of the memory on a heavily used 10M network.

I'm not a java aware so I can't help you on the specifics, but I've done similar stuff in C, Delphi and VB. I can't really help you though until you know where the problem is, you never know there might not be one.

P.P.S
Nearly forgot, if the live database is expected to have 1000s of customers and you've got six in your test database ....

Not being funny, but I once got the job of optimising a select query that was meant to run in a web page, kept timing out even though it had worked in test.

Took 45 minutes to execute the select statement and that was Oracle 8 on a Solaris in the live system!

Collapse -

Thank you very mych!!!

by Jason Dong In reply to Well not a structural pro ...

Thank you very much for your answer.
Idea is more important than result.
Give my best wishes to you!
Thanks again!

Back to Software Forum
4 total posts (Page 1 of 1)  

Related Discussions

Related Forums