Parameter-less query if fast, but slow in procedure

Hello everyone,

I have the following query that returns me around 500,000 rows. It runs in around 1 minute and 30-40 seconds when runs as query, but as a procedure, it goes up to 8 minutes. What may I be missing? I received it for maintenance but can't find what the bottleneck is:

SELECT
        AL.CD_ALMOXARIFADO,
        AL.DC_ALMOXARIFADO,
        UN.CD_SUPRI,
        UN.TXT_DCR_UNID_SAUD,
        MT.COD_MATE_SAUDE,
        MT.DESCRICAO,
        MT.UNIDADE_MEDIDA,
        CAST(ISNULL(EL.QT_CMM, 0) AS DECIMAL(12,2)) AS QT_CMM_INFORMADO,
        CAST(ISNULL(CM.QT_CMM_CALCULADO, 0) AS DECIMAL(12,2)) AS QT_CMM_CALCULADO,
        ISNULL(EL.QT_ESTOQUE, 0) AS QT_ESTOQUE
FROM
        ALMOXARIFADO AL (NOLOCK)
        INNER JOIN ESTOQUE_LOCAL EL (NOLOCK)
                ON AL.CD_ALMOXARIFADO = EL.CD_ALMOXARIFADO
        INNER JOIN T9216_UNIDAD_SAUDE UN (NOLOCK)
                ON EL.COD_UNID_SAUD = UN.COD_UNID_SAUD
        INNER JOIN T9115_MAT_SERV_SAD MT (NOLOCK)
                ON EL.COD_MATE_SAUDE = MT.COD_MATE_SAUDE
        INNER JOIN PARAMETRO_ESTOQUE_UNIDADE PU (NOLOCK)
                ON EL.COD_UNID_SAUD = PU.COD_UNID_SAUD
        INNER JOIN
        (
                SELECT
                        DISTINCT ku.cod_unid_saud, ki.cod_mate_saude
                FROM
                        kit kt
                INNER JOIN
                        kit_item ki
                        ON kt.ci_kit = ki.ci_kit
                INNER JOIN
                        kit_x_unidade ku
                        ON kt.ci_kit = ku.ci_kit 
                INNER JOIN
                        T9216_unidad_saude u on u.cod_unid_saud=ku.cod_unid_saud
                WHERE
                        kt.in_status = 'A' AND
                        ki.in_status = 'A'
                        AND DT_FIM_UNID_SAUD IS NULL
        ) AS MU
                ON      EL.COD_MATE_SAUDE = MU.COD_MATE_SAUDE AND 
                        EL.COD_UNID_SAUD = MU.COD_UNID_SAUD
        LEFT JOIN 
        (
                SELECT
                        mp.cod_unid_saud,
                        mi.cd_almoxarifado,
                        mi.cod_mate_saude,
                        (SUM(mi.qt_movimento) / 90) * 30 AS QT_CMM_CALCULADO
                FROM
                        movimento_produto mp (nolock)
                INNER JOIN
                        movimento_item mi (nolock)
                        ON mp.id_movimento = mi.id_movimento
                WHERE
                        mp.cd_tipo_movto IN ('S', 'U', 'X', 'D') AND
                        mp.dt_movimento BETWEEN DATEADD(d, -90, GETDATE()) AND GETDATE() AND
                        (mi.in_estorno IS NULL OR mi.in_estorno = 'N')
                GROUP BY
                        mp.cod_unid_saud,
                        mi.cd_almoxarifado,
                        mi.cod_mate_saude
        ) AS CM
                ON      EL.COD_UNID_SAUD = CM.COD_UNID_SAUD AND
                        EL.CD_ALMOXARIFADO = CM.CD_ALMOXARIFADO AND
                        EL.COD_MATE_SAUDE = CM.COD_MATE_SAUDE
WHERE
        (EL.IN_BLOQUEIO = 'N' OR EL.IN_BLOQUEIO IS NULL) AND
        ((UN.CD_SUPRI IS NOT NULL OR UN.CD_SUPRI <> '') AND CD_SUPRI NOT LIKE '1810%') AND
        AL.IN_STATUS = '0' AND
        MT.TP_ORIGEM_ABASTECIMENTO = 'A' AND
        MT.IN_REQUISICAO_ABASTECIMENTO = 'S' AND
        PU.IN_REQUISICAO_ABASTECIMENTO = 'S' AND
        PU.IN_ATENDIMENTO = 'S'
ORDER BY
        UN.COD_UNID_SAUD,
        AL.CD_ALMOXARIFADO,
        MT.COD_MATE_SAUDE

If anyone has any other hints/tips on any optimization I may be missing, I appreciate.

Thanks in advance,

Marcelo

---

Marcelo

http://ift.tt/1lrQ75l - Price Comparison. Need the lowest price? We find it for you.