Get Help:Ask a Question in our Forums|Report a Bug|More Help Resources
Last post Feb 18, 2010 08:09 AM by katie_27
Jan 20, 2010 12:26 PM|LINK
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")
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)
'RUN INSERT STATEMENT
objCoverNoteInfo. CoverNoteInfoText = Server.HtmlEncode(txtArea_covernote_info.InnerText)
objCoverNoteInfo. cvID = Session("cvID")
returnVal = cv_covernote_info_DAO.InsertCoverNoteInfo(objCoverNoteInfo)
'CLOSE CONNECTION TO THE DATABASE
Dim closeConn As Integer
closeConn = DataAccess.CloseConnectionString()
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.
Jan 20, 2010 01:14 PM|LINK
What is the update query / stored procedure that is executed in "cv_covernote_info_DAO.UpdateCovernoteInfo(objCoverNoteInfo)"?
Jan 20, 2010 01:42 PM|LINK
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
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
Jan 20, 2010 01:52 PM|LINK
Do you have primary keys and indexes on your tables?
Jan 20, 2010 02:07 PM|LINK
The column covernote_id is the PK within the table tbl_covernote. I do not have any indexes setup within the table.
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.
Jan 20, 2010 02:21 PM|LINK
I'll make that change and hopefully that'll help.
Thanks for your help.
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
Please Mark as Answer if helpful :)
Jan 20, 2010 02:39 PM|LINK
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.
Jan 20, 2010 02:48 PM|LINK
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.