From 746166e4c23cb6bbb60d104b5c79df119f7e161b Mon Sep 17 00:00:00 2001 From: "Jose M. Guisado" Date: Fri, 16 Apr 2021 10:33:51 +0000 Subject: #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. --- src/ogAdmServer.c | 13 ------------- 1 file changed, 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" -- cgit v1.2.3-18-g5258