--- In firebird-support@yahoogroups.com, Mahesh Ishwar wrote:

> Hello All,
> I've a query which, while executing, is taking a quite a lot of

time. Can someone help me out to optimize it.

>
> select column1, column2
> from table1
> where <some static condition>
> and column3 not in ( select coulmn3
> from table1
> where <some static condition>
> )
>
> It is a join of table1 to itself. 'table1' is not having any index
> as the records are being populated from a JMS queue, which can be
> out of sync and duplicate too.
> If I use 'not in' clause for the sub-query, it gives me correct
> result according to the static conditions, but takes a lot of time
> when the size of the table grows. Even if I fire the query from isql
> and not from my Java class, it takes a lot of time.
> If I use 'not exists' clause, it somehow misses some records almost
> all the time but the answer is quick. The static conditions are
> noting but date comparisions.
> Can someone tell me how I change my query for optimization so that I
> can speed up without loosing data?

Mahesh,
do as Milan advices you and use not exists. Also, alias your table, e.
g.

select column1, column2
from table1 t1
where <some static condition>
and not exists (
select * from table1 t2
where t2.column3=t1.column3
and <some static condition clearly referencing the the tables
through aliases>
)

If this still does not work (it ought to work), then you should add a
primary key to your table. Populate that key through a trigger using a
generator, and you will not run into any trouble with duplicate
records.

HTH,
Set