Performance with MySQL and Database connectivity toolbox

[mattias@hv]

Hi!

I'm having quite some problems with the performance of MySQL and Database connectivity toolbox. However, I'm very happy with the ease of using database connectivity toolbox. The background is:

I have 61 variables (ints and floats) which I would like to save in the MySQL-database. This is no problem, however, the loop time increases from 8ms to 50ms when using the database. I have concluded that it has to do with the DB Tools Insert Data.vi and I think that I have some kind of performance issue with this VI. The CPU never reach more the 15% of its maximum performance. I use a default setup and connect through ODBC.

My questions are:

1. I would like to save 61 variables each 8-10ms, is this impossible using this solution?

2. Is there any way of increasing the performance of the DB Tools Insert Data.vi or use any other VI?

3. Is there any way of adjusting the MySQL setup to achieve better performance?

Thank you very much for your time.

Regards,

Mattias

[mattias@hv]

Perhaps I should add that I use LabVIEW 8.2.1, Database Connectivity Toolbox, WIndows XP and MySQL 5.

Thanks in advance for any advice.

/Mattias

[Brian_Beal]

Is your database on a different computer?  Does your loop execute 61 times?  In general, database operations will be slow compared to binary or text file operations.

[sivamgr]

You can speed up the database write by using bulk insert statements. This can be done in 2 ways.

Method 1:
=======
1) Keep 2 loops. One loop for datalogging into a text file. Log all your values in a comma/delimiter seperated format
2) In the other loop, Bulk insert the CSV file into the database using Bulk insert from file command. This loop can run at a very slow rate.



Method 2:
=======
1) Keep 2 Loops. one loop for your control/dataacquistion. In that loop, enqueue the required data into a queue.
2) In the Other Loop, Dequeue all the elements in the queue and form a Insert Statement like below.
insert into myTable values (1,'Y',36.00),(2,'N',57.00),(row3),(row4),....(row n)
and execute this statement. This loop can run at a very slow rate.

Note: Method 2 is supported by MySQL but not supported by SQL Express.

In my system, 500 individual Insert Statement take 33 Second to execute but, A single Insert statement with 500 rows takes less than 300 milliSec to execute.

[mattias@hv]

First of all, thank you very much for your time. All of you have been really good support to me.

>> Is your database on a different computer?  Does your loop execute 61 times? 

Database is on the same computer as the MySQL server.

The loop saves 61 values at once to the database, in one SQL-statement.

I have now added the front panel and block diagram for my test-VI. I have implemented the queue system and separate loops for producer and consumer. However, since the queue is building up faster then the consumer loop consumes values, the queue is building up quite fast and the disc starts working.

The test database table that I add data to is created by a simple:
create table test(aa int, bb char(15));

...I'm sure that this can be improved in some way.

I always open and close the connection to the database "outside the loop". However, it still takes some 40-50 ms to save the data to the database table - so, unfortunatly no progress to far. I currently just want to save the data.

Any more advise will be gratefully accepted.
Regards,
Mattias

Message Edited by mattias@hv on 10-23-2007 07:50 AM

[mattias@hv]

I have now also added a procedure to my database called AddData. I have managed to decrease my write time to about 33 ms by using this procedure instead of having a "insert into... etc" .

I have also added the possibility to save data to a simple text file. I'm having no problems at all when using this method, the write time is as low as 1ms or less. But when saving to the database the disc activity increases very much compared to when only writing to the text file. Do you know why the disc activity increases when using the database instead of a simple text file? I have measure my drive by using HD Tune and I see no problems with the disc performance.

Thanks in advance for any advice!

Regards,

Mattias