Re: ALTER in procedure

23 Jul 2013

      Hi,
I looks like different kind of issue. I'm running into problem when 
almost the same SQL running separately takes 5 seconds and in a 
procedure it never finishes (30 minutes ...).

Following insert takes 5 seconds:
INSERT INTO crmkartastitek (
       cislo_karty,
       stitek_id,
       stitek_nazev,
       stitek_upresneni
      )
SELECT COUNT(DISTINCT p.cislo_karty),120,'sdfgdf','sdfgsdf'
      FROM crmkartaaktivita k
      JOIN crmkartapohyby p ON p.cislo_karty=k.cislo_karty
      WHERE 1=1
        AND k.aktivita > 0
        AND p.pohyb = 'VV'
        AND p.datum BETWEEN (timestamp'2013-07-01 00:00:00' - INTERVAL 
'12' MONTH) AND (timestamp'2013-07-01 00:00:00' - INTERVAL '1' DAY)

Following procedure never returns:
DROP PROCEDURE crm_segm_kvv_rl
/

-- vyhodnotí pravidlo pro štítek KKV
-- @param p_interval dle číselníku crmregstitek
-- @param p_datum timestamp, první den v měsíci, např. timestamp 
'2013-07-01 00:00:00'
CREATE PROCEDURE crm_segm_kvv_rl(p_interval INT, p_datum timestamp)
-- $Id: crm_segm_kvv_rl.sql 958 2013-07-22 08:40:30Z rbiciste $
BEGIN
   -- hlavička procedury
   DECLARE v_proc VARCHAR(250);
   SET v_proc = 'crm_segm_kvv_rl';
   -- nastav id pravidla
   DECLARE v_stitek_id int;
   DECLARE v_stitek_nazev,v_stitek_upresneni VARCHAR(200);
   -- nastav štítek
   SET v_stitek_id = 100;
   SELECT nazev,upresneni INTO v_stitek_nazev,v_stitek_upresneni FROM 
crmregstitek WHERE stitek_id = v_stitek_id;
   -- proveď výpočet pouze pokud spouštím měsíční přepočet
   IF p_interval = crm_segm_vrat_spusteni(v_stitek_id) THEN
     INSERT INTO crmkartastitek (
       cislo_karty,
       stitek_id,
       stitek_nazev,
       stitek_upresneni
      )
      SELECT DISTINCT 
p.cislo_karty,v_stitek_id,v_stitek_nazev,v_stitek_upresneni
      FROM crmkartaaktivita k
      JOIN crmkartapohyby p ON p.cislo_karty=k.cislo_karty
      WHERE 1=1
        AND k.aktivita > 0
        AND p.pohyb = 'VV'
        AND p.datum BETWEEN (p_datum - INTERVAL '12' MONTH) AND (p_datum 
- INTERVAL '1' DAY);
   END IF;
END;
/

-- tests
call crm_segm_kvv_rl(1,timestamp '2013-07-01 00:00:00')
/

I tried:
1) comment out IF
2) replace variables v_xxx for constants like 'dfghdfgh' etc

Maybe I'm trying to do things Oracle way... but have not learned the 
monet way yet.

Thank you,
Radovan

On 07/23/2013 10:23 AM, Stefan Manegold wrote:
...
Be aware that MonetDB will check/validate the constraints in either case,
i.e., either during loading (copy into ?) in case constraints are enable,
or once you re-enabled them after loading with constraints disabled.
Did you test the speed difference between loading with constraints enabled vs. loading with constraints disabled plus enabling (checking) the constraints afterwards?
(you can do this "by hand" in plain SQL, i.e., outside any procedure)
If so, what are your findings?
...
But maybe we
should use different approach like indexing. We have started to use
MonetDB recently.
We have a table hundreds millions of rows and we are tagging data in it.
Example SQL:
      INSERT INTO crmkartastitek (
        cislo_karty,
        stitek_id,
        stitek_nazev,
        stitek_upresneni
       )
       SELECT DISTINCT cislo_karty,v_stitek_id,v_stitek_nazev,v_stitek_upresneni
        FROM (SELECT cislo_karty
          FROM crmkartapohyby p
          JOIN crmkartaaktivita k ON p.cislo_karty=k.cislo_karty
          WHERE 1=1
           AND k.aktivita > 0
           AND p.pohyb IN ('VR','VV')
           AND p.datum BETWEEN (p_datum - INTERVAL '12' MONTH) AND (p_datum - INTERVAL '1' DAY)
          GROUP BY p.cislo_karty
          HAVING SUM(castka_r + castka_d) > SUM(castka_v)
        ) AS sumy_castek_pohybu;
What exactly it the problem (if any)?
Stefan

Re: ALTER in procedure

Radovan Bičiště