Evo obavih par testova nabrzaka, da provjerim/dokazem da li je i koliko je brze koristiti podupite (subselects), nego privremene (temporary) tabele...
Kreirao sam dvije procedure - jednu sa privremenim tabelama, a drugu sa podupitom.
Osnovna tabela sa podacima se zove
table1 i ima polja
id, value i descr, pri cemu je value auto_increment. Tu tabelu sam popunio koristeci obicnu proceduru:
Kôd:
DELIMITER $$
DROP PROCEDURE IF EXISTS `testDB`.`fill_table1` $$
CREATE PROCEDURE `fill_table1`(INOUT broj_redova INT,
OUT poruka VARCHAR(45))
BEGIN
-- deklaracija potrebnih varijabli
DECLARE v_counter INT DEFAULT 0;
DECLARE v_id INT DEFAULT 1;
DECLARE v_descr VARCHAR(45);
DECLARE v_ostatak INT DEFAULT 1;
DECLARE exit HANDLER FOR SQLEXCEPTION ROLLBACK;
-- oznacimo pocetak transakcije
START TRANSACTION;
-- petlja_za_unos_podataka
WHILE v_counter < broj_redova
DO
-- povecaj brojac za 1
SET v_counter = v_counter + 1;
-- na osnovu ostatka dobijenog kada brojac podijelimo sa 7,
-- odredjivacemo vrijednost v_id, odnosno kolone id:
SET v_ostatak = MOD (v_counter, 7);
IF v_ostatak = 1 THEN
SET v_id = 1;
ELSEIF v_ostatak = 2 THEN
SET v_id = 2;
ELSEIF v_ostatak = 3 THEN
SET v_id = 3;
ELSEIF v_ostatak = 4 THEN
SET v_id = 4;
ELSE
SET v_id = 5;
END IF;
-- ovdje vrsimo unos podataka
INSERT INTO table1 (id, descr)
VALUES (v_id, CONCAT('description var ', v_counter));
-- nakon svakih 100 iteracija (unosa), potvrdi unos sa COMMIT
IF MOD (v_counter, 100) = 0 THEN
COMMIT;
END IF;
END WHILE;
COMMIT;
SET poruka = CONCAT('Ukupno je uneseno ', v_counter, ' redova u tabelu');
END $$
DELIMITER ;
Pozovite tu proceduru sa npr. CALL fill_table1(500, @poruka).
Prva procedura sa temp tabelama -
proc_temp():
Kôd:
DELIMITER $$
DROP PROCEDURE IF EXISTS `testDB`.`proc_temp` $$
CREATE PROCEDURE `testDB`.`proc_temp` ()
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
SQL SECURITY DEFINER
BEGIN
CREATE TABLE tempdata AS
SELECT id, MIN(value) min_value, MAX(value) max_value
FROM table1
GROUP BY id
ORDER BY id;
SELECT t1.id,
t1.descr "Description za min",
t2.descr "Description za max"
FROM table1 t1,
table1 t2,
tempdata t
WHERE t1.id = t.id
AND t1.value = t.min_value
AND t2.value = t.max_value;
DROP TABLE tempdata;
END $$
DELIMITER ;
Druga procedura sa podupitom -
proc_subselect():
Kôd:
DELIMITER $$
DROP PROCEDURE IF EXISTS `testDB`.`proc_subselect` $$
CREATE PROCEDURE `testDB`.`proc_subselect` ()
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
SQL SECURITY DEFINER
BEGIN
SELECT t1.id, t1.descr, t2.descr
FROM table1 t1, table1 t2,
(select min(value) min_value, max(value) max_value from table1 group by id) sub
WHERE t1.id = t2.id
AND t1.value = sub.min_value
AND t2.value = sub.max_value;
END $$
DELIMITER ;
Rezultat prve procedure sa privremenim tabelama:
Kôd:
mysql> call proc_temp();
+----+--------------------+---------------------+
| id | Description za min | Description za max |
+----+--------------------+---------------------+
| 1 | description var 1 | description var 498 |
| 2 | description var 2 | description var 499 |
| 3 | description var 3 | description var 500 |
| 4 | description var 4 | description var 494 |
| 5 | description var 5 | description var 497 |
+----+--------------------+---------------------+
5 rows in set (0.05 sec)
Query OK, 0 rows affected (0.06 sec)
mysql>
Rezultat druge procedure sa podupitima:
Kôd:
mysql> call proc_subselect();
+----+-------------------+---------------------+
| id | descr | descr |
+----+-------------------+---------------------+
| 1 | description var 1 | description var 498 |
| 2 | description var 2 | description var 499 |
| 3 | description var 3 | description var 500 |
| 4 | description var 4 | description var 494 |
| 5 | description var 5 | description var 497 |
+----+-------------------+---------------------+
5 rows in set (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
mysql>
Dakle, uocljivo je da su
podupiti 5-6 puta brzi od koristenja privremenih tabela...