Where is the bottleneck?

miknav
miknav Member Posts: 13
edited 2023-03-12 in NAV/Navision Classic Client
Hi, NAV5 with file database here.
a report runs for about 100 seconds on old HW with 10K HDDs and Win Server. The repotr is just doing some filters and iterations on some tables.
I have upgraded to a server several generations newer with shining CPU and memory, brought in plenty of SSDs, split database in files among them and guess what- it runs 180s in average now:(
However, I can't find the bottleneck - drives usage 5%, same for memory, cpu and network. Everyone is speaking about drives, but there is no big difference if I use 1 or 3 SSDs or even 15K HDD.
I just can't find out what's slowing it down.
Any ideas please?

Answers

  • bbrown
    bbrown Member Posts: 3,268
    Did anyone actually look at the report's performance or did the "experts" just guess?

    Guessing is the worse thing you can do. investigate the actual performance using something like SQL Profiler or the NAV performance analyzer that was available back then. Or perhaps both.

    There are no bugs - only undocumented features.
  • miknav
    miknav Member Posts: 13
    bbrown wrote: »
    SQL Profiler or the NAV performance analyzer that was available back then. Or perhaps both.
    This is native db. Is NAV performance analyzer available for native db?
    I think the bottleneck could be on the client side, the report uses a lot of caching and virtual tables and using better CPU makes some difference. However, it could still be better, and the CPU core making the calculations is not getting used like it could.
  • bbrown
    bbrown Member Posts: 3,268
    I didn't notice that this was the native DB. You did mention that. Been a long time since I've worked with that. I remember it being very limited in the hardware it could use. 1 GB RAM and 1 CPU is what I recall. It was very dependent on disk I/O.

    The performance analyzer does not work with the native db. I don't recall what (if any) performance tools were available for that db. Honestly, it has just been too long.

    How is performance for the rest of the system?
    There are no bugs - only undocumented features.
  • DenSter
    DenSter Member Posts: 8,307
    RAID 5 was a very common performance killer back then. I've worked hard to forget all about that time ;)
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • miknav
    miknav Member Posts: 13
    bbrown wrote: »
    How is performance for the rest of the system?
    It seems the server does not have much impact on the report and performance monitor shows it's pretty much idling. The client PC running report has huge reserves also (i7-13700F, 64GB RAM). The report uses much of caching - virtual tables, it loads all data into memory and then does all filtering there. I thought it might be a memory thoughput or something but there is no noticeable difference when I run it on client PC several generations older.

    DenSter wrote: »
    RAID 5 was a very common performance killer back then. I've worked hard to forget all about that time ;)
    The Server runs on 3 pairs of SSDs in RAID1. Yeah, I wish we could switch to SQL, but that would cost a lot- we have bunch of custom code that would need changes.
  • skiddoo
    skiddoo Member Posts: 19
    What happens when you are running the report on the Server. Makes that a difference?
  • miknav
    miknav Member Posts: 13
    skiddoo wrote: »
    What happens when you are running the report on the Server. Makes that a difference?

    Not much difference. I think I got it where's the bottleneck though- one of the prcessor cores shows 100% usage while running the report. So I need the highest single core performance cpu to make it as fast as possible.
  • bbrown
    bbrown Member Posts: 3,268
    miknav wrote: »
    skiddoo wrote: »
    What happens when you are running the report on the Server. Makes that a difference?

    Not much difference. I think I got it where's the bottleneck though- one of the prcessor cores shows 100% usage while running the report. So I need the highest single core performance cpu to make it as fast as possible.

    That old database did not support multiple cores. It was also limited to only 1 GB of memory. Performance was all about disk i/o.

    There are no bugs - only undocumented features.
  • Kowa
    Kowa Member Posts: 926
    On the server, have you tried opening the database directly from the client which is installed there?
    Kai Kowalewski
  • miknav
    miknav Member Posts: 13
    bbrown wrote: »
    That old database did not support multiple cores. It was also limited to only 1 GB of memory. Performance was all about disk i/o.
    Yeah, and since it's all about CPU and disk i/o is not an issue (we are doing all tasks on virtual tables anyway), we're pretty much dommed to find CPU with highest single core performance.. Thanks!

  • miknav
    miknav Member Posts: 13
    Kowa wrote: »
    On the server, have you tried opening the database directly from the client which is installed there?

    No difference..

Categories