This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes Optimizing queries Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Optimizing queries" Watch "Optimizing queries" New topic
Author

Optimizing queries

Mallika Kumar
Ranch Hand

Joined: Feb 15, 2001
Posts: 61
Hi,
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.
Thanks.
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.
Dan
[This message has been edited by Daniel Dunleavy (edited May 21, 2001).]
 
Don't get me started about those stupid light bulbs.
 
subject: Optimizing queries
 
Similar Threads
SQL Query Issues: Avoiding cartesian product and right JOIN syntax
Building a query for Tree
join and in
Old style SQL vs new style SQL
hibernate migration