At 09:54 AM 12-06-02 +0300, you wrote:

>Thanks for your help...
>
>Has anyone at developing team has any plan to correct this...

It's not broken. It is just logically "slow SQL" if used for large
sets. Your requirements here seem to involve small sets.

Here was your query:

SELECT URUN_KUTUK_ID
, CARIID
, MARKAID
, URUN_KOD
, URUN_AD_TR
, URUN_AD_EN
, GTIP
FROM URUN_KUTUK
WHERE URUN_KOD IN
(SELECT DISTINCT(GM_PART_NUMBER)
FROM INVOICE_DETAIL
WHERE INVOICE_MASTER_ID=437)

Assuming Invoice_Detail.GM_PART_NUMBER represents the same data element as
URUN_KUTUK.URUN_KOD...

Remove the DISTINCT operator from this query as it causes totally
unnecessary extra processing of the comparison set. Logically, you are
interested only in whether there are ANY matching rows.

If GM_PART_NUMBER is not the same as URN_KOD, then the criterion will never
be met at all.

btw, the syntax for the EXISTS() predicate is

SELECT U.URUN_KUTUK_ID
, U.CARIID
, U.MARKAID
, U.URUN_KOD
, U.URUN_AD_TR
, U.URUN_AD_EN
, U.GTIP
FROM URUN_KUTUK U
WHERE (EXISTS (
SELECT I.GM_PART_NUMBER FROM INVOICE_DETAIL I
WHERE I.INVOICE_MASTER_ID=437
and I.GM_PART_NUMBER = U.URUN_KOD
))

However, EXISTS() won't work for you here. It will select either ALL of
the rows (if there is a row in the table matching the subselect) or NONE of
the rows (if there is no matching row). EXISTS() would help you if this
query were in a stored procedure as a FOR SELECT query.

It would be worth trying Ian's join, to see whether it works faster than
predicating with IN(). Any performance difference would depend on how many
eligible rows exist. If it is only a few, then the IN(subselected set of
values) approach would probably be faster than the join. If there is no
difference, then stay with IN(), because it will produce an updatable
output set, whereas the joined set will not.

cheers,
heLen

All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org ·
http://users.tpg.com.au/helebor/
_______________________________________________________