very slow execution of stored procedures
От | Vilson farias |
---|---|
Тема | very slow execution of stored procedures |
Дата | |
Msg-id | 001501c0c903$bbd42020$98a0a8c0@dti.digitro.com.br обсуждение исходный текст |
Список | pgsql-general |
Greetings, I found something very weird related with stored procedures execution. I have this stored procedure to finalize a phone call, writing tha time of call finalization and some other values to a calls table, called cham_chamada. Please check this out (very simple) : ------------------------------------------------------------------ CREATE FUNCTION finaliza_chamv2(INT4, INT4, INT4, CHAR(23), CHAR(23), CHAR(1), INT4, INT4, INT4, CHAR(23), INT4, INT4, CHAR(25), INT4, INT4, INT4) RETURNS int4 AS ' DECLARE pbxs ALIAS FOR $1; pchave ALIAS FOR $2; pidentificacao ALIAS FOR $3; pdtinicial ALIAS FOR $4; pdtfinal ALIAS FOR $5; pflgliber ALIAS FOR $6; ptempototal ALIAS FOR $7; pcodliber ALIAS FOR $8; pddd ALIAS FOR $9; pdtocup ALIAS FOR $10; pindicadora ALIAS FOR $11; pcategoria ALIAS FOR $12; pidentidadea ALIAS FOR $13; pfds ALIAS FOR $14; presultcham ALIAS FOR $15; pcifraorigem ALIAS FOR $16; BEGIN UPDATE cham_chamada SET dt_final = TIMESTAMP(pdtfinal), flg_liberacao = pflgliber, temp_total = ptempototal, cod_liberjuntor = pcodliber, ddd = pddd, indicadora = pindicadora, cod_categoria = pcategoria, identidadea = pidentidadea, cod_fds = pfds, cod_resultcham = presultcham, cifra_origem = pcifraorigem WHERE cod_bxs = pbxs AND chave = pchave AND identificacao = pidentificacao AND dt_inicial = TIMESTAMP(pdtinicial); IF pdtocup <> '''' THEN UPDATE cham_servico SET dt_ocupacao = TIMESTAMP(pdtocup) WHERE cod_bxs = pbxs AND chave = pchave AND identificacao = pidentificacao AND dt_inicial = TIMESTAMP(pdtinicial) AND dt_finalizacao is null; END IF; RETURN 0; END; ' LANGUAGE 'plpgsql'; ------------------------------------------------------------------ Once you know all about this stored procedure, lets see this call : SELECT finaliza_chamv2(1,65535,49644,'2001-04-17 11:50:55','2001-04-17 12:17:40', '0', 0, 0, 48, '', 0, 10, '2817107', 0, 261, 65535); If I change all variables to the parameters value inside the stored procedure and then execute the frist script, then it is very fast, check out : bxs=# bxs=# UPDATE cham_chamada bxs-# SET dt_final = TIMESTAMP('2001-04-17 12:12:10'), bxs-# flg_liberacao = '0', bxs-# temp_total = 0, bxs-# cod_liberjuntor = 0, bxs-# ddd = 48, bxs-# indicadora = 0, bxs-# cod_categoria = 10, bxs-# identidadea = '2817005', bxs-# cod_fds = 0, bxs-# cod_resultcham = 6, bxs-# cifra_origem = 65535 bxs-# WHERE cod_bxs = 1 AND bxs-# chave = 65535 AND bxs-# identificacao = 49644 AND bxs-# dt_inicial = TIMESTAMP('2001-04-17 12:12:00'); UPDATE 1 execution time : <1ms now its time to do the same thing using the stored procedure : bxs=# bxs=# SELECT finaliza_chamv2(1,65535,49644,'2001-04-17 11:50:55','2001-04-17 12:17:40', '0', 0, 0, 48, '', 0, 10, '2817107', 0, 261, 65535); finaliza_chamv2 ----------------- 0 (1 row) execution time : about 5s Is it supose to execute with different speed? What can I do to fix it? I'm using postgres RPM 7.0.3-2 in RedHat 6.2. ps: There are some specific procedures I needed to execute before I got pl/pgsql working : CREATE FUNCTION plpgsql_call_handler () RETURNS opaque AS '/usr/lib/pgsql/plpgsql.so' LANGUAGE 'C'; CREATE PROCEDURAL LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler LANCOMPILER 'PL/PgSql internal'; Best regards from Brazil, José Vilson de Mello de Farias Dígitro Tecnologia Ltda - Brasil
В списке pgsql-general по дате отправления: