-1

I am looking for advice on how to reduce unpredictable horrible latency/response times for API calls from VBA. I did some statistical analysis of Excel VBA API calls to QueryPerformanceCounter and GetSystemTimeAsPrecisionFileTime.

On my machine (8 core, 5.2Ghz Max frequency, W10, Office 2019) both of these have 100nanosecond single tick resolution, they both need at a minimum of 6 ticks elapsed time to get response back, a mode of 7 ticks, an average of 8+ ticks, which I can live with.

But there are serious outliers in the distribution: 0.2% of the time they need at least 100 ticks (10 microseconds), and on very rare occasion as much as 5 milliseconds to get a response back to VBA. If I unplug the power supply from this laptop, the delays increase of course. They skyrocket to >11 ticks average, and ~0.2% of the time > 20 microseconds. I surmise this is some sort of queue time issue but I have failed to find any discussion on this issue.

Is there a way to improve priority for the API calls? Maybe something crazy like assigning two or three cores exclusively to Excel and the API, everything else to the other 5-6 cores?

Excel/VBA only uses max of ~30% CPU time according to task manager, so probably no hit on speed of execution for the code.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
photonblaster
  • 11
  • 1
  • 4

2 Answers2

0

VBA does not support multi-threading it can only use one core of your computer. So if you need multi-threading switch to a real programming language eg. Python (there exist libraries to handle Excel data with Python). Or use one of the alternatives mentioned here: Multi-threading in VBA

In VBA Excel will always wait for one command to finish until it can start the next one (single threading).

By the way the time between the VBA command to the API and the API returning the result cannot be influenced by VBA (or any other solution). This is the API's calculation time to give the result. So this is not Excel's fault that it takes long, it is the API that needs that time to calculate the result (which depends on the values you give that API how long this calculation time actually is).

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • PEH, have you run timing tests in Python to show Python is faster than VBA re getting info back from QPC? Or give me a link to this? You have to really do statistical analysis, you cannot just look at the average, you have to look at the distribution and outliers. This answer does not address the the fundamental issue...it cannot take 700 nanoseconds one time and 5000000 nanoseconds another time to get a number back from QPC because the calculation times are different. It is not about multithreading...it is about asking for a date/time info and getting one that is horribly out of date. – photonblaster Sep 09 '21 at 18:18
  • It can take 700 nanoseconds one time and 5000000 nanoseconds depending on many issues. Fact is the time between sending the request to the API and getting the answer back in VBA is not the fault of VBA it is in the hands of the API. That can take different times depending on where the API does get the result from? Database, Internet, other computer/server, calculating only based on input data? All these factors can be a bottleneck to the result. Eg if the API request a database that database can be the bottleneck. No way to tell you where the problem is without knowing what the API does. – Pᴇʜ Sep 10 '21 at 06:16
0

I have continued to research my question. I think the bottom line is that you are at the total mercy of windows unpredictable prioritization of events. You cannot force prioritization, even if you set affinity to one CPU core and priority to high. I have tried both and indeed I see these timing outliers. See for example hints of this in this thread about getting frequency for timing calcs.

So I have to be aware that any one result has a 2+ percent probability that the timing error will be 2+ microseconds, at least on my PC running in turbo mode.

photonblaster
  • 11
  • 1
  • 4