machine working so hard importing data from Mysql database

[larson09]

Hello!

I’m using the labview database connectivity toolset 1.0.1 to import data from a MySQL database to Labview.

I have to import several times along my application 10 tables…..each table has almost 400rows and 4columns. I use to import it DB Tools Select Data block (I wire the database name, password, and the name of the columns to import). But my machine (Pentium 4) works so hard, works at 100%. I want to know if there is another way using this toolset or another thing to import this data faster and with a machine working less hard.

I don’t know if there is a newer version of database toolkit of labview.

Can you help me?

P.D: i've atthached an example of how i import the data, i think is not the best way to import but i don't know another way

Thank you very much

Larson using Labview 7.0 on windows XP

[Underflow]

Hey Larson,

Check the tips at:
http://digital.ni.com/public.nsf/websearch/862567530005f0a186256a33006b917a

. And thanks to CD for getting it rewritten! The old version was pretty bad.

Joe Z.

[larson09]

Do you know if this problem of getting data is solved in a newer version than the version i have (labview database connectivity toolset 1.0.1).

Have you done the things that are written in the report.......is faster and better to import data in this way??

Thanks!!

Larson

[Underflow]

I am not sure if there is a newer version than 1.0.1.  The current versions page doesn't seem to list the DBCT.

I have used some of the tips from that page, and they do speed up query time.

As a summary from the document:

1.  Use an OLE DB provider.  I used MyODBC, but it's pretty easy to imagine this does help.

2.  Read less at a time.  Always good advice.  You could check into using cursors, or check the example code for ideas.  Last time I used MySQL (a couple of years ago) it didn't support server side cursors, and the DBCT didn't support client-side cursors, but that may have changed.

3.  ***Use a different VI to return everything as a string.***  It helped me tremendously, but I had to handle casting/converting string types.  If I were to make a (mostly uneducated) guess, there is an ActiveX step in converting to the DB Variant type that slows down type based queries.

4.  Optimize your schema.  Well, yes.

5.  Use queries optimized for your database.  I have not used any of the more exotic (and, not coincidentally, expensive) databases, so I can't comment on this one.

Good luck,

Joe Z.

[AnalogKid2DigitalMan]

FYI-

Current version of the DB Connectivity Toolkit is 1.0.1

[Brian_Beal]

You may want to break your program up into 2 steps to see where the problem is.  I suspect that it is in the data type conversion, and not at the query itself.  Do the query alone to get the 2d array of data.  Then do the data conversion on that array.

[Chaos]

Larson,

While I dont have the DB tool for testing for fastest method.  I checked the code picture you provided and think this would not only be faster but take less room.  Since you are doing the exact same processing on each element of the array just autoindex using a for loop.

[Crystal]

Hi, Larson

I just wanted to reiterate some of the other responses you got.  From that support document, I think #3 is the one that most applies to your situation.  Use the Select subVI from the Read All Data examples and not what's in the palette.  To convert the resulting strings to numbers use the Fract/Exp String To Number function.  The attached screen capture shows what I mean.

Crystal

PS:  Yes, the current version of the toolkit is still 1.0.1.  However, if you have feedback for what you'd like to see in an update, please let me know. 

[larson09]

Hi Crystal!

I don't know what this means:

 Use the Select subVI from the Read All Data examples and not what's in the palette.

I have to use the SElect data block from read all data example???

This block is the same of the databse toolset paletter, isn't it?

Thanks

Larson

[Crystal]

No, the DB Tools Select Data VI from the palette is not the same as the DB Tools Select All Data VI used in the Read All Data example.  Their names, icons, and internal code are all very different.  That's why I asked you to use the DB Tools Select All Data VI. 

There were a few technical reasons (the inability to read NULLs, etc) that kept us from putting it in the palette when this toolkit was released almost 5 years ago, but those reasons have all been fixed and are no longer a problem.  Feel free to use this VI anywhere you need it.