ALTER in procedure

Stefan Manegold Stefan.Manegold at cwi.nl
Tue Jul 23 13:12:47 CEST 2013


Did you try to compare *the same* query between plain SQL and in procedure?

Mind in particular 

SELECT COUNT(DISTINCT p.cislo_karty)
vs.
SELECT DISTINCT p.cislo_karty

(and I assume you use the same data and same timestampes in both/all cases)

If both of the above are fast in plain SQL but slow in a procedure,
you might want to use stethoscope [1] to find out where things hang.

Stefan

[1] http://www.monetdb.org/Documentation/Manuals/MonetDB/Profiler/Stethoscope

----- Original Message -----
> 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
> >
> _______________________________________________
> users-list mailing list
> users-list at monetdb.org
> http://mail.monetdb.org/mailman/listinfo/users-list
> 

-- 
| Stefan.Manegold at CWI.nl | DB Architectures   (DA) |
| www.CWI.nl/~manegold/  | Science Park 123 (L321) |
| +31 (0)20 592-4212     | 1098 XG Amsterdam  (NL) |




More information about the users-list mailing list