Discussion :

[Sebwar]

Bonjour,

Je constate que l'execution de mon SQL est plus rapide que le même SQL executé via une procédure stocké. Est-ce normal ?

Mon code SQL met 0.0014 seconde a s'executé :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
CREATE TEMPORARY TABLE TMP_MAX_ID (HTR_ID INT);
INSERT INTO TMP_MAX_ID (HTR_ID) SELECT MAX(HTR_ID) FROM TG_HISTORIC_HTR WHERE HST_ID = 1 GROUP BY CTR_ID;
 
CREATE TEMPORARY TABLE TMP_LAST_VALUE (HTR_ID INT, HTA_VALUE VARCHAR(20));
INSERT INTO TMP_LAST_VALUE (HTR_ID, HTA_VALUE) SELECT A.HTR_ID, HTI_VALUE FROM T_HISTORIC_INTEGER_HTI A INNER JOIN TMP_MAX_ID B ON A.HTR_ID = B.HTR_ID;
INSERT INTO TMP_LAST_VALUE (HTR_ID, HTA_VALUE) SELECT A.HTR_ID, HTD_VALUE FROM T_HISTORIC_DECIMAL_HTD A INNER JOIN TMP_MAX_ID B ON A.HTR_ID = B.HTR_ID;
INSERT INTO TMP_LAST_VALUE (HTR_ID, HTA_VALUE) SELECT A.HTR_ID, HTS_VALUE FROM T_HISTORIC_STRING_HTS A INNER JOIN TMP_MAX_ID B ON A.HTR_ID = B.HTR_ID;
INSERT INTO TMP_LAST_VALUE (HTR_ID, HTA_VALUE) SELECT A.HTR_ID, HTX_VALUE FROM T_HISTORIC_TEXT_HTX A INNER JOIN TMP_MAX_ID B ON A.HTR_ID = B.HTR_ID;
INSERT INTO TMP_LAST_VALUE (HTR_ID, HTA_VALUE) SELECT A.HTR_ID, HTE_VALUE FROM T_HISTORIC_DELTA_HTE A INNER JOIN TMP_MAX_ID B ON A.HTR_ID = B.HTR_ID;
 
SELECT
	ITM_NAME,
	HST_NAME,
	HTA_VALUE
FROM
	T_HOST_HST A INNER JOIN
	TJ_USE_USE B ON A.HST_ID = B.HST_ID INNER JOIN
	TJ_CONTAIN_CTN C ON B.TPL_ID = C.TPL_ID INNER JOIN
	T_CONTROL_CTR D ON C.CTR_ID = D.CTR_ID INNER JOIN
	T_ITEM_ITM E ON D.ITM_ID = E.ITM_ID LEFT JOIN
	TG_HISTORIC_HTR F ON D.CTR_ID = F.CTR_ID AND A.HST_ID = F.HST_ID INNER JOIN 
	TMP_LAST_VALUE G ON F.HTR_ID = G.HTR_ID
WHERE
	A.HST_ID = 1;

La procédure - call sp_last_value(1) - met 0.2084 seconde :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
CREATE PROCEDURE SP_LAST_VALUE (VAR_HST_ID INT)
BEGIN
	CREATE TEMPORARY TABLE TMP_MAX_ID (HTR_ID INT);
	INSERT INTO TMP_MAX_ID (HTR_ID) SELECT MAX(HTR_ID) FROM TG_HISTORIC_HTR WHERE HST_ID = VAR_HST_ID GROUP BY CTR_ID;
 
	CREATE TEMPORARY TABLE TMP_LAST_VALUE (HTR_ID INT, HTA_VALUE VARCHAR(20));
	INSERT INTO TMP_LAST_VALUE (HTR_ID, HTA_VALUE) SELECT A.HTR_ID, HTI_VALUE FROM T_HISTORIC_INTEGER_HTI A INNER JOIN TMP_MAX_ID B ON A.HTR_ID = B.HTR_ID;
	INSERT INTO TMP_LAST_VALUE (HTR_ID, HTA_VALUE) SELECT A.HTR_ID, HTD_VALUE FROM T_HISTORIC_DECIMAL_HTD A INNER JOIN TMP_MAX_ID B ON A.HTR_ID = B.HTR_ID;
	INSERT INTO TMP_LAST_VALUE (HTR_ID, HTA_VALUE) SELECT A.HTR_ID, HTS_VALUE FROM T_HISTORIC_STRING_HTS A INNER JOIN TMP_MAX_ID B ON A.HTR_ID = B.HTR_ID;
	INSERT INTO TMP_LAST_VALUE (HTR_ID, HTA_VALUE) SELECT A.HTR_ID, HTX_VALUE FROM T_HISTORIC_TEXT_HTX A INNER JOIN TMP_MAX_ID B ON A.HTR_ID = B.HTR_ID;
	INSERT INTO TMP_LAST_VALUE (HTR_ID, HTA_VALUE) SELECT A.HTR_ID, HTE_VALUE FROM T_HISTORIC_DELTA_HTE A INNER JOIN TMP_MAX_ID B ON A.HTR_ID = B.HTR_ID;
 
	SELECT
		ITM_NAME,
		HST_NAME,
		HTA_VALUE
	FROM
		T_HOST_HST A INNER JOIN
		TJ_USE_USE B ON A.HST_ID = B.HST_ID INNER JOIN
		TJ_CONTAIN_CTN C ON B.TPL_ID = C.TPL_ID INNER JOIN
		T_CONTROL_CTR D ON C.CTR_ID = D.CTR_ID INNER JOIN
		T_ITEM_ITM E ON D.ITM_ID = E.ITM_ID LEFT JOIN
		TG_HISTORIC_HTR F ON D.CTR_ID = F.CTR_ID AND A.HST_ID = F.HST_ID INNER JOIN 
		TMP_LAST_VALUE G ON F.HTR_ID = G.HTR_ID
	WHERE
		A.HST_ID = VAR_HST_ID;
 
END

Je ne sais pas si ça a un impact, j’exécute tout ça a partir du phpmyadmin, n'ayant pour l'instant, pas accès au serveur.