In a department where they were complaining that a report was being run slowly, I jumped on one of the empty machines there and tried it out. It took a good 6 and 15 seconds to run.
Run from a client on the VM on the Hypervisor that contains the files, it runs almost instantaneously. And run from a client on a separate VM on the same Hypervisor, it takes about 19 seconds. (Of course the VM on the Hypervisor is probably a good bit faster than the workstations in question).
I asked tech support for the app in question to confirm, that, in order for the report to be run, the files for every table (and it's index files) involved needed to be copied over to the local machine first by the client software (since there is not a server-side process for it); they confirmed it.
I also asked which files/tables were involved in the query, and I was told that about six tables were involved; do to the nature of the worst case query being one that spans before and after final quarter close (last years archived check lines are involved as well as this years).
The tables in question were the following: employees, employerconfig, paychecklinehistory, paychecklinesthisyear, payrollCodes, paygroups
I summed up the total sizes of these six tables, and additionally the .cdx index files for each.
I also noted that all computers in HR are: 1. Attached to the network via their IP phones...which contain a switch which is 100 Mbps 2. The phone is then attached to a PoE device (to power the said IP phone) and that device, despite having a Gigbit ethernet port that connects it to the rest of the network (the network is Gigbit); is limited by 24 ports that are 100 Mbs.
So this lead me to believe that summing the file sizes and taking into account the speed of the file transfer would result in how long it might take to copy the files over to the client.
So to look at the full picture here the time involved would be the following: 1. The amount of time it takes to copy the tables and their indexes to the client. 2. The amount of time it takes to join/filter the tables (in whatever order that takes place) 3. The amount of time it takes to display / generate the report with the results of that query
For #1: Using an equation and a website, I figured out that it should take about 3 minutes and 15 seconds to run given the size of the table and index files and the 100 Mbs lan speed.
For #2: It will depend on the workstation specs which vary, so I can't include it.
For #3: This too will depend on the workstation's specs which vary, so I can't include it.
Now my boss says he thinks it's something that can be repaired with the database, since there's a Gigabit Ethernet connection, but from what I can tell this isn't the case, and short of purging the tables, or hooking the HR workstations directly into the Gigabit Ethernet switch instead of their phones, I don't think anything else with the database can be done to speed this up unless we give them access to a VM on the same machine as the "server".
Am I correct in thinking that this is an issue with the dual bottlenecks in the network, and not the database itself?
