I am having a problem with my ASP.Net Web application. The application is developed using vb.net and is linked to a SQL Server database. Let me explain how the application works and the problem I am experiencing.
The system is an online web app which allows registered users to create a CV online. One of the pages within the app gives users the chance to add a cover note to their CV. The page that allows them to do this consists of only a textarea control and a button
control. The textarea allows users to input up to 4,000 characters.
Once the user clicks the ‘Save’ button to save their cover note info, the following code then executes.
This code checks to see if the CV already has cover note info, if it does, then the application runs an update statement, otherwise, it runs an insert statement.
The table within the database which records the cover note information is called tbl_covernote and has three columns, covernote_id (int and autoincrement), cv_id(int), covernote_text (nvarchar(max)).
The error which occurs sometimes is as follows:
Dim dr As SqlDataReader
Dim param(0) As SqlParameter
param(0) = New SqlParameter("cv_id", Session("cvID"))
dr = DataAccess.ExecuteDataReader(param, "sp_get_covernote_info")
This code checks to see if the CV already has cover note info, if it does, then the application runs an update statement, otherwise, it runs an insert statement.
The table within the database which records the cover note information is called tbl_covernote and has three columns, covernote_id (int and autoincrement), cv_id(int), covernote_text (nvarchar(max)).
The error which occurs sometimes is as follows:
System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
However, this error does not occur very often, maybe once or twice a week, and I have also noticed that it only seems to happen when an update to the cover note information is executed, not when an insert is done.
I have read a bit about this error and some people have advised me that it could be to do with the CommandTimeout of my DbCommand. However, I increased this to 120 and I am still having this problem.
If anyone could give me some help it would be much appreciated.
If that does not help, you can set the SQLCommandtimeout to your desired time, I mean to say you can calculate the time it takes to max execute your query and set that time as timeout.You can do that as below
SQLComm.CommandTimeout = x
replace here SQLComm by your object of sqlcommand, and X by the integer value(i.e secs).
I currently have my ConnectTimeout set to 120, and although, this seems fine, I still get the odd Timeout Error.
I could increase the ConnectTimeout, but I am scared that this may be a bad thing? Also, I have no way of calculating the max length of time it takes to execute this update query.
In my opinion, increasing the timeout should be the last thing you should do.
Your focus should be on making your database perform faster. Only if there is no possible way to speed things up and you just have to live with the fact that you have a query that will take a long time to complete should you consider increasing the timeout.
However, in your case you are doing a simple update. This shouldn't take so long that it times out, so I'm pretty sure you can get a better performance first.
katie_27
0 Points
6 Posts
SQL Statement Timeout
Jan 20, 2010 12:26 PM|LINK
Hi Folks
Hopefully I am posting this in the correct forum.
I am having a problem with my ASP.Net Web application. The application is developed using vb.net and is linked to a SQL Server database. Let me explain how the application works and the problem I am experiencing.
The system is an online web app which allows registered users to create a CV online. One of the pages within the app gives users the chance to add a cover note to their CV. The page that allows them to do this consists of only a textarea control and a button control. The textarea allows users to input up to 4,000 characters.
Once the user clicks the ‘Save’ button to save their cover note info, the following code then executes.
This code checks to see if the CV already has cover note info, if it does, then the application runs an update statement, otherwise, it runs an insert statement.
The table within the database which records the cover note information is called tbl_covernote and has three columns, covernote_id (int and autoincrement), cv_id(int), covernote_text (nvarchar(max)).
The error which occurs sometimes is as follows:
Dim dr As SqlDataReader
Dim param(0) As SqlParameter
param(0) = New SqlParameter("cv_id", Session("cvID"))
dr = DataAccess.ExecuteDataReader(param, "sp_get_covernote_info")
Try
If dr.HasRows Then
'RUN UPDATE STATEMENT
Dim objCoverNoteInfo As New cv_covernote_info
Dim returnVal As Integer
objCoverNoteInfo.CoverNoteInfoText = Server.HtmlEncode(txtArea_covernote_info.InnerText)
objCoverNoteInfo.cvID = Session("cvID")
returnVal = cv_covernote_info_DAO.UpdateCovernoteInfo(objCoverNoteInfo)
Else
'RUN INSERT STATEMENT
Dim objCoverNoteInfo As New cv_covernote_info
Dim returnVal As Integer
objCoverNoteInfo. CoverNoteInfoText = Server.HtmlEncode(txtArea_covernote_info.InnerText)
objCoverNoteInfo. cvID = Session("cvID")
returnVal = cv_covernote_info_DAO.InsertCoverNoteInfo(objCoverNoteInfo)
End If
Finally
'CLOSE CONNECTION TO THE DATABASE
Dim closeConn As Integer
closeConn = DataAccess.CloseConnectionString()
End Try
This code checks to see if the CV already has cover note info, if it does, then the application runs an update statement, otherwise, it runs an insert statement.
The table within the database which records the cover note information is called tbl_covernote and has three columns, covernote_id (int and autoincrement), cv_id(int), covernote_text (nvarchar(max)).
The error which occurs sometimes is as follows:
System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
However, this error does not occur very often, maybe once or twice a week, and I have also noticed that it only seems to happen when an update to the cover note information is executed, not when an insert is done.
I have read a bit about this error and some people have advised me that it could be to do with the CommandTimeout of my DbCommand. However, I increased this to 120 and I am still having this problem.
If anyone could give me some help it would be much appreciated.
Thank You.
Kate.
Jaap78
Member
350 Points
63 Posts
Re: SQL Statement Timeout
Jan 20, 2010 01:14 PM|LINK
Hi Kate,
What is the update query / stored procedure that is executed in "cv_covernote_info_DAO.UpdateCovernoteInfo(objCoverNoteInfo)"?
Jaap
katie_27
0 Points
6 Posts
Re: SQL Statement Timeout
Jan 20, 2010 01:42 PM|LINK
Hi Japp78
The stored procedure used for updating the Cover note information is as follows:
ALTER PROCEDURE [dbo].[sp_update_covernote_info]
-- Add the parameters for the stored procedure here
@cv_id int,
@covernote_text nvarchar(max)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
update tbl_covernote
set covernote_text=@covernote_text
where cv_id=@cv_id
END
Thanks Again.
Katie.
Jaap78
Member
350 Points
63 Posts
Re: SQL Statement Timeout
Jan 20, 2010 01:52 PM|LINK
Hi Katie,
Do you have primary keys and indexes on your tables?
Jaap
katie_27
0 Points
6 Posts
Re: SQL Statement Timeout
Jan 20, 2010 02:07 PM|LINK
Hi Jaap78
The column covernote_id is the PK within the table tbl_covernote. I do not have any indexes setup within the table.
Thanks.
Katie.
Jaap78
Member
350 Points
63 Posts
Re: SQL Statement Timeout
Jan 20, 2010 02:11 PM|LINK
Try putting an index on column cv_id. This should speed things up, since you're basically using this column to look up the cover notes.
katie_27
0 Points
6 Posts
Re: SQL Statement Timeout
Jan 20, 2010 02:21 PM|LINK
Hi Jaap78
I'll make that change and hopefully that'll help.
Thanks for your help.
Katie.
NiravVyas
Participant
912 Points
217 Posts
Re: SQL Statement Timeout
Jan 20, 2010 02:31 PM|LINK
If that does not help, you can set the SQLCommandtimeout to your desired time, I mean to say you can calculate the time it takes to max execute your query and set that time as timeout.You can do that as below
SQLComm.CommandTimeout = x
replace here SQLComm by your object of sqlcommand, and X by the integer value(i.e secs).
Hope it helps,
Thanks and Regard
Nirav Vyas,
Please Mark as Answer if helpful :)
Thanks
Nirav
katie_27
0 Points
6 Posts
Re: SQL Statement Timeout
Jan 20, 2010 02:39 PM|LINK
Hi Nirav
Thanks for your help.
I currently have my ConnectTimeout set to 120, and although, this seems fine, I still get the odd Timeout Error.
I could increase the ConnectTimeout, but I am scared that this may be a bad thing? Also, I have no way of calculating the max length of time it takes to execute this update query.
Katie.
Jaap78
Member
350 Points
63 Posts
Re: SQL Statement Timeout
Jan 20, 2010 02:48 PM|LINK
Hi Katie,
In my opinion, increasing the timeout should be the last thing you should do.
Your focus should be on making your database perform faster. Only if there is no possible way to speed things up and you just have to live with the fact that you have a query that will take a long time to complete should you consider increasing the timeout.
However, in your case you are doing a simple update. This shouldn't take so long that it times out, so I'm pretty sure you can get a better performance first.
Regards,
Jaap