ALTER in procedure

Radovan Bičiště radovan.biciste at ceosdata.com
Tue Jul 23 12:41:41 CEST 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
>



More information about the users-list mailing list