Hello, This is my first post here. I tried to search the archive but did not find anything related. We build application and would like disable foreign keys in a procedure. Something like: CREATE PROCEDURE crmkartaaktivita_drop_cons() BEGIN -- hlavička procedury DECLARE v_proc VARCHAR(250); SET v_proc = 'crmkartaaktivita_drop_cons'; -- drop ALTER TABLE crmkartastitek DROP CONSTRAINT crmkartastitek_crmkartaaktivita_FK; ALTER TABLE crmkartastitekm DROP CONSTRAINT crmkartastitekm_crmkartaakitvita_FK; END; / I'm getting "Statement 'ALTER_TABLE' is not a valid flow control statement". Is there any way around please? Our algorithm should like this: 1. disable/drop foreign keys. 2. load data. 3. enable/create constraints. Thanks, Radovan -- Radovan Biciste Czech Republic
Hi Radovan, I'm afraid that MonetDB does not allow any schema manipulation with SQL procedures (or functions). I'm actually not sure, whether the SQL standard allows that ... Having said that, why would you want to drop constraints while loading data, only to add them afterwards, again? Best, Stefan ----- Original Message -----
Hello, This is my first post here. I tried to search the archive but did not find anything related. We build application and would like disable foreign keys in a procedure. Something like:
CREATE PROCEDURE crmkartaaktivita_drop_cons() BEGIN -- hlavička procedury DECLARE v_proc VARCHAR(250); SET v_proc = 'crmkartaaktivita_drop_cons'; -- drop ALTER TABLE crmkartastitek DROP CONSTRAINT crmkartastitek_crmkartaaktivita_FK; ALTER TABLE crmkartastitekm DROP CONSTRAINT crmkartastitekm_crmkartaakitvita_FK; END; /
I'm getting "Statement 'ALTER_TABLE' is not a valid flow control statement".
Is there any way around please? Our algorithm should like this: 1. disable/drop foreign keys. 2. load data. 3. enable/create constraints.
Thanks, Radovan -- Radovan Biciste Czech Republic _______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |
Hi Stefan, Thank you for a brisk answer. I'm aware of the conflict with standards. We are mostly Oracle shop. Reason for disabling constraints is speed of loading. 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; The big table is crmkartapohyby. Data to the table is fed from Oracle through mclient java interface. Thank you for any hints, Radovan On 07/23/2013 09:55 AM, Stefan Manegold wrote:
Hi Radovan,
I'm afraid that MonetDB does not allow any schema manipulation with SQL procedures (or functions). I'm actually not sure, whether the SQL standard allows that ...
Having said that, why would you want to drop constraints while loading data, only to add them afterwards, again?
Best, Stefan
Hi Stefan, Thank you for a brisk answer. I'm aware of the conflict with standards. We are mostly Oracle shop.
Reason for disabling constraints is speed of loading.
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
The big table is crmkartapohyby. Data to the table is fed from Oracle through mclient java interface.
Thank you for any hints, Radovan
On 07/23/2013 09:55 AM, Stefan Manegold wrote:
Hi Radovan,
I'm afraid that MonetDB does not allow any schema manipulation with SQL procedures (or functions). I'm actually not sure, whether the SQL standard allows that ...
Having said that, why would you want to drop constraints while loading data, only to add them afterwards, again?
Best, Stefan
_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |
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
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@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |
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
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@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
participants (2)
-
Radovan Bičiště -
Stefan Manegold