Increasing query time-outs with TableAdapters

  • Article

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 Namespace

When 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