Procedure Lenta

Firebird

11/06/2011

Galera eu tenho uma procedure no firebird2.1 que está demorando uns 3 seg. local e na rede demora uns 5 a 6 seg., eu ja crei indices na minha tabela, mas ainda esta lenta. Tem alguma possibilidade de deixa-la mais rápida.


A Procedure Principal é a SP_LISTA_ESTOQUE que lista o estoque de cada produto.

CREATE PROCEDURE SP_LISTA_ESTOQUE 
returns (
    prod_id integer,
    prod_ean dm_cod_barras,
    prod_descricao varchar(80),
    prod_pco_varejo dm_valor,
    status varchar(8),
    estoque_l1 numeric(9,3),
    estoque_l2 numeric(9,3))
as
declare variable id_loja integer;
declare variable est_saldo numeric(9,3);
BEGIN
    FOR
        SELECT
            produtos.prod_id,
            produtos.prod_ean,
            produtos.prod_descricao,
            produtos.prod_pco_varejo,
            case
                when produtos.prod_status = 'A' then 'ATIVO'
            else
                'INATIVO'
            end as prod_status
        FROM
            PRODUTOS  ORDER BY 1

        INTO :prod_id,
             :prod_ean,
             :prod_descricao,
             :prod_pco_varejo,
             :status
    DO
    BEGIN
        ESTOQUE_L1 = 0;
        ESTOQUE_L2 = 0;

        for select
              coalesce(e1.loj_id, -1), coalesce(e1.estoq_atual,0)
            from estoque e1
            where e1.prod_id = :prod_id
            and e1.estoq_id = (select
                                 max(e2.estoq_id)
                               from estoque e2
                               where e2.prod_id = e1.prod_id
                               and e2.loj_id = e1.loj_id)
            into :id_loja, :est_saldo do
        begin
            if (:id_loja = 1) then
                ESTOQUE_L1 = est_saldo;
            else
                if (:id_loja = 2) then
                    ESTOQUE_L2 = est_saldo;
        end
        suspend;
    END
END


Indices da tabela:
PROD_ID,ESTOQ_ID
LOJ_ID,PROD_ID,ESTOQ_ID


Agora vem o problema, eu criei a procedure SP_PESQUISA_PRODUTOS para localizar os produtos no resultado da SP_LISTA_ESTOQUE.


  CREATE PROCEDURE SP_PESQUISA_PRODUTOS (
    strvalor varchar(80))
returns (
    prod_id integer,
    prod_ean varchar(14),
    prod_descricao varchar(80),
    prod_pco_varejo numeric(9,2),
    status varchar(8),
    estoque_l1 numeric(9,3),
    estoque_l2 numeric(9,3))
as
BEGIN
  FOR
    /* Retorna os registo cujo a Descrição seja igual a StrValor  */
    SELECT
        PROD_ID,
        prod_ean,
        PROD_DESCRICAO,
        prod_pco_varejo,
        STATUS,
        ESTOQUE_L1,
        ESTOQUE_L2
    FROM
        SP_LISTA_ESTOQUE
    where
        PROD_DESCRICAO LIKE :StrValor || '%'
    AND STATUS = 'ATIVO'
    
    UNION ALL
    
    /* Retorna os registo cujo o ultimos digitos do EAN13 seja igual a StrValor  */
    
    SELECT
        PROD_ID,
        prod_ean,
        PROD_DESCRICAO,
        prod_pco_varejo,
        STATUS,
        ESTOQUE_L1,
        ESTOQUE_L2
    FROM
        SP_LISTA_ESTOQUE
    where
        prod_ean LIKE   '%' || :StrValor
    AND STATUS = 'ATIVO'
    INTO :PROD_ID,
         :prod_ean,
         :PROD_DESCRICAO,
         :prod_pco_varejo,
         :STATUS,
         :ESTOQUE_L1,
         :ESTOQUE_L2
  DO
  BEGIN
    SUSPEND;
  END
END


Processos:
1º execulta a procedure SP_LISTA_ESTOQUE pegando o estoque e depois chamo outra procedure para localizar um produto no resultado da SP_LISTA_ESTOQUE.

Eu tô achando muito processso mas não consigo resolver preciso da ajuda de vocês.
Sidney Abreu

Sidney Abreu

Curtidas 0

Respostas

Emerson Nascimento

Emerson Nascimento

11/06/2011

porque uma outra stored procedure? pode ser uma instrução SQL simples:

SELECT
    *
FROM
    SP_LISTA_ESTOQUE
WHERE
    STATUS = 'ATIVO'
    AND (PROD_DESCRICAO STARTING WITH :StrValor
         OR PROD_EAN STARTING WITH :StrValor)





mas se você precisa mesmo de uma stored procedure:

CREATE PROCEDURE SP_PESQUISA_PRODUTOS (
    strvalor varchar(80))
returns (
    prod_id integer,
    prod_ean varchar(14),
    prod_descricao varchar(80),
    prod_pco_varejo numeric(9,2),
    status varchar(8),
    estoque_l1 numeric(9,3),
    estoque_l2 numeric(9,3))
as
BEGIN
  FOR
    /* Retorna os registo cuja Descrição ou cód.barras iniciem por StrValor  */
    SELECT
        PROD_ID,
        prod_ean,
        PROD_DESCRICAO,
        prod_pco_varejo,
        STATUS,
        ESTOQUE_L1,
        ESTOQUE_L2
    FROM
        SP_LISTA_ESTOQUE
    WHERE
        STATUS = 'ATIVO'
        AND (PROD_DESCRICAO STARTING WITH :StrValor
             OR PROD_EAN STARTING WITH :StrValor)
    INTO :PROD_ID,
         :prod_ean,
         :PROD_DESCRICAO,
         :prod_pco_varejo,
         :STATUS,
         :ESTOQUE_L1,
         :ESTOQUE_L2
  DO
    SUSPEND;
END

de qualquer forma não terá uma performance ótima pois a busca usando o LIKE ou o STARTING WITH está sendo feita no resultset da SP e não há indices a serem usados.




o que você pode fazer é alterar a stored procedure SP_LISTA_ESTOQUE de modo a receber o parâmetro para o filtro:

CREATE PROCEDURE SP_LISTA_ESTOQUE(
    strvalor varchar(80))
returns (
    prod_id integer,
    prod_ean dm_cod_barras,
    prod_descricao varchar(80),
    prod_pco_varejo dm_valor,
    status varchar(8),
    estoque_l1 numeric(9,3),
    estoque_l2 numeric(9,3))
as
declare variable id_loja integer;
declare variable est_saldo numeric(9,3);
BEGIN
    FOR
        SELECT
            produtos.prod_id,
            produtos.prod_ean,
            produtos.prod_descricao,
            produtos.prod_pco_varejo,
            case
                when produtos.prod_status = 'A' then 'ATIVO'
            else
                'INATIVO'
            end as prod_status
        FROM
            PRODUTOS  ORDER BY 1
    WHERE
            produtos.prod_descricao starting with :strvalor
            OR produtos.prod_ean starting with :strvalor
        INTO :prod_id,
             :prod_ean,
             :prod_descricao,
             :prod_pco_varejo,
             :status
    DO
    BEGIN
        ESTOQUE_L1 = 0;
        ESTOQUE_L2 = 0;

        for select
              coalesce(e1.loj_id, -1), coalesce(e1.estoq_atual,0)
            from estoque e1
            where e1.prod_id = :prod_id
            and e1.estoq_id = (select
                                 max(e2.estoq_id)
                               from estoque e2
                               where e2.prod_id = e1.prod_id
                               and e2.loj_id = e1.loj_id)
            into :id_loja, :est_saldo do
        begin
            if (:id_loja = 1) then
                ESTOQUE_L1 = est_saldo;
            else
                if (:id_loja = 2) then
                    ESTOQUE_L2 = est_saldo;
        end
        suspend;
    END
END


e consultar usando esse novo parâmetro:

SELECT
    *
FROM
    SP_LISTA_ESTOQUE( conteudo_desejado )
WHERE
    STATUS = 'ATIVO'
GOSTEI 0
POSTAR