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  select query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Performance
Bookmark "performance of  select query" Watch "performance of  select query" New topic
Author

performance of select query

ihsan kocak
Ranch Hand

Joined: Mar 14, 2012
Posts: 31
hi all.i have a jsp which lists(with datagrid) broken down workitems.in that list there is a column which is to show if an attachment of the workitem is locked or not.if it is locked there exists docbaselockicon in the column.

i determine locked attachment in this way:


IDfCollection coll = getQuery("SELECT r_lock_owner FROM dbo.hlk_document_sp where r_lock_owner!='' and workflow_id='"
+ oWorkitem.getWorkflowId().toString() + "'");
if (coll.next()) {
isLocked[i] = true;

} else {
isLocked[i] = false;

}


this works very slowly.what can i do to make performance better?is there a caching system for this in java?

Best Regards.
Jayesh A Lalwani
Bartender

Joined: Jan 17, 2008
Posts: 2333
    
  28

Did you look at the query plan? How big is the table? Is r_lock_owner a string? Is it indexed? Doing a not equals search on unindexed string columns can be very expensive.

Is workflow_id indexed? Which criteria is more selective? If you have unindexed columns, it might be cheaper to put the more selective criteria before the less selective criteria if you just look at the individual criteria, which criteria will return more rows. The criteria that returns less rows is more selective.

I would look at the query plan on a database populated with data first. The query plan will tell you what the database is doing. You can tweak your query.

Also, do you have a large number of lock owners on a workflow? It might be better to do a select count(r_lock_owner) than select r_lock_owner. It will reduce the network latency
ihsan kocak
Ranch Hand

Joined: Mar 14, 2012
Posts: 31
Thank you very much for your reply.i figured out the real problem.i determined that this query runs fast but, the method which executes the query is called for 1281 times for just 1 click.because it is an onControlInitiialized method and runs for every control.so as a solution i removed the query to init() method it became fast.
Best regards.
Jayesh A Lalwani wrote:Did you look at the query plan? How big is the table? Is r_lock_owner a string? Is it indexed? Doing a not equals search on unindexed string columns can be very expensive.

Is workflow_id indexed? Which criteria is more selective? If you have unindexed columns, it might be cheaper to put the more selective criteria before the less selective criteria if you just look at the individual criteria, which criteria will return more rows. The criteria that returns less rows is more selective.

I would look at the query plan on a database populated with data first. The query plan will tell you what the database is doing. You can tweak your query.

Also, do you have a large number of lock owners on a workflow? It might be better to do a select count(r_lock_owner) than select r_lock_owner. It will reduce the network latency
Winston Gutkowski
Bartender

Joined: Mar 17, 2011
Posts: 7652
    
  19

ihsan kocak wrote:i determined that this query runs fast but, the method which executes the query is called for 1281 times for just 1 click.

Yup, that'll do it.

Databases have been around for a lot longer than Java, and are pretty good at doing things fast - even with stuff like 'NOT =' and 'MATCHES' - so if I find a JDBC-type query is taking inordinately long, my first instinct is to look at the Java side. It's not a 100% solution (especially if you're querying a very large table), but I've found that it's true more often than not.

Winston


Isn't it funny how there's always time and money enough to do it WRONG?
Articles by Winston can be found here
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

You should definitely use PreparedStatement (see also our wiki) to prevent SQL injection attacks.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: performance of select query