LabVIEW

cancel
Showing results for 
Search instead for 
Did you mean: 

SQL query time out error

Solved!
Go to solution

We have a data table that is growing every day, roughly 40M records as of today.

 

it causes some of queries to run slower and slower. 

 

One of the query is as follows

     select distinct ScanID

     from tblClassifierResults  

     where ClassifierID LIKE 'CNTR%04052021%'

 

'ClassifierID' is the primary key of the table, so there is an automatic index for it. 

 

When I ran the same query in 'Azure Data Studio', it took long time to run as well, but it does not time out. I can get data after the query is complete. 

 

But when I ran it in LabView, I got the following error. 

 

NI_Database_API.lvlib:Conn Execute.vi->Exec SQL Statement.vi<ERR>ADO Error: 0x80040E31


Exception occured in Microsoft OLE DB Provider for SQL Server: Query timeout expired in NI_Database_API.lvlib:Conn Execute.vi->Exec SQL Statement.vi

 

sql query time out error.png

 

I don't feel that this is a LabView issue. All LV does is to send the query and wait for any response. 

 

On the other hand, the default time out in the Azure is like 600 seconds, long enough to complete the query. 

 

My question is why LabView gets a time-out error here? Any solution to let LV wait a bit longer until the query is complete?

0 Kudos
Message 1 of 18
(3,164 Views)

Default connection timeout is 15 seconds (in the Open Connection VI), and you don't have anything wired there so that's the timeout you're using currently. Try wiring a higher number to that timeout terminal.

Redhawk
Test Engineer at Moog Inc.

Saying "Thanks that fixed it" or "Thanks that answers my question" and not giving a Kudo or Marked Solution, is like telling your waiter they did a great job and not leaving a tip. Please, tip your waiters.

0 Kudos
Message 2 of 18
(3,141 Views)
Solution
Accepted by topic author MengHuiHanTang

The short answer to your question is, specify a longer command timeout, which is a property at the Connection level.  There is a polymorphic VI DB Tools Set Properties in the Connectivity>Database>Utility subpalette which, when wired at the Connection refnum level, allows you to set a timeout for all commands (in integer seconds).

 

The longer answer is, why does your query take so long?  SQL Server can be amazingly fast in lookups, but as datasets grow larger, if your query times grow proportionately, you're doing something wrong at the database level.  Query times should ideally grow at more of a log(N) rate.

 

I would most definitely test further in the Data Studio tool to see how you might speed the query up.

 

Also, looks suspiciously like your "ClassifierID" field is being searched for text that represents a date.  Do you have control over your table definition?  If so, and date searching is a common selection method, why aren't you storing (and searching) a date (or datetime, or datetime2, or datetimeoffset, etc) value?

 

Dave

David Boyd
Sr. Test Engineer
Abbott Labs
(lapsed) Certified LabVIEW Developer
Message 3 of 18
(3,133 Views)

In reply to RedHawk:

 

I think that timeout (for Open Connection) is only for the open action itself.  I'm unsure it carries over automatically to all subsequent transactions (I could be wrong).  I think the set properties method I described is the only way to be sure.

 

Best regards,

Dave

David Boyd
Sr. Test Engineer
Abbott Labs
(lapsed) Certified LabVIEW Developer
0 Kudos
Message 4 of 18
(3,130 Views)

Check the UDL file for timeout parameters. 

aputman
------------------
Heads up! NI has moved LabVIEW to a mandatory SaaS subscription policy, along with a big price increase. Make your voice heard.
0 Kudos
Message 5 of 18
(3,123 Views)

aputman:

 

Again, I believe that value in the UDL (if specified) is a connection-setup timeout only.  The OP needs (for the short term, at least) an extension to the command timeout, which is applied at the level of an established connection.

 

Dave

David Boyd
Sr. Test Engineer
Abbott Labs
(lapsed) Certified LabVIEW Developer
0 Kudos
Message 6 of 18
(3,115 Views)

@DavidBoyd wrote:

In reply to RedHawk:

 

I think that timeout (for Open Connection) is only for the open action itself.  I'm unsure it carries over automatically to all subsequent transactions (I could be wrong).  I think the set properties method I described is the only way to be sure.

 

Best regards,

Dave


Now that I'm looking at the detailed help for that terminal, it seems like you're right. Also not sure if it carries over to all transactions though. Never had that long of a query time myself.

Redhawk
Test Engineer at Moog Inc.

Saying "Thanks that fixed it" or "Thanks that answers my question" and not giving a Kudo or Marked Solution, is like telling your waiter they did a great job and not leaving a tip. Please, tip your waiters.

0 Kudos
Message 7 of 18
(3,113 Views)

David, 

 

You are correct. The timeout in the connection VI does not apply to the subsequent transaction time. 

 

I did your way and it worked. 

 

It is an old database. So it never anticipates the data will grow to the current scale. Like you said, it will only get worse. We will have to stop using that query. 

 

Thanks for your time and help. 

0 Kudos
Message 8 of 18
(3,109 Views)

RedHawk, 

 

Thanks for your time and help. Appreciate it. 

0 Kudos
Message 9 of 18
(3,109 Views)

This seems to be a case study in to why one should use proper timestamps, which for text is year, month, day, hour, minute, second.  If you would be selecting LIKE 'CNTR20210504%' your search would be very fast.

0 Kudos
Message 10 of 18
(3,090 Views)