Nezgodan upit

[Dejan Topalovic]

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:

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():

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():

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:

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:

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...