The reported 0.3–3 s variation sits between sequence states 1 and 2, while the interval from states 2 to 3 stays below 1 s; measure whether the caller is waiting to enter the database subVI before changing its reentrancy. The reported query time is about 4 s in some runs and 0.3 s in others, but direct MySQL execution is reportedly fast. That contrast points first to the LabVIEW call path, connection use, or network—not proof that the database query itself is slow.
Measure the state 1-to-2 interval at the subVI boundary
Separate time waiting for a VI instance from time spent executing SQL and fetching results. A timestamp around a flat-sequence frame can include caller scheduling or waiting to enter a non-reentrant subVI; it does not automatically measure only server execution. The existing measurement locates the variable interval before or within the database subVI, but does not distinguish those components.
- Add timestamps immediately before the database subVI call and immediately after it returns. Preserve the existing state markers so you can compare the intervals.
- Use a data dependency to make each timestamp execute at the intended boundary. In LabVIEW, sequence order alone is not a substitute for an explicit dataflow dependency where the timing VI must control entry or exit.
- Record caller identity, query text or query class, and whether another database call is active at the same time. Repeat under normal application load, not only from an isolated test.
- Compare a slow run with a fast run. If the pre-call-to-entry wait expands, investigate VI-instance availability or scheduling. If time inside the call expands, inspect database execution, connection use, result transfer, and network behavior.
Use the LabVIEW VI Profiler during application operation to identify callers consuming execution time. State 2-to-3 remaining under one second makes that interval a lower-priority target until a new measurement shows otherwise.
Check whether callers serialize at a non-reentrant query VI
About 100 sources call the same VI. That call count does not mean 100 requests execute simultaneously: if the shared query subVI is non-reentrant, concurrent callers can wait for the active instance to finish. Conversely, a reentrant VI can permit multiple instances, but it does not make a database connection, shared reference, or table operation safe for concurrent use by itself.
| Observed result | Likely area to investigate | Next check |
|---|---|---|
| Delay grows before the subVI starts; another call is active | Caller queueing or non-reentrant VI instance contention | Profile callers and test controlled serialization versus appropriate reentrant instances |
| SubVI starts promptly, but the call duration grows under concurrent load | Connection contention, database work, result transfer, or network latency | Measure query execution and fetch separately; inspect connection handling and load |
| One caller is consistently slow even without overlap | That query variation, parameters, result size, or caller-specific work | Compare exact SQL and returned rows against a fast run |
| All callers slow down at the same time | Shared database, host, or network resource | Compare client-side timing with server-side query timing and network conditions |
The evidence describes the database library as reentrant and asks whether the Execute Query VI should use preallocated memory; it does not establish that the called query subVI itself is reentrant. Check that VI’s execution properties directly. Preallocated memory or clone allocation controls VI instance availability and memory allocation; it does not remove contention on a shared connection or guarantee parallel database access.
Compare exact requests before changing the database schema
Check whether the callers issue exactly the same SQL or merely similar requests. If many callers fetch overlapping data, combine the required retrieval into fewer calls and filter the returned data in LabVIEW where that reduces repeated database work. Do not batch blindly: confirm that the combined result has acceptable size and that filtering preserves the required records.
The database is reported as indexed, with 169 columns and about 700,000 rows. Those facts alone do not show that every query uses an index efficiently, nor do they prove a schema change is required. Compare the exact SQL, parameters, result row count, and fetch time between a fast and a slow LabVIEW call. A direct MySQL test is useful only when it runs the same query with comparable parameters and result handling; a fast interactive execution does not reproduce the LabVIEW connection path or concurrent application load.
Do not split a wide table solely because it has 169 columns. First determine which columns the application actually needs and whether the query transfers unnecessary data. If the query selects every column, selecting only required columns may reduce transferred data and client-side handling. Schema normalization is a separate design decision that requires understanding relationships and application access patterns, not just column count.
Test reentrancy without multiplying shared database state
After timing and profiling, test one controlled change at a time. If callers queue at a non-reentrant VI and each invocation can safely own independent execution state and database access, test the query VI as reentrant in a controlled environment. Watch both latency and correctness under concurrent reads and writes. If it uses shared connection state or resources that must be serialized, retain a single database-access owner instead of allowing uncontrolled parallel entry.
A serialized database worker is a practical production pattern when callers contend: callers submit requests to one worker, which performs database operations in an explicit order and returns each response to its requester. Buffer write commands when the application permits delayed writes, then drain them in order and handle failures visibly. This avoids concurrent callers competing over shared state, but it does not fix an inherently slow SQL statement or an overloaded server.
Do not switch the entire database library to preallocated memory based only on a latency symptom. First identify the VI whose instance allocation is relevant, confirm its current reentrancy setting, and establish whether database handles are per-call or shared. A change that creates additional VI instances can increase concurrent demand without improving throughput.
Choose a temporary restore before the permanent correction
For a temporary restore, route database requests through one controlled access path and limit overlapping calls. If requests are identical or substantially overlapping, reduce duplicate retrieval by issuing fewer calls and filtering locally when the result volume is manageable. Keep the existing index and schema unchanged until measurements identify a database-side bottleneck.
For a permanent correction, address the measured cause: remove unnecessary repeated queries, correct caller behavior, provide safe independent instances where justified, or preserve deliberate serialization around shared connection state. If time is spent inside SQL or result fetching, optimize the exact request and its returned data, then reassess under realistic concurrency. If client timings rise while server execution remains fast, investigate the LabVIEW-to-MySQL connection and network path.
Apply the measured change and verify under application load
- Capture baseline timings at the call boundary, inside query execution and fetch where measurable, and between sequence states 1 and 2. Record overlap, caller, exact query, parameters, and result size.
- Use the profiler and controlled tests to determine whether the delay is caller waiting, query execution, or result transfer. Change only the implicated factor: caller scheduling, safe VI reentrancy, serialization, or query/result scope.
- Repeat the same workload with the same callers and request mix. Compare the slowest calls as well as typical calls, and confirm that returned data and write ordering remain correct.
- Keep the temporary serialized path if the reentrant test causes errors, unsafe shared access, or no measurable improvement. Document the chosen execution model so future callers do not bypass it.
Consider the correction verified when repeated application-load tests show the state 1-to-2 variation has been explained and reduced or controlled, query results remain correct, and concurrent operations do not corrupt or reorder data. Compare timings from the same measurement boundaries; do not treat one fast direct MySQL execution as verification of the complete LabVIEW path.
Frequently asked questions
What happens if I set the query VI to reentrant?
LabVIEW can allow simultaneous VI instances, which may remove waiting at a non-reentrant subVI. Test it only after checking whether each invocation has independent execution state and safe database access; shared connection state can still contend or fail.
What happens if the MySQL query runs instantly outside LabVIEW?
That narrows the comparison but does not include LabVIEW caller scheduling, connection handling, result fetching, or application concurrency. Run the same SQL with the same parameters and compare server execution time with the measured LabVIEW call duration.
What happens if all 100 callers request similar data?
Check whether they use identical SQL and whether their results overlap. If so, consolidate retrieval and filter in LabVIEW when the combined result size is manageable; otherwise preserve separate requests and control their concurrency.
What happens if one query still takes about 4 seconds?
Measure the call’s wait-to-enter, SQL execution, and fetch intervals, then compare its exact query, parameters, and returned row count with a fast call. Stop changing VI allocation or schema until those measurements identify the slow stage.
Stop the reentrancy or schema experiment if it produces incorrect results, unsafe concurrent access, or no repeatable improvement. Escalate to the official National Instruments or MySQL support channel with the timing breakdown, profiler capture, exact query and parameters, concurrency observations, and connection details; back up the database before any migration.