Description of problem:
Having many Hostgroups, esp. with nesting, then WebUI form to generate register command takes many seconds to react.

E.g. for 400 HGs with several levels of nesting hierarchy, choosing from some menus on /hosts/register URI takes 10+ seconds. Since there are three such selections, it affects user experience significantly.

Version-Release number of selected component (if applicable):
Sat 6.14 (applicable to older versions as well)

How reproducible:
100%

Steps to Reproduce:
1. Create 400 child Hostgroups with some parents, e.g. artificial but very evident use case. The 400 HGs is a real number from a customer.

hammer hostgroup create --name HG_GGGbase
hammer hostgroup create --name HG_GGbase --parent-title=HG_GGGbase --organization-id 1
hostgroup create --name HG_Gbase --parent-title=HG_GGGbase/HG_GGbase --organization-id 1 --location-id 2
hostgroup create --name HG_base --parent-title=HG_GGGbase/HG_GGbase/HG_Gbase --organization-id 1 --location-id 2 --lifecycle-environment-id 1 --content-view-id 1

for i in $(seq 0 9); do echo "hostgroup create --name HG_par${i} --lifecycle-environment-id 1 --content-view-id 1 --organization-id 1 --location-id 2 --operatingsystem-id 2 --parent-title=HG_GGGbase/HG_GGbase/HG_Gbase/HG_base"; done | hammer shell

for i in $(seq 1 400); do echo "hostgroup create --name HG_child_${i} --lifecycle-environment-id 1 --content-view-id 1 --organization-id 1 --location-id 2 --operatingsystem-id 2 --parent-title=HG_GGGbase/HG_GGbase/HG_Gbase/HG_base/HG_par$((i%10))"; done | hammer shell

2. Optionally, have 10+ Operating Systems and 10+ Activation Keys - these are also some (small) scalability factors.
3. Navigate in WebUI: Hosts -> Register Host , and select options from drop-down menu.
4. Monitor duration of each such selection, also via production.log

Actual results:
3. Huge delays in response times when initially loading the page or after selecting Hostgroup or OperatingSystem.
4. Example of the response times per production.log:

2023-12-05T13:06:21 [I|app|342a80a5] Started GET "/hosts/register/data?organization_id=1&location_id=2" for 1.2.3.4 at 2023-12-05 13:06:21 +0100
2023-12-05T13:06:39 [I|app|342a80a5] Completed 200 OK in 18113ms (Views: 78.2ms | ActiveRecord: 443.5ms | Allocations: 10128705)

2023-12-05T13:06:56 [I|app|e2a2357e] Started GET "/hosts/register/data?organization_id=1&location_id=2&hostgroup_id=1251" for 10.45.225.72 at 2023-12-05 13:06:56 +0100
2023-12-05T13:06:56 [I|app|66efc044] Started GET "/hosts/register/data?organization_id=1&location_id=2&hostgroup_id=1251&operatingsystem_id=" for 10.45.225.72 at 2023-12-05 13:06:56 +0100
2023-12-05T13:07:15 [I|app|66efc044] Completed 200 OK in 18819ms (Views: 80.7ms | ActiveRecord: 457.3ms | Allocations: 10160321)
2023-12-05T13:07:16 [I|app|e2a2357e] Completed 200 OK in 19124ms (Views: 75.1ms | ActiveRecord: 483.0ms | Allocations: 10160507)

(why there are two identical? that is a bug "per se"..)

2023-12-05T13:07:27 [I|app|4a679061] Started GET "/hosts/register/data?organization_id=1&location_id=2&hostgroup_id=1251&operatingsystem_id=2" for 10.45.225.72 at 2023-12-05 13:07:27 +0100
2023-12-05T13:07:45 [I|app|4a679061] Completed 200 OK in 18231ms (Views: 75.8ms | ActiveRecord: 468.5ms | Allocations: 10160883)

Expected results:
Response times within a second, regardless of number of Hostgroups.

Additional info:
Enable sql debugs to see evident redundant AND durable requests made for each request. E.g.:

2023-12-05T13:07:27 [I|app|4a679061] Started GET "/hosts/register/data?organization_id=1&location_id=2&hostgroup_id=1251&operatingsystem_id=2" for 10.45.225.72 at 2023-12-05 13:07:27 +0100
2023-12-05T13:07:27 [I|app|4a679061] Processing by RegistrationCommandsController#form_data as JSON
2023-12-05T13:07:27 [I|app|4a679061] Parameters:

..
2023-12-05T13:07:27 [D|sql|4a679061] (0.5ms) SELECT DISTINCT "taxable_taxonomies"."taxable_id" FROM "taxable_taxonomies" WHERE "taxable_taxonomies"."taxable_type" = $1 AND "taxable_taxonomies"."taxonomy_id" = $2 [["taxable_type", "Hostgroup"], ["taxonomy_id", 1]]
2023-12-05T13:07:27 [D|sql|4a679061] Hostgroup Load (4.9ms) SELECT "hostgroups".* FROM "hostgroups" WHERE (hostgroups.id IN (1228,3,1229,1230,1231,1232,1233,1234,1235,1236,1237,1238,1239,1240,1241,1242,1243,1244,1245,1246,1247,1248,1249,1250,1251,1252,1253,1254,1255,1256,1257,1258,1259,1260,1261,1262,1263,1264,1265,1266,1267,1268,1269,1270,1271,1272,1273,1274,1275,1276,1277,1278,1279,1280,1281,1282,1283,1284,1285,1286,1287,1288,1289,1290,1291,1292,1293,1294,1295,1296,1297,1298,1299,1300,1301,1302,1303,1304,1305,1306,1307,1308,1309,1310,1311,1312,1313,1314,1315,1316,1317,1318,1319,1320,1321,1322,1323,1324,1325,1326,1327,1328,1329,1330,1331,1332,1333,1334,1335,1336,1337,1338,1339,1340,1341,1342,1343,1344,1345,1346,1347,1348,1349,1350,1351,1352,1353,1354,1355,1356,1357,1358,1359,1360,1361,1362,1363,1364,1365,1366,1367,1368,1369,1370,1372,1374,1375,1376,1377,1380,1381,1382,1385,1386,1387,1388,1389,1390,1393,1394,1395,1396,1397,1398,1399,1404,1405,1406,1412,1414,1415,1419,1420,1421,1371,1373,1378,1379,1383,1384,1391,1392,1400,1401,1402,1403,1407,1408,1409,1410,1411,1413,1416,1417,1418,1422,1423,1424,1425,1426,1427,1428,1429,1430,1431,1432,1433,1434,1435,1436,1437,1438,1439,1440,1441,1442,1443,1447,1448,1449,1450,1453,1454,1455,1456,1457,1458,1459,1460,1461,1462,1444,1445,1446,1451,1452,1463,1464,1465,1466,1467,1468,1469,1470,1471,1472,1473,1474,1475,1476,1477,1478,1479,1480,1481,1482,1483,1484,1485,1486,1487,1488,1489,1490,1491,1492,1493,1494,1495,1496,1497,1498,1499,1500,1501,1502,1503,1504,1505,1506,1507,1508,1509,1510,1511,1512,1513,1514,1515,1516,1517,1518,1519,1520,1521,1522,1523,1524,1525,1526,1528,1529,1530,1532,1533,1535,1538,1539,1541,1527,1531,1534,1536,1537,1540,1542,1543,1544,1545,1546,1547,1548,1549,1550,1551,1552,1553,1554,1555,1556,1557,1558,1559,1560,1561,1562,1563,1564,1565,1566,1567,1568,1569,1570,1571,1572,1575,1578,1581,1582,1588,1589,1592,1595,1596,1597,1598,1599,1600,1601,1602,1603,1604,1605,1608,1609,1614,1615,1617,1618,1619,1620,1621,1624,1627,1628,1629,1573,1574,1576,1577,1579,1580,1583,1584,1585,1586,1587,1590,1591,1593,1594,1606,1607,1610,1611,1612,1613,1616,1622,1623,1625,1626,1631,1630,1632,1633,1634,1635,1636,1637,1638,1639,1640,1641)) ORDER BY hostgroups.title
..
2023-12-05T13:07:27 [D|sql|4a679061] CACHE (0.0ms) SELECT DISTINCT "taxable_taxonomies"."taxable_id" FROM "taxable_taxonomies" WHERE "taxable_taxonomies"."taxable_type" = $1 AND "taxable_taxonomies"."taxonomy_id" = $2 [["taxable_type", "Hostgroup"], ["taxonomy_id", 1]]
2023-12-05T13:07:27 [D|sql|4a679061] Hostgroup Load (0.5ms) SELECT "hostgroups".* FROM "hostgroups" WHERE (hostgroups.id IN (1228,3,1229,1230,1231,1232,1233,1234,1235,1236,1237,1238,1239,1240,1241,1242,1243,1244,1245,1246,1247,1248,1249,1250,1251,1252,1253,1254,1255,1256,1257,1258,1259,1260,1261,1262,1263,1264,1265,1266,1267,1268,1269,1270,1271,1272,1273,1274,1275,1276,1277,1278,1279,1280,1281,1282,1283,1284,1285,1286,1287,1288,1289,1290,1291,1292,1293,1294,1295,1296,1297,1298,1299,1300,1301,1302,1303,1304,1305,1306,1307,1308,1309,1310,1311,1312,1313,1314,1315,1316,1317,1318,1319,1320,1321,1322,1323,1324,1325,1326,1327,1328,1329,1330,1331,1332,1333,1334,1335,1336,1337,1338,1339,1340,1341,1342,1343,1344,1345,1346,1347,1348,1349,1350,1351,1352,1353,1354,1355,1356,1357,1358,1359,1360,1361,1362,1363,1364,1365,1366,1367,1368,1369,1370,1372,1374,1375,1376,1377,1380,1381,1382,1385,1386,1387,1388,1389,1390,1393,1394,1395,1396,1397,1398,1399,1404,1405,1406,1412,1414,1415,1419,1420,1421,1371,1373,1378,1379,1383,1384,1391,1392,1400,1401,1402,1403,1407,1408,1409,1410,1411,1413,1416,1417,1418,1422,1423,1424,1425,1426,1427,1428,1429,1430,1431,1432,1433,1434,1435,1436,1437,1438,1439,1440,1441,1442,1443,1447,1448,1449,1450,1453,1454,1455,1456,1457,1458,1459,1460,1461,1462,1444,1445,1446,1451,1452,1463,1464,1465,1466,1467,1468,1469,1470,1471,1472,1473,1474,1475,1476,1477,1478,1479,1480,1481,1482,1483,1484,1485,1486,1487,1488,1489,1490,1491,1492,1493,1494,1495,1496,1497,1498,1499,1500,1501,1502,1503,1504,1505,1506,1507,1508,1509,1510,1511,1512,1513,1514,1515,1516,1517,1518,1519,1520,1521,1522,1523,1524,1525,1526,1528,1529,1530,1532,1533,1535,1538,1539,1541,1527,1531,1534,1536,1537,1540,1542,1543,1544,1545,1546,1547,1548,1549,1550,1551,1552,1553,1554,1555,1556,1557,1558,1559,1560,1561,1562,1563,1564,1565,1566,1567,1568,1569,1570,1571,1572,1575,1578,1581,1582,1588,1589,1592,1595,1596,1597,1598,1599,1600,1601,1602,1603,1604,1605,1608,1609,1614,1615,1617,1618,1619,1620,1621,1624,1627,1628,1629,1573,1574,1576,1577,1579,1580,1583,1584,1585,1586,1587,1590,1591,1593,1594,1606,1607,1610,1611,1612,1613,1616,1622,1623,1625,1626,1631,1630,1632,1633,1634,1635,1636,1637,1638,1639,1640,1641)) AND "hostgroups"."id" IN (1228, 1229) ORDER BY "hostgroups"."ancestry" ASC NULLS FIRST

That is repeated many times - esp. "order by ancestry" (which already has an index..)

And all the "get me Hostgroups" requests are repeated even for client request that already specified Hostgroup (so we should check just ancestry of the selected HG and dont care of others, no..?)