Why gets my DB Query code so slow?

[Xenia1986]

Hi, 

I have a big problem when I try to retrieve data from my database. The code you can see in the attachment shows my "get record" lv code. It works fine, if I use this code only from time to time. However, if I use this code in my main application, which executes this code about once a second, the function slows down after a while. I did some time measuring during the execution of the function and the duration increases constantly. After 30 minutes the main application is not usable anymore. 

After reading the "Read all Data" example in the DB Toolset examples, I'm not sure if this solution actually works for me. I don't want to retrieve the whole table at once, just one selected row!

Is there anything I can do to avoid this problem?

Thanks in advance!!!

I use LabVIEW 8.5.1 with the DB Toolset.

[NickG88]

When you execute the query, how much data is gathered at once? If the database is growing as you are querying it might explain why it takes longer and longer to execute.

If you are only trying to gather the first set of data, try using a SELECT FIRST clause.

http://en.wikipedia.org/wiki/Select_%28SQL%29#FETCH_FIRST_clause

[Xenia1986]

Thanks for your idea, NickQ.

Everytime I call my "get record" function I want to retrieve only one row. My SELECT clause is always defined to extract only one at once. In the biggest table I have currently over 1 million entries, but I can't imagine that this is the main problem. I don't know the toolset exactly, but if I use a SELECT clause which retrieves only one row from a big table, LabVIEW has not to extract the SELECTED row, the SQL statement does this job?! I guess LabVIEW gets only the one row I selected, not more?! So, I think your idea is not really necessary for my problem, or maybe it is??

By the way, I use MS SQL Server 2005.

[PhillipBrooks]

What happens if BOF or EOF is true? Do you still close the recordset reference?

If not, you may be 'leaking' memory (unclosed references).

[Xenia1986]

@Philip: I checked the other case and the references will be closed like in the other case!

Just found another thing: It seems to be that the "Fetch All" Sub VI of the DB Toolkit could be the bottle neck After the main program executes for ~30min, this VI gets slower and slower. -> I found a while loop inside the "Fetch All" VI, which is designed badly; it builds up uninitialized variant arrays...the loop itself is not timed. Very slow when I have large rowsets, it iterates for all columns. (How does it come that NI doesn't practice, what their preach??).

After trying to improve this code, the execution time of my program improved a bit. However, the problem is still there...