Delay while reading from MySQL

[mr_akhilarya]

Hi everyone,

Need some help,

I am using MySQL for storing data from my LabView application.

Sequence for program : 

1. Search if Barcode already exist in database.

    QUERY used: Select * from All_data where BARCODE = "123"

2. If yes then update its values other wise do nothing.

    QUERY used: Update Switch_Assembly Set ST28_RESULT='OK', PRODUCTION_END_TIME='13:13:54',PRODUCTION_END_DATE='09-07-24' WHERE SERIAL_NUMBER="123"

Sometimes, These queries use 88ms minimum time and 800ms time at max. Need to decrease this max time to min and need to know reason why does this. Now my database VI is used by multiple VI and called at same time also. Please take that in consideration.

[aputman]

Unfortunately, I can only open 2018 files.

Looking at your queries, my first suggestion would be to consider a different datatype for the serial / barcode field.  It looks like you are using text and doing a full text search can be time consuming, depending on the number of records in your database and the size of the text field.  If you can change this field to a numeric and make it a unique column with an index, queries should be near instant.  If you need to leave it as text, still consider creating an index for that column. 

[JÞB]

Without even looking at your code my magic 8-Ball started spinning 

I just bet there are several instances of "Trim String.vi" hanging all over your code.  

Way back, I wrote a Trim Anything VIM that outperformed Trim String.vi by an order of magnitude.   It also inlines so it is reentrant and preallocates memory unlike Trim String.vi so it doesn't block.

Kudos for the Google search that finds that link!

[aputman]

MMMMM....kudos.

https://forums.ni.com/t5/LabVIEW/Trim-Whitespace-vi-is-not-re-entrant-Why/td-p/3646261

[JÞB]

---

@aputman wrote:

MMMMM....kudos.

https://forums.ni.com/t5/LabVIEW/Trim-Whitespace-vi-is-not-re-entrant-Why/td-p/3646261

---

Duly granted!

Later in that thread I show the @50x speedup over The ogtk version and <80x speedup over the built-in vi. Even in sequential call cases.