db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From <de...@segel.com>
Subject RE: URGENT!!! JDBC SQL query taking long time for large IN clause
Date Tue, 07 Apr 2009 17:12:37 GMT
Bryan,

Thanks.
Just a mini rant for a second...

KISS == Keep It Simple Stupid. This is an engineering principle that goes to
the core of the problem. If you break a complex problem down in to is base
components, you'll find that you can solve the smaller component problems
easier and then build up to solving the more complex problem. 

So before you panic, (The topic said URGENT!!!), slow down and relax. Go
back and clean up your code and *think*. If you panic, you'll jump to
conclusions and come up with a bad design.

Ok, rant over...

To answer Arindam's question from another post...

Once you open a database connection, you can prepare the SQL queries that
you want to run. As long as you have an open connection and don't drop the
connection, you should be able to run the prepared statements without
incurring additional costs in preparing the statement. So you spend the
~170ms once and not each time you execute the statement.

In my post I mention that you should consider a temp table. Now a caveat...
different databases handle temp tables differently. With Oracle and DB2,
temp tables are a pain to work with. With Informix (IBM's IDS) you can
create temp tables on the fly and they will only persist as long as you
maintain the connection. I'd have to go back and look at how Derby handles
temp tables, but a worst case scenario is that at the start of your app, you
drop the temp table (catch the exception), create the temp, and then prior
to running your query you insert your rows in to the temp table.

For your problem since you've got hundreds+ values in your IN clause, the
first question I have to ask.. are you ensuring that the values in your IN
clause are unique? That is, are you reducing your collection to a minimum?

Also I just saw your post for the results with 5K elements in the IN clause.
No shock there. Now put them in a temp table and join them as part of a sub
select. 

Arindam's question does hit on a problem with a lot of databases. How do
they handle the IN clause? 

A possible feature request could be to allow the end user to join against an
external collection. There's more to this and I'd imagine that you'd end up
with some form of VTI like Informix has.

BTW ... Knut's solution isn't simple but interesting ...

HTH 

-Mike

> -----Original Message-----
> From: Bryan Pendleton [mailto:bpendleton@amberpoint.com]
> Sent: Tuesday, April 07, 2009 11:06 AM
> To: Derby Discussion
> Subject: Re: URGENT!!! JDBC SQL query taking long time for large IN clause
> 
> > What version of Derby? What operating system? What version of Java?
> > [Arindam] 10.1.3.1; Windows XP; JRE 1.6
> 
> Definitely try using Derby 10.4.
> 
> Also, Mike Segel made a bunch of other great suggestions in his mail, so
> I suggest following those and see where it gets you.
> 
> thanks,
> 
> bryan



Mime
View raw message