Link to home
Start Free TrialLog in
Avatar of CipherIS
CipherISFlag for United States of America

asked on

VB.NET 2008 - SQL Timeout

I have an application that was developed with VB.NET using ADO.net to connect to SQL Server.  Often a user encounters a timeout error.  Is there anyway to reduce it or eliminate it?
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

You need to be more specific.
When the timeout error occurs? During the connection or during a transaction?
If the latest, is always at the same transaction or it's happening randomly?
SOLUTION
Avatar of AndyAinscow
AndyAinscow
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of CipherIS

ASKER

The application is using an ADO.NET connection.  The issues seemed to occur when getting customer information for the application.  Below is the code.

The problem was occurring consistently yesterday.  It would always fail at this call.  It is working today.

I don't know if it was occurring during a connection or transaction.

SELECT DISTINCT 
C.CustomerId, C.CustomerId as id_customer, C.Name as Name, C.Custom1 as Custom1, 
ISNULL(STUFF((SELECT '<CUSTOMER_Contact> 	<id_contact name="'+cast(id_contact as varchar(5))+'"/> 	<FullName name="'+FullName + 
'"/> 	<job_title name="'+job_title+'"/> 	<ck_buyer name="'+cast(ck_buyer as varchar(1))+'"/> 	<ck_engineer name="'+cast(ck_engineer as varchar(1))+
'"/> 	<email name="'+email+'"/> 	<phone name="'+phone+'"/> 	<id_shippingAdd name="'+cast(ISNULL(id_shippingAdd, 0) as varchar(5))+
'"/> 	<id_invoiceAdd name="'+cast(ISNULL(id_invoiceAdd, 0) as varchar(5))+'"/> </CUSTOMER_Contact>' 
FROM CUSTOMER_Contacts CC2 
WHERE CC2.id_customer = C.CustomerId AND CC2.isActive = 1 
order by CC2.FullName for xml path('a'), type).value('.','nvarchar(max)'),1,0,''),'') 
contacts, 
ISNULL(STUFF((SELECT '<CUSTOMER_Address> 	<id_address name="'+cast(id_address as varchar(5))+'"/> 	<Name name="'+Name+
'"/> 	<CompanyName name="'+companyName+'"/> 	<ck_shippingAdd name="'+cast(ck_shippingAdd as varchar(1))+
'"/> 	<ck_invoiceAdd name="'+cast(ck_invoiceAdd as varchar(1))+'"/> 	<attn name="'+attn+'"/> 	<streetAddress name="'+streetAddress+
'"/> 	<extraNote name="'+extraNote+'"/> 	<state name="'+state+'"/> 	<zipCode name="'+zipCode+'"/> 	<city name="'+city+
'"/> 	<country name="'+CO.en+'"/> 	<telephone name="'+telephone+'"/>    <fullAddress name="'+
Replace( Replace( Replace( Replace(  Replace(  Replace(AF.format, '<CompanyName>', companyName),  '<StreetAddress>', streetAddress+
CASE WHEN extraNote <> '' THEN Char(10)+extraNote ELSE '' END), '<State>', state), '<ZipCode>', zipCode), '<City>', city), '<Country>', CO.en)+'"/> </CUSTOMER_Address>' 
FROM CUSTOMER_Address CA2 
INNER JOIN BASE_Country CO ON CO.rowid = CA2.id_country 
INNER JOIN SYS_AddressFormat AF ON AF.id_addressFormat = CO.id_format 
WHERE CA2.id_customer = C.CustomerId AND CA2.iSActive = 1 
order by CA2.Name for xml path('a'), type).value('.','nvarchar(max)'),1,0,''),'')  addresses 
FROM BASE_Customer C 
WHERE C.isActive = 1 
AND [C].[distributor] = 0 
ORDER BY C.Name

Open in new window


Below is the error being returned.


System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.SetMetaData(_SqlMetaDataSet metaData, Boolean moreInfo) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader() at Application.C_Connection.DisplayResult(String SQLString, String[] champ) at Application.M_SalesOrder.LoadCustomers()

Seems it is occurring when connecting.

Below is the ADO Code
Imports System.Data.SqlClient
Public Class C_Connection

    ' SQL Objects
    Dim SQLStr As String
    Private ConnString As String

    Private ObjetConnection As SqlConnection
    Private ObjetCommand As SqlCommand
    Private ObjetDataAdaptater As SqlDataAdapter
    Private ObjetDataSet As New DataSet()
    Private strSql As String
    Private ObjetDataTable As DataTable
    Private ObjetDataRow As DataRow
    Private strConn As String
    Private ObjetCommandBuilder As SqlCommandBuilder

    ''' <summary>
    ''' SQL queries for insert, update, delete
    ''' </summary>
    ''' <param name="SQLdr">String query</param>
    ''' <remarks></remarks>
    Public Sub QuerySQL(ByVal SQLdr As String)

        ObjetDataSet.Clear()

        strSql = "SELECT * FROM PRODUCT"
        ObjetConnection = New SqlConnection()
        ObjetConnection.ConnectionString = Singleton.strConnection

        ObjetCommand = New SqlCommand(strSql)
        ObjetDataAdaptater = New SqlDataAdapter(ObjetCommand)

        ObjetConnection.Open()

        ObjetDataAdaptater.InsertCommand = New SqlCommand(SQLdr, ObjetConnection)

        ObjetDataAdaptater.InsertCommand.ExecuteNonQuery()
        ObjetConnection.Close()

    End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I didn't write the SQL.  I would have to play with it.

From the feedback I received, when the server was restarted everything was working as expected.

Another problem is that the SQL returns over 800 records to .NET.  Then the application uses a for loop to add data to a combobox.  OUCH!
From the feedback I received, when the server was restarted everything was working as expected.
Ofc. After a restart, resources are not being held by processes anymore. Mind that is a very bad workaround since during the restart the database won't be available and the application won't work.

Another problem is that the SQL returns over 800 records to .NET.  Then the application uses a for loop to add data to a combobox.  OUCH!
800 rows is nothing special unless you have a very very bad network.
>>Another problem is that the SQL returns over 800 records to .NET.  Then the application uses a for loop to add data to a combobox.  OUCH!

From performance that is no problem.  From the point of view of the user selecting something - hmmmmm.

ps.  With the data, make certain columns being sorted/filtered are indexed if possible.  That brings massive performance improvements.
How can I find out what resource is holding the process?
How can I find out what resource is holding the process?
Check the CPU and Memory usage. If they are near 100% usage you should know that something bad is happening and then you should start to troubleshoot inside the SQL Server instance but here you need some SQL knowledge to know what to query and what to looking for.