Schematic Choose Symbol loading very slow with database connection

Software
1

Hello,

I’ve created my part database on SQL and after linking it to KiCad i encounter a problem when trying to open the Choose Symbol menu.

I’ve measured the time and currently it takes around 80 seconds for it to open. After this everything is pretty smooth as usual. The database itself is pretty small (~450 entries), so not sure why it’s taking that long. Most of the components share the same symbol and footprint (e.g. SMA capacitors and resistors) and there are only a few unique (in terms of symbol and footprint) components as of yet.

Also I’ve noticed with the Task Manager that the CPU usage is not that high during this process, but the disk usage is around ~2-4MB/s for the whole time when loading the Choose Symbol. The task manager reports it to be write operations mainly. I’m using an SSD so it shouldn’t be a throughput issue. I’m also not using any antivirus apart from built in windows defender. The symbol and footprint libraries are on local disk and the database is on external server.

The Standard KiCad libraries are inactive and not visible and I am not using them. So far I’ve always thought that KiCad caches the database locally and refreshes it from time to time, but it seems not to be the case.

I’ll appreciate if anyone has an idea what could cause this or how to improve the loading time.

Edit: It seems that if I instead use the change symbol feature on existing schematic component the library loads much faster (6s-10s). Maybe because it’s not loading footprint previews?

Application: KiCad Schematic Editor x64 on x64
Version: 8.0.1, release build
Libraries:
    wxWidgets 3.2.4
    FreeType 2.12.1
    HarfBuzz 8.3.0
    FontConfig 2.14.2
    libcurl/8.5.0-DEV Schannel zlib/1.3
Platform: Windows 11 (build 22631), 64-bit edition, 64 bit, Little endian, wxMSW
Build Info:
    Date: Mar 15 2024 01:52:47
    wxWidgets: 3.2.4 (wchar_t,wx containers)
    Boost: 1.83.0
    OCC: 7.7.1
    Curl: 8.5.0-DEV
    ngspice: 42
    Compiler: Visual C++ 1936 without C++ ABI
Build settings:
2

i don’t think so, the symbol and footprint are local files on your machine isn’t it?

Which OS is hosting the DB?

My DB is much bigger (in the order of 15000 items, we tested both MySQL and MariaDB no perceived difference), we don’t see so large delays, just a 2…3 secs to startup, then faster (both win10 and Linux).

I have seen that when connected in remote with VPN the speed decreases, but nothing of the order of yours.

Do you have some tool to verify the performance of the DB? for example i use MySQL workbench, and use the performance dashboard for a general view.

3

Thanks for the reply,

Well there’s definitely some difference in the load time as add part button takes the 80s and change symbol one takes lets say 8s:
obraz
So the queries must differ between those two functionalities. But that’s just a side observation.

For the questions regarding the database, I’ll have to get this info from my colleague as he did the setup for the server. Unfortunately he’s on vacation till end of April, so I’ll have to wait for that. If I somehow get this info earlier then I’ll post it.

Anyway, thanks for sharing that on your setup it is working fine as it proves it is possible to set it up so it works correctly. This gives me a hint that I should look a bit deeper into database configuration. Maybe the current server setup is limiting access speed for the user.

4

yes, if i’ve got to place a bet, i’d say that the different behavior could arise from the component filters in the symbol chooser, just a conjecture without knowledge of the code internals.

Maybe craftyjon will chime-in in the discussion some time.

5

I have some updates in the topic:

  1. I did a check on a different machine with the same connection and there it it working fine. The main difference is that there is a win10 and kicad 8.0.0 there and maybe something different that I am not aware of. So in the server/DB side the configuration should be fine
  2. I’ve added kicad.exe to windows security exception - no improvement in performance
  3. I did find one option slowing down the connection which was enabled tracing in the ODBC driver. With tacing turned off there is an increase in the speed. But since I’ve found a place where I can track exact communication with the database I did some experimenting.
  4. I’ve made a test on an empty project and tested the Choose Symbol operation. The observations are:
    When there are no symbols from the database on the schematic there is one ENTER SQLPrepareW for each table present in the database. This changes however when components from the database are placed on the schematic.
  • 0 components: 1 occurence
  • 2 components: 5 occurences
  • 5 components: 11 occurences
  • 1078 components: 606 occurences (tool like 20minutes and produced a whopping 90MB log file from this single button press)
    So it seems that for each component from the database present on the schematic additional 2 requests to refresh database are made. Why is that I have no idea.

For some reason, this scaling does not occur on my colleague’s PC.