Thanx for ur prompt response. Here are both the queries

Not in version :

select first 1 column1, column2
from table1
where column3 < current_date
and column3 > (current_date -7)
and column4 not in (select column4
from table1
where column3 < current_date
and column3 > current_date
and column5 <> 'XYZ'
)

Not Exists version :

select first 1column1, column2
from table1
where column3 < current_date
and column3 > (current_date -7)
and not exists (select column4
from table1
where column3 < current_date
and column3 > current_date
and column5 <> 'XYZ'
)

Please let me know in case is something else you want to know.

Thanx.
Mahesh.

Milan Babuskov <albis@...> wrote:

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



Yahoo! Groups SponsorADVERTISEMENT

To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com



Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.


---------------------------------
Want to chat instantly with your online friends? Get the FREE Yahoo!Messenger

[Non-text portions of this message have been removed]