ALTER in procedure

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


Seems like I found the root cause hopefully. Replaced DISTINCT with 
GROUP BY and it is working beautifully (in 15 secs).
It looks more like mindset shift. :)
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
>>
>>
>>
>>
>>
>> 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
>>
>

-- 
__________________________

Radovan Bičiště
ceos data s.r.o.
třída SNP 402/48
500 03 Hradec Králové
Czech Republic

mobil CZ: +420 601 563 014
skype: rbiciste



More information about the users-list mailing list