Optimize query with many NOT IN

microsoft.public.sqlserver.programming

2 Nov 2006 5:19 PM

luigi.zambetti

Hi all,
I have a problem.
I have to replicare this complex select with many NOT IN clause:

SELECT uid, sid, Cliente, OpCode, IdServizio, IdProdotto, Quantita,
OperationDate, ModalitaPagamento, PaymentReference, Importo, Status
    FROM tblTransazioniInput
    WHERE Cliente IN
        (select distinct cliente
            from tblTransazioniInput TI left outer join tblTrascodificaClienti
TC
            on TI.Cliente = TC.CodiceZu
            where TC.CodiceZucchetti is null)
       OR IdServizio IN
    (select distinct idServizio
        from tblTransazioniInput TI left join tblTrascodificaServizi TS
        on TI.IdServizio = TS.Id
        where TS.Id is null)
       OR IdProdotto IN
    (select distinct idProdotto
        from tblTransazioniInput TI left join tblTrascodificaProdotti TP
        on TI.IdProdotto = TP.Id
        where TP.Id is null)

       OR ModalitaPagamento NOT IN (SELECT ID FROM
tblTrascodificaDKBDATPaymentType)
       OR sid NOT IN (SELECT sid FROM tbl_CentriServizi WHERE
environment = 1)

What I'd like to have is the same query with Left Join (or another
Join) that replicate the exact behaviour of this one, that has problems
of performance (ca. 6min to execute over 500,000 records).

Could anyone help me?
Thanks in advance