ALTER in procedure

Radovan Bičiště radovan.biciste at ceosdata.com
Tue Jul 23 14:01:51 CEST 2013


I'm really sorry for the mistake. Indeed it works the same now both 
procedure and plain SQL.
I'm going to keep looking around ...

Thank you for your help.
Radovan


On 07/23/2013 01:12 PM, Stefan Manegold wrote:
> 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
>>



More information about the users-list mailing list