Welcome to OCS Inventory NG community support, where you can ask questions and receive answers from other members of the community.

Please ask questions only in English or French.

Release 2.11.1 available

The official documentation can be found on http://wiki.ocsinventory-ng.org. Read it before asking your question.

OCS search slow due to memories table size


We have a large deployment and with 2.6 version, we have a latency issue while searching through the inventory.

The default SQL request is:

SELECT SQL_CALC_FOUND_ROWS a.fields_3, a.TAG, h.lastdate, h.name, h.ID, h.userid, h.osname, h.memory as capa, h.processors, h.workgroup, h.osversion, h.oscomments, h.processort, h.processorn, h.swap, h.lastcome, h.quality, h.fidelity, h.description, h.wincompany, h.winowner, h.useragent, e.smanufacturer, e.bmanufacturer, e.ssn, e.smodel, e.bversion, GROUP_CONCAT(DISTINCT CASE WHEN n.ipaddress != 0x20 THEN n.ipaddress ELSE NULL END SEPARATOR 0x2D) as ipaddress, h.userdomain, h.ARCH, e.bdate, v.name as vname, ac.category_name, n.macaddr, n.ipmask, n.ipgateway, n.ipsubnet 

from hardware h LEFT JOIN accountinfo a ON a.hardware_id=h.id 

LEFT JOIN networks n ON n.hardware_id=h.id 

LEFT JOIN bios e ON e.hardware_id=h.id 

LEFT JOIN memories m ON m.hardware_id=h.id 

LEFT JOIN assets_categories ac ON ac.ID = h.CATEGORY_ID 

LEFT JOIN videos v ON v.hardware_id=h.id 

where deviceid<>'_SYSTEMGROUP_' AND deviceid<>'_DOWNLOADGROUP_' AND n.STATUS = 'up' 

group by h.id 

order by a.fields_3 asc limit 0 , 10

This takes 8 to 12 seconds and mainly due to this step 

Copying To Tmp Table8.7 s


If I remove the memories for the SQL request, the result is lower than 1 second.
Can you improve the default SQL request?
in OCS Inventory NG server for Unix by (300 points)

2 Answers

0 votes

Could you test a nightly build to verify if the problem is still present?

Nightly builds are available here => http://download.ocsinventory-ng.org/nightly/

Please open an issue on https://github.com/OCSInventory-NG/OCSInventory-ocsreports/issues/new/choose


by (88.5k points)
0 votes

I tried the nightly build and the issue is almost the same.

It is no more Copying Tmp Table but Sending Data

The bug is https://github.com/OCSInventory-NG/OCSInventory-ocsreports/issues/903

by (300 points)
Powered by Question2Answer