File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Performance and the fly likes Performance of SQL Query 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 » Java » Performance
Bookmark "Performance of SQL Query" Watch "Performance of SQL Query" New topic
Author

Performance of SQL Query

Syed Hussain
Ranch Hand

Joined: Feb 06, 2001
Posts: 55
We are looking to tune our SQL queries.
One suggestion that came from on of our developers is to replace multiple small queries and merge them to optimize them since the queries take a large time to cross network and reach database. When the queries are merged they become hard to understand for an average person and understanding them is very hard.
What should be the strategy related to running one large query vs running multiple small queries.
Can anybody give out pointers on how to write efficient queries and what consititutes a good query.
Brahim Bakayoko
Ranch Hand

Joined: Aug 29, 2003
Posts: 155
1. Consider using stored procedures if the multiple queries are part of a single transaction.
2. if you are calling the same statement only with different parameters more than 65 times, use prepared statments, else use statements unless you need to set in parameters (then use prepared statments in all cases).
3. retrieving a large number of fields should not scare anyone, even novices. Loops and Hashtables and other collections are your friends. They can help you make your code a lot cleaner.

More importantly, thought, you need to analyze your database design. If you are forced to make a large number queries during a single transaction, it is probably due to bad design.
Although you might be using a relational database, you can model it in a pseudo object oriented fashion (storing and retrieving collections and objects).


SCJP, SCWCD, SCBCD, IBM CSD WebSphere v5, <br />A+, MCP 2000 and 2000 server, CST, and few incompleted certification tracks.<br /> <br />Ivory Coast<br /> <br />Analyze your web Request/Response @ <a href="http://webtools.servehttp.com" target="_blank" rel="nofollow">http://webtools.servehttp.com</a> down for a while...
Ilja Preuss
author
Sheriff

Joined: Jul 11, 2001
Posts: 14112
Originally posted by Syed Hussain:
One suggestion that came from on of our developers is to replace multiple small queries and merge them to optimize them since the queries take a large time to cross network and reach database.

How do you know his?


The soul is dyed the color of its thoughts. Think only on those things that are in line with your principles and can bear the light of day. The content of your character is your choice. Day by day, what you do is who you become. Your integrity is your destiny - it is the light that guides your way. - Heraclitus
Syed Hussain
Ranch Hand

Joined: Feb 06, 2001
Posts: 55
Originally posted by Brahim Bakayoko:
1. Consider using stored procedures if the multiple queries are part of a single transaction.

Braham, thanks for your suggestions, especially one regarding replacing very large queries with Stored Procedures. I`ll check to see if we can do this.

Originally posted by Ilja Preuss:

How do you know his?

Ila, we do not have any process on evaluating how much time the application spends in business logic and how much it spends on database logic. So this statement is only an impression since retrieving data from database is generally the most expensive operation.
Thanks for your help.
Ilja Preuss
author
Sheriff

Joined: Jul 11, 2001
Posts: 14112
Originally posted by Syed Hussain:
Ila, we do not have any process on evaluating how much time the application spends in business logic and how much it spends on database logic. So this statement is only an impression since retrieving data from database is generally the most expensive operation.

Then it could also be that not the network transfer is your problem, but the actual execution of the statements.
You could try to use IronEye Sql to analyze where your performance problem really is coming from...
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30057
    
149

Syed,
I agree with Ilja that the first place to look is the queries themselves.
We do happen to have a problem with network traffic. The problem isn't tied so much to the number of queries as it is to the amount of data returned. If this winds up being your problem too, make sure that you are not returning any unused columns (This is probably a good thing to do anyway) and that you are not returning any columns that can be determined from the input parameters.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
 
jQuery in Action, 2nd edition
 
subject: Performance of SQL Query
 
Similar Threads
jforum 2.1.7 b3 error with sql server 2000 script
why we require execute() method?
com.sybase.jdbc2. jdbc. SybSQLException
JDBC Code Optimization Question
When Command Object/POJO is not sufficient