Help to optimize a Query

i need optimize this query, please see the comented line:

SELECT p.NUM_PROCESSO,
       p.NUM_PROC_JUD,
       p.Num_Proc_Jud_Antigo1,
       p.Num_Proc_Jud_Antigo2,
       p.Num_Proc_Jud_Novo,
       a.assunto,
       su.subassunto,
       u.UNIDADE,
       s.SERVIDOR,
       dvj.data_vinc,
       p.TIPO,
       c.DESC_CLASSIF
FROM   processo p
       LEFT OUTER JOIN assunto a
         ON a.cod_assunto = p.cod_assunto
       LEFT OUTER JOIN subassunto su
         ON su.cod_subassunto = p.cod_subassunto
       LEFT OUTER JOIN Distrib_VincJud dvj
         ON dvj.num_processo = p.num_processo
       LEFT OUTER JOIN servidor s
         ON S.COD_SERVIDOR = dvj.COD_SERVIDOR
       LEFT OUTER JOIN unidade u
         ON u.COD_UNIDADE = s.COD_UNIDADE
       LEFT OUTER JOIN Classif_Processo c
         ON C.COD_CLASSIF = p.COD_CLASSIF
WHERE  p.TIPO = 'J'
       AND p.NUM_PROCESSO NOT IN (SELECT d.num_processo
          开发者_StackOverflow社区                        FROM   distribuicao d
                                  WHERE  d.COD_SERVIDOR in ( '0', '000' )
                                         AND d.num_distribuicao IN
                                             (SELECT MAX(num_distribuicao)
                                              FROM   Distribuicao
                                              GROUP  BY num_processo)
                                         --this suquery return 100k lines !!! and consume all CPU:
                                         AND dvj.id_vinc IN
                                                (SELECT MAX(id_vinc)
                                                FROM Distrib_VincJud
                                                where  ativo = '1'
                                                GROUP  BY num_processo))
       AND p.NUM_PROCESSO NOT IN (SELECT num_processo
                                  FROM   Anexos)
       AND s.ATIVO = 1  

my horrible solution at this moment: http://pastebin.com/C4PHNsSc

What I would do is convert the IN and NOT IN into joins:

SELECT p.NUM_PROCESSO, p.NUM_PROC_JUD, p.Num_Proc_Jud_Antigo1,
    p.Num_Proc_Jud_Antigo2, p.Num_Proc_Jud_Novo, a.assunto,
    su.subassunto, u.UNIDADE, s.SERVIDOR, dvj.data_vinc, p.TIPO,
    c.DESC_CLASSIF
FROM
    processo p
    INNER JOIN (
        SELECT p.num_processo,
            CASE WHEN dvj.id_vinc IS NOT NULL
                AND d.num_distribuicao IS NOT NULL
                OR a.num_processo IS NOT NULL THEN
                1
            ELSE
                0
            END exclude
        FROM
            processo p
            LEFT JOIN Anexos a
                ON p.num_processo = a.num_processo
            LEFT JOIN (
                SELECT num_processo,
                    MAX(num_distribuicao) AS max_distribuicao
                FROM Distribuicao
                GROUP BY num_processo
            ) md ON p.num_processo = md.num_processo
            LEFT JOIN (
                SELECT num_processo, MAX(id_vinc) AS max_vinc
                FROM Distrib_VincJud
                WHERE ativo = '1'
                GROUP BY num_processo
            ) mv on p.num_processo = mv.num_processo
            LEFT JOIN distribuicao d
                ON p.num_processo = d.num_processo
                    AND md.max_distribuicao = d.num_distribuicao
            LEFT JOIN Distrib_VincJud dvj
                ON p.num_processo = dvj.num_processo
                    AND mv.max_vinc = dvj.id_vinc
        WHERE d.COD_SERVIDOR in ('0', '000')
    ) IncExc
        ON p.num_processo = IncExc.num_processo
    LEFT OUTER JOIN assunto a
        ON a.cod_assunto = p.cod_assunto
    LEFT OUTER JOIN subassunto su
        ON su.cod_subassunto = p.cod_subassunto
    LEFT OUTER JOIN Distrib_VincJud dvj
        ON dvj.num_processo = p.num_processo
    LEFT OUTER JOIN servidor s
        ON S.COD_SERVIDOR = dvj.COD_SERVIDOR
    LEFT OUTER JOIN unidade u
        ON u.COD_UNIDADE = s.COD_UNIDADE
    LEFT OUTER JOIN Classif_Processo c
        ON C.COD_CLASSIF = p.COD_CLASSIF
WHERE
    p.TIPO = 'J'
    AND IncExc.exclude = 0
    AND s.ATIVO = 1

This part

 AND p.NUM_PROCESSO NOT IN (

        SELECT d.num_processo FROM distribuicao d 
        WHERE d.COD_SERVIDOR in ('0','000') 
        AND d.num_distribuicao IN (
              SELECT MAX(num_distribuicao) FROM Distribuicao GROUP BY num_processo
        )

and this part

  AND p.NUM_PROCESSO NOT IN (

        SELECT num_processo FROM Anexos

  )

are going to be your biggest bottlenecks in the query as you've got nested subqueries in there.

You also have a few of these:

SELECT MAX(id_vinc) FROM Distrib_VincJud where ativo = '1' GROUP BY num_processo)
SELECT MAX(num_distribuicao) FROM Distribuicao GROUP BY num_processo

You might gain a few more seconds by letting these be seperate queries where you can store the results.

In fact, you might do well to have a separate table with these NOT IN(...) values that gets updated upon every insert to your database. It all depends on how often you run each query.

Have you tried running your Query Optimizer on these?

Separate out the sub queries and then do a join

i.e. find all the num_processo that you are excluding in one query first. do a left join with the processo table on the num_processo field and exclude those where the first table's num_processo field is null

Edit: what's the relationship between the tables distribuicao and distrib_vincJud?

this line is killing your performance...

AND dvj.id_vinc IN
(   SELECT MAX(id_vinc)
    FROM Distrib_VincJud
    where  ativo = '1'
    GROUP  BY num_processo
)

sub query in a sub query which then references a joined table outside of the sub query??????