Increasing query time-outs with TableAdapters
Tuning your SQL queries for the best performance can be part of life when you’re writing a database application. Sometimes life is hard, and you just can’t get things running as quickly as you’d like. With the default command time-out at 30 seconds, how do you use a TableAdapter to execute methods without timing out and failing? Turns out, it’s pretty easy. If you have a DataSet called MyDataSet.xsd, add code to the MyDataSet.vb partial class file:
Imports System.Data.SqlClient
Namespace MyDataSetTableAdapters
Partial Class CustomersTableAdapter
Public Sub SetCommandTimeOut(ByVal timeOut As Integer)
For Each command As SqlCommand In Me.CommandCollection
command.CommandTimeout = timeOut
Next
End Sub
End Class
End NamespaceWhen it comes time to call a long query, just call the SetCommandTimeOut method before the query:
Dim ds As New MyDataSet
Dim customersTA As New MyDataSetTableAdapters.CustomersTableAdapter
' Increase time-out to 60 seconds
customersTA.SetCommandTimeOut(60000)
' Do the slow query
customersTA.FillSlowQuery(ds.Customers)
This will change the time-out for all of the commands on the instance of the TableAdapter, so be sure to set it back to a lower value once you’re done with the long query
Hopefully you'll find my first post useful!
Ryan Cavanaugh
