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?

Not exists is The Right Way (tm). It should give the same results if you
translated it correctly. Can you please post the query you're using, and
your 'not exists' variant of it, so we can find the error.

--
Milan Babuskov
http://fbexport.sourceforge.net