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?

  • Checking my understanding of your question real quick, are you saying that there is no centralized DB server and the application is copying over raw files and running off a local DB every single time it's used? Either way at a quick glance i would lean towards network problem. Side note, to get Gigabit speeds every part of the network needs to be Gigabit capable: The switches, the phones, the PC's cards, etc.... otherwise you'll eventually hit a bottleneck somewhere and slow to the lowest speed component. – Duffy Feb 26 '16 at 21:15
  • sigh that's what tech support said...there are just foxpro files. It's a free table directory file-based database. – leeand00 Feb 26 '16 at 21:17
  • It is centralized in that they are all on the same server, but there isn't a server process – leeand00 Feb 26 '16 at 21:17
  • With that setup performance is going to entirely be based around network transfer speed and then the machine executing it's (or the VM/Workstation) capabilities. Depending on how universal the setup is you could get radically different results all over the place. Makes a lot of sense the best run is directly on the home server. This a general architecture problem not a database issue. – Duffy Feb 26 '16 at 21:21
  • Oh good I'm not crazy. What do you mean by "How Universal the setup is"? – leeand00 Feb 26 '16 at 21:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.