Optimize query with many NOT IN

[luigi.z...@gmail.com]

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

[Anith Sen]

>> 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).

Others would need to reproduce your problem scenario to understand how this
query behaves. So please post your table DDLs, sample data & expected
results. For details, see: www.aspfaq.com/5006

You seem to have written some redundant code with all the LEFT JOINs in the
subqueries -- it looks like it could be much simpler.

--
Anith

[--CELKO--]

You need to read ISO-11179 so you use proper data element names. You
actually had "tbl-"on the table names! Sometimes "id" id a
prefix and sometimes it is a postfix. Why is TransazioniInput not
"<something>Transazoni" You use name like status (of what??) and
quantita (of what??) and id (of what??) that are too vague to be valid
data element names.

You constantly have multiple names for the same data element!
(TI.idservizio = TS.id) element.

You do not need the SELECT DISTINCT with an IN() predicate, but you
also do not need IN() predictes. Try something like this:

SELECT u_id, s_id, cliente, opcode, id_servizio, id_prodotto, quantita,
operation_date, modalitapagamento, paymentreference, importo, status
FROM TransazioniInput AS T1
WHERE
NOT EXISTS
(SELECT *
FROM TrascodificaClienti AS TC
WHERE TI.cliente = TC.codicezu)
OR
NOT EXISTS
(SELECT *
FROM TrascodificaServizi AS TS
WHERE TI.idservizio = TS.id)

OR
NOT EXISTS
(SELECT *
FROM trascodificaprodotti AS TP
WHERE TI.idprodotto = TP.id)

-- these could be EXISTS() also, but leave them as is
OR modalitapagamento
NOT IN (SELECT id --vague name
FROM TrascodificadkbdatPayment_types)

OR s_id
NOT IN (SELECT sid
FROM Centriservizi
WHERE environment = 1);

Why did you fail to declare REFERENCES clauses to all these tables on
TransazioniInput? The SQL engine should be enforcing all these rules.

[luigi.z...@gmail.com]

--CELKO-- ha scritto:

> You need to read ISO-11179 so you use proper data element names. You
> actually had "tbl-"on the table names! Sometimes "id" id a
> prefix and sometimes it is a postfix. Why is TransazioniInput not
> "<something>Transazoni" You use name like status (of what??) and
> quantita (of what??) and id (of what??) that are too vague to be valid
> data element names.

[cut]

Hi Celko,
thanks for the response. Unfortunately I've not made the database
structure, so I've get the layout as is.
By the way, where can I find the ISO-11179 without have to buy them?
(just for my IT culture).
Now I'm testing your query.
The structure of the tables in here:

CREATE TABLE [dbo].[tblTransazioniInput] (
[uid] [varchar] (36) NOT NULL ,
[sid] [int] NOT NULL ,
[Cliente] [varchar] (5) NOT NULL ,
[OpCode] [varchar] (3) NOT NULL ,
[IdServizio] [int] NOT NULL ,
[IdProdotto] [int] NOT NULL ,
[Quantita] [int] NOT NULL ,
[OperationDate] [varchar] (14) NOT NULL ,
[ModalitaPagamento] [int] NOT NULL ,
[PaymentReference] [varchar] (32) NULL ,
[Importo] [money] NULL ,
[Status] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblTrascodificaClienti] (
[Id] [int] NOT NULL ,
[zId] [varchar] (10) NULL ,
[CodiceZu] [varchar] (5) NOT NULL ,
[IdGruppo] [int] NOT NULL ,
[zGruppoConti] [varchar] (12) NOT NULL ,
[zSocietaS] [varchar] (4) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblTrascodificaDKBDATPaymentType] (
[Id] [int] NOT NULL ,
[zId] [varchar] (12) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tbl_CentriServizi] (
[sid] [int] NOT NULL ,
[environment] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblTrascodificaProdotti] (
[Id] [int] NOT NULL ,
[zId] [varchar] (12) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblTrascodificaServizi] (
[Id] [int] NOT NULL ,
[zId] [varchar] (12) NULL ,
[ContractTypeId] [int] NOT NULL ,
[IdRivenditore] [int] NOT NULL
) ON [PRIMARY]
GO

[luigi.z...@gmail.com]

The orginal query is this one:

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.CodiceZuc

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)

and my problem is to optimize this.

[--CELKO--]

>> By the way, where can I find the ISO-11179 without have to buy them? <<

Try http://metadata-standards.org/11179/#11179-5. I also have a
"human readable" set of suggestions based on this standard in my book
SQL PROGRAMMING STYLE.