Proper way to execute a large SQL query in vb.net

Question

I have a SQL query that I used my vb.net application to run it, but i have a problem when It exceeds to its command timeout property. I set it to 300, any tips or suggestions?

here is the snippet of my code in vb.net

  .ExecuteQuery("exec spCentrIX_PracticeSet '" & oCompany & "','" & .SecDatabaseName & "'", sDataSet)
  Application.DoEvents()
  .ExecuteQuery("exec spCentrIX_PracticeSet '" & oCompany & "','" & .SetDatabaseName & "'", sDataSet)
  Application.DoEvents()
  .ExecuteQuery("exec spCentrIX_PracticeSet '" & oCompany & "','" & .TranDatabaseName & "'", sDataSet)
  Application.DoEvents()

same script that run three times. this script gets all the data from the 3 databases which runs okie. however, when it is running...it exceeds the connection timeout of my code.

Maybe if you post your SQL, or some details about the structure of the info you're trying to retrieve we can help you optimize it. Maybe the query can be optimized, maybe you need to use a paging strategy, etc. But without more info we can't help much. — Mike Parkhill
– Mike Parkhill, Commented Oct 23, 2012 at 20:12

PatFromCanada · Accepted Answer · 2012-10-23 20:30:43Z

1

Setting the timeout to zero will never time out, this is not advised in a production environment but if you really really want to wait till it is finished it will do the trick. You would want to investigate ways to optimise the query if possible. Also run it asynchronously, maybe using backgroundworker.

http://www.codeproject.com/Articles/20627/BackgroundWorker-Threads-and-Supporting-Cancel

edited Oct 23, 2012 at 20:30

answered Oct 23, 2012 at 20:15

PatFromCanada

2,7981 gold badge30 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

illumi Over a year ago

a backgroundworker...sounds new to me...can you provide me some examples?

PatFromCanada Over a year ago

I added a link from a quick google search, I have not actually used it but Code Project is a good site. Be careful posting on SO before doing some investigation yourself, people tend to discourage that.

Andrew Morton Over a year ago

I am wondering if Tasks would be a better/simpler solution. msdn.microsoft.com/en-us/library/dd460717%28v=vs.100%29.aspx . Also, @dave, do you need .ExecuteQeury or .ExecuteNonQuery? (You may have shortened your code such that I can't tell.)

Collectives™ on Stack Overflow

Proper way to execute a large SQL query in vb.net

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related