summaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorJose M. Guisado <jguisado@soleta.eu>2021-04-16 10:33:51 +0000
committerOpenGnSys Support Team <soporte-og@soleta.eu>2021-04-16 13:31:45 +0200
commit746166e4c23cb6bbb60d104b5c79df119f7e161b (patch)
treee932ffdd5693d39345208edfde36ae53b263336e
parent42c22539a3970395ebfd6c0f306ca18624089293 (diff)
#971 Remove sql bottleneck when removing software profiles
Several universities have reported that creating a software profile hangs the machine running the ogServer for a while, sometimes up to minutes. Legacy SQL code is producing said bottleneck, responsible for pruning a intermediate table between "perfilessoft" and "softwares". There is redundant code, "perfilssoft" should be pruned first, speeding up the later task of pruning the intermediate table "perfilessoft_softwares" There is no need to execute: DELETE FROM perfilessoft_softwares WHERE idperfilsoft IN ( SELECT idperfilsoft FROM perfilessoft WHERE idperfilsoft NOT IN ( SELECT DISTINCT idperfilsoft from ordenadores_particiones) AND idperfilsoft NOT IN ( SELECT DISTINCT idperfilsoft from imagenes)) When afterwards "perfilessoft" is going to be pruned and "perfilessoft_softwares" pruned again: DELETE FROM perfilessoft WHERE idperfilsoft NOT IN (SELECT DISTINCT idperfilsoft from ordenadores_particiones) AND idperfilsoft NOT IN (SELECT DISTINCT idperfilsoft from imagenes) DELETE FROM perfilessoft_softwares WHERE idperfilsoft NOT IN (SELECT idperfilsoft from perfilessoft) The two latter commands suffice. This should not happen when using a relational database supporting foreign keys and ON DELETE CASCADE, like innoDB, which will be adopted soon.
-rw-r--r--src/ogAdmServer.c13
1 files changed, 0 insertions, 13 deletions
diff --git a/src/ogAdmServer.c b/src/ogAdmServer.c
index 9da249e..77ddff8 100644
--- a/src/ogAdmServer.c
+++ b/src/ogAdmServer.c
@@ -1024,19 +1024,6 @@ bool cuestionPerfilSoftware(struct og_dbi *dbi, char *idc, char *ido,
/* DEPURACIÓN DE PERFILES SOFTWARE */
- /* Eliminar Relación de softwares con Perfiles software que quedan húerfanos */
- result = dbi_conn_queryf(dbi->conn,
- "DELETE FROM perfilessoft_softwares WHERE idperfilsoft IN "\
- " (SELECT idperfilsoft FROM perfilessoft WHERE idperfilsoft NOT IN"\
- " (SELECT DISTINCT idperfilsoft from ordenadores_particiones) AND idperfilsoft NOT IN"\
- " (SELECT DISTINCT idperfilsoft from imagenes))");
- if (!result) {
- dbi_conn_error(dbi->conn, &msglog);
- syslog(LOG_ERR, "failed to query database (%s:%d) %s\n",
- __func__, __LINE__, msglog);
- return false;
- }
- dbi_result_free(result),
/* Eliminar Perfiles software que quedan húerfanos */
result = dbi_conn_queryf(dbi->conn,
"DELETE FROM perfilessoft WHERE idperfilsoft NOT IN"