CipherIS
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
Below is the error being returned.
System.Data.SqlClient.SqlE xception: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. at System.Data.SqlClient.SqlC onnection. OnError(Sq lException exception, Boolean breakConnection) at System.Data.SqlClient.TdsP arser.Thro wException AndWarning (TdsParser StateObjec t stateObj) at System.Data.SqlClient.TdsP arser.Run( RunBehavio r runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlD ataReader. SetMetaDat a(_SqlMeta DataSet metaData, Boolean moreInfo) at System.Data.SqlClient.TdsP arser.Run( RunBehavio r runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlD ataReader. ConsumeMet aData() at System.Data.SqlClient.SqlD ataReader. get_MetaDa ta() at System.Data.SqlClient.SqlC ommand.Fin ishExecute Reader(Sql DataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlC ommand.Run ExecuteRea derTds(Com mandBehavi or cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlC ommand.Run ExecuteRea der(Comman dBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlC ommand.Run ExecuteRea der(Comman dBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlC ommand.Exe cuteReader (CommandBe havior behavior, String method) at System.Data.SqlClient.SqlC ommand.Exe cuteReader () at Application.C_Connection.D isplayResu lt(String SQLString, String[] champ) at Application.M_SalesOrder.L oadCustome rs()
Seems it is occurring when connecting.
Below is the ADO 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
Below is the error being returned.
System.Data.SqlClient.SqlE
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
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.
ASKER
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.
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?