File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Optimizing queries Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Optimizing queries" Watch "Optimizing queries" New topic

Optimizing queries

Mallika Kumar
Ranch Hand

Joined: Feb 15, 2001
Posts: 61
I have a query tool which gets results based on inputs provided by users. I have several different SQL JOIN statements involving 6-8 tables in each query. Some involve outer joins too. I would like to know what steps should be taken to make these queries efficient and quick.
I have been using the concept of having the smallest table first in the FROM clause, and the largest(in terms of rows) at the end of FROM clause. Alongwith this, I'm trying to put the most important JOIN CONDITION first in the WHERE clause. I'm using Oracle 8i, and PreparedStatements to execute these queries.
Any suggestions would be helpful.
Daniel Dunleavy
Ranch Hand

Joined: Mar 13, 2001
Posts: 276
I think the most important thing to look at is your indexes. Which indexes to create, and to make sure they are used when the sql executes. Perhaps using HINTS in the SQL if you think Oracle will not choose the quickest path. If you have very small tables, you don't need indexes because the entire table will be pulled in.
[This message has been edited by Daniel Dunleavy (edited May 21, 2001).]
I agree. Here's the link:
subject: Optimizing queries
It's not a secret anymore!