LabVIEW

cancel
Showing results for 
Search instead for 
Did you mean: 

DB Toolset - Slow Query

Solved!
Go to solution

I'm using DB toolset to acquire data from the server and found it to be very slow. It takes almost 3 minutes to bring the data up using the toolset but there's almost no wait when I did the query in the MS SQL Server Management Studio. I'm not expecting the toolset to be as fast as SQL SMS but maybe a lil closer... Unless there's something wrong with my VI then it's a different story.

As I searched the forum this is seems to be an issue in the fast..

If there's any idea to speed this thing up please do advice..

 

Thank you very much..

 

DBTEST.JPG

______________
KowdTek
LabVIEW 2009

One Step At A Time, Maybe Two...
0 Kudos
Message 1 of 13
(4,763 Views)

How lrge is the table you are fetching (rows , columns)

Paul Falkenstein
Coleman Technologies Inc.
CLA, CPI, AIA-Vision
Labview 4.0- 2013, RT, Vision, FPGA
0 Kudos
Message 2 of 13
(4,754 Views)

Within our 6 months data it's about half a million rows x 12 columns. I think regardless of the date specifiedin the query, it will still scan the whole table that slows the process.

I've also tried other method (My Data Source, this came up after configuring the database.) it took around 30 seconds to bring up the data. After comparing the time with other 2 methods I'm thinking LabVIEW can do nearly as fast as the two.

 

Thanks for the reply..

______________
KowdTek
LabVIEW 2009

One Step At A Time, Maybe Two...
0 Kudos
Message 3 of 13
(4,748 Views)

I dont think the querry is the issue but the conversion to string table that is slow, you are building a massive string table dynamically using this fetch table (I dont have this in my db 2009 palette?).  You can try to fetch all and see if the speed is comparable.  You can also limit the search more (LIMIT 1000) and view the table chunks at a time. 

Paul Falkenstein
Coleman Technologies Inc.
CLA, CPI, AIA-Vision
Labview 4.0- 2013, RT, Vision, FPGA
0 Kudos
Message 4 of 13
(4,741 Views)

 


@KowdTek wrote:

Within our 6 months data it's about half a million rows x 12 columns. I think regardless of the date specifiedin the query, it will still scan the whole table that slows the process.

I've also tried other method (My Data Source, this came up after configuring the database.) it took around 30 seconds to bring up the data. After comparing the time with other 2 methods I'm thinking LabVIEW can do nearly as fast as the two.

 

Thanks for the reply..


 

Now why would you want to copy half a million rows over the network? What is it you really want to do with the query? (Also why are you looping the query?)

If you want to look through the data i'd recommend cursors and fetch data. Then the query will be faster and you transfer alot less data at a time.

Your query will probably work fine with a "top 1000" as mentioned to limit the data transferred. If you only want to count it a "select count(*)" will be alot faster.

 

So, what's the goal?

/Y

G# - Award winning reference based OOP for LV, for free! - Qestit VIPM GitHub

Qestit Systems
Certified-LabVIEW-Developer
0 Kudos
Message 5 of 13
(4,727 Views)
Solution
Accepted by KowdTek

"Why Is the Database Toolset Slow in Returning Table Data?"

http://digital.ni.com/public.nsf/allkb/862567530005F0A186256A33006B917A

 

(I suspect that the database-variant to data at the core of Fetch Table is the culprit.  Try the normal Fetch recordset that outputs an array of variants.)

-Barrett
CLD
Message 6 of 13
(4,707 Views)

falkpl,

I just tried the fetch all and it did return the data with the same time so that wouldn't be an option. I'll try to limit the result though I'm not sure if it will work because the table's date column is not in order..

 

Thanks!

______________
KowdTek
LabVIEW 2009

One Step At A Time, Maybe Two...
0 Kudos
Message 7 of 13
(4,687 Views)

 


KowdTek wrote:

 

Within our 6 months data it's about half a million rows x 12 columns. I think regardless of the date specifiedin the query, it will still scan the whole table that slows the process.

I've also tried other method (My Data Source, this came up after configuring the database.) it took around 30 seconds to bring up the data. After comparing the time with other 2 methods I'm thinking LabVIEW can do nearly as fast as the two.

 

Thanks for the reply..

 


 Yamaeda wrote:

 

Now why would you want to copy half a million rows over the network? What is it you really want to do with the query? (Also why are you looping the query?)

If you want to look through the data i'd recommend cursors and fetch data. Then the query will be faster and you transfer alot less data at a time.Your query will probably work fine with a "top 1000" as mentioned to limit the data transferred. If you only want to count it a "select count(*)" will be alot faster.

 So, what's the goal?

/Y

 


Correct me if I'm wrong but I'm not copying the whole half million, yes I'm scanning the contents and bringing up the selected data base on the query.

 

The goal is to display the production number of each work station based on thier job type.

 

Thanks!

 

______________
KowdTek
LabVIEW 2009

One Step At A Time, Maybe Two...
0 Kudos
Message 8 of 13
(4,686 Views)

If you are just interested in the number of records that satisfy your search parameters you should go with a 'count' querry instead of a fetch record sets.  This is fast and will only return a single number.  There is probably some overhead in the sql calls from labview.  I have always been able to work around it but there are probably some limitations.

 

Paul Falkenstein
Coleman Technologies Inc.
CLA, CPI, AIA-Vision
Labview 4.0- 2013, RT, Vision, FPGA
0 Kudos
Message 9 of 13
(4,671 Views)

Hello,

 

I would agree with blawson.  Try removing the while loop and using the native recordset data type to see if that reduces the execution time.  Also, confirm that the SQL query you are writing returns the desired results with MS SQL Server Management Studio.  If that query is correct, you should only be seeing results within the desired timeframe.

 

Regards,

 

National Instruments
Senior Systems Engineer
0 Kudos
Message 10 of 13
(4,654 Views)