performance of select query

[ihsan kocak]

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]

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]

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]

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]

You should definitely use PreparedStatement (see also our wiki) to prevent SQL injection attacks.