Re: [PERFORM] Queries slow using stored procedures

[Alban Medici (NetCentrex)]

You seem to not have index on botnumber,  but in your query bot number is the clause.

 

I don't explain you why the same query is so long.

but have your try procedure with a loop structure (witch create cursor) ?

 

you could try

 

 

CREATE OR REPLACE FUNCTION  sp_test_Alban1 ( ) returns integer         
as '
DECLARE
  bot char(10);
  oldbatch INTEGER;

  rec RECORD;

  query VARCHAR;

BEGIN

 

  -- initialisation

  bot := ''1-7'';

  query  := '' SELECT  batchserial FROM transbatch WHERE botnumber  = ' || quote_ident(bot) || '' <optionaly your limit clause> ;'';

 

 

   FOR rec IN EXECUTE var_query  LOOP
        return rec."batchserial ".;    

   END LOOP;

   

    --else

    return 0;

 

END;
'
language plpgsql  ;

does it return the same results in the same time ? 

---

From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Rod Dutton
Sent: lundi 18 octobre 2004 20:01
To: [EMAIL PROTECTED]
Subject: [PERFORM] Queries slow using stored procedures

Hi,

 

I have a problem where a query inside a function is up to 100 times slower inside a function than as a stand alone query run in psql.

 

The column 'botnumber' is a character(10), is indexed and there are 125000 rows in the table.

 

Help please!

 

This query is fast:-

 

explain analyze  

  SELECT batchserial
  FROM transbatch
  WHERE botnumber = '1-7'
  LIMIT 1;

                                                           QUERY PLAN                                                          
--------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..0.42 rows=1 width=4) (actual time=0.73..148.23 rows=1 loops=1)
   ->  Index Scan using ind_tbatchx on transbatch  (cost=0.00..18.73 rows=45 width=4) (actual time=0.73..148.22 rows=1 loops=1)
         Index Cond: (botnumber = '1-7'::bpchar)
 Total runtime: 148.29 msec
(4 rows)

 

 

This function is slow:-

 

CREATE OR REPLACE FUNCTION  sp_test_rod3 ( ) returns integer         
as '
DECLARE
  bot char(10);
  oldbatch INTEGER;
BEGIN

 

  bot := ''1-7'';

 

  SELECT INTO oldbatch batchserial
  FROM transbatch
  WHERE botnumber = bot
  LIMIT 1;

 

  IF FOUND THEN
    RETURN 1;
  ELSE
    RETURN 0;
  END IF;

 

END;
'
language plpgsql  ;

 

explain analyze SELECT sp_test_rod3();

                                       QUERY PLAN                                      
----------------------------------------------------------------------------------------
 Result  (cost=0.00..0.01 rows=1 width=0) (actual time=1452.39..1452.40 rows=1 loops=1)
 Total runtime: 1452.42 msec
(2 rows)