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é :
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 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;
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.
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
Partager