Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

performance of select query

 
ihsan kocak
Ranch Hand
Posts: 31
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Rancher
Posts: 2756
32
Eclipse IDE Spring Tomcat Server
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 31
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 10427
63
Eclipse IDE Hibernate Ubuntu
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You should definitely use PreparedStatement (see also our wiki) to prevent SQL injection attacks.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic