Optimize DB Tools Set Parameter Value

[GregFreeman]

I am making a call to a stored procedure to load blob data. This stored procedure takes about 20 ms to execute. Setting the blob data parameter takes almost the same amount of time, and the two seem to scale fairly linearly based on data size. I have benchmarked to find where the time is being lost and it is on the call to the property node to set the parameter value. 20 ms may not seem like a lot, but we have a requirement to recover from a network fault and this effectively doubles the time it will take to get all that data into the database in this situation. Downstream processes can't execute until this data is calculated on and loaded through this procedure.

Is there any way to preinitialize that parameter's value to allocate some sort of buffer, or is the overhead from the property node call itself? I am a bit concerned this may just be the cost we eat due to the parameter value type being a variant. I have tried initializing a value when creating the parameters, to no avail.

Edit: I should add, this isn't just a first call overhead either. I have called this in a loop and it is consistently ~20ms to execute

[Ben]

Wild idea...

Setting the VI to run in the UI thread to prevent thread swapping as the delay?

Ben

[GregFreeman]

---

@Ben wrote:

Wild idea...

Setting the VI to run in the UI thread to prevent thread swapping as the delay?

Ben

---

Unfortunately this slowed it down. Not a bad thing to try though. I may just be SOL.

[aputman]

Why do you pass the data into the parameter in two locations?  I don't think you need the set parameter value since you already initialized the parameter with the same value.  But don't quote me on that.....unless it is correct. 

[GregFreeman]

---

@aputman wrote:

Why do you pass the data into the parameter in two locations?  I don't think you need the set parameter value since you already initialized the parameter with the same value.  But don't quote me on that.....unless it is correct. 

---

That's true except we will be calling this throughout the lifetime of the program with changing blob values. This is just a scaled down version of the code that I am allowed to post. I was hoping that initial call to  create parameterized query (which we will call only once at startup) would allocate some memory that could be reused, possibly making things more efficient. 

it will be more like this:

 

[aputman]

How often are you making calls to this stored proc?  I didn't find it feasible to open a connection to the dB at the start of the program and deal with maintaining that connection.  So in my case, I open/write/close but I am writing every 30 seconds or so.