Timeout error on table update - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Timeout error on table update

Author

Topic

wesh
Starting Member

3 Posts

Posted - 2007-12-13 : 15:02:56

The following page code gives the error below. I have researched this and I understand that the sql command timeout needs to be extended, but at my skill level I am having trouble putting what I am reading in the context of what I am trying to do and need some help understanding how to do this. I predominantly rely on the drop in widgets present in VS2005, and building these by hand is not something I am grasping easily when it comes to accessing sql.

Any help would be appreciated.

error:
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
The statement has been terminated.

code:
<%@ Page Language="C#" MasterPageFile="~/MasterPage.master" Title="Edit Sale" %>

<asp:Content ID="Content1" ContentPlaceHolderID="MainBody" Runat="Server">
You may edit the sale in the dialer using the form below. All edits are logged.<br />
You are Editing the record as <asp:Label ID="EditUser" runat="server" Text="Wes"></asp:Label>

<br />
<br />
<asp:DetailsView ID="EditSaleForm" runat="server" AutoGenerateRows="False"
DefaultMode="Edit" Height="50px" Width="341px" DataSourceID="sqlEditSale" DataKeyNames="Campaign, crrecordid, crcalldatetime, phonenumber, ccauthcode" OnPageIndexChanging="EditSaleForm_PageIndexChanging">
<Fields>
<asp:BoundField DataField="Campaign" HeaderText="Campaign" ReadOnly="True" SortExpression="Campaign" />
<asp:BoundField DataField="crrecordid" HeaderText="crrecordid" ReadOnly="True" SortExpression="crrecordid" />
<asp:BoundField DataField="crcalldatetime" HeaderText="crcalldatetime" ReadOnly="True"
SortExpression="crcalldatetime" />
<asp:BoundField DataField="cragentid" HeaderText="cragentid" SortExpression="cragentid" />
<asp:BoundField DataField="verifierid" HeaderText="verifierid" SortExpression="verifierid" />
<asp:BoundField DataField="PhoneNumber" HeaderText="PhoneNumber" ReadOnly="True"
SortExpression="PhoneNumber" />
<asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName" />
<asp:BoundField DataField="SpouseName" HeaderText="SpouseName" SortExpression="SpouseName" />
<asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName" />
<asp:BoundField DataField="Street" HeaderText="Street" SortExpression="Street" />
<asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />
<asp:BoundField DataField="State" HeaderText="State" SortExpression="State" />
<asp:BoundField DataField="Zip" HeaderText="Zip" SortExpression="Zip" />
<asp:BoundField DataField="AltNumber" HeaderText="AltNumber" SortExpression="AltNumber" />
<asp:BoundField DataField="Email" HeaderText="Email" SortExpression="Email" />
<asp:BoundField DataField="Adults" HeaderText="Adults" SortExpression="Adults" />
<asp:BoundField DataField="Children" HeaderText="Children" SortExpression="Children" />
<asp:BoundField DataField="TaxPrice" HeaderText="TaxPrice" SortExpression="TaxPrice" />
<asp:BoundField DataField="ResortCode" HeaderText="ResortCode" SortExpression="ResortCode" />
<asp:BoundField DataField="OfferCode" HeaderText="OfferCode" SortExpression="OfferCode" />
<asp:BoundField DataField="Comments" HeaderText="Comments" SortExpression="Comments" />
<asp:BoundField DataField="CCAuthCode" HeaderText="CCAuthCode" ReadOnly="True" SortExpression="CCAuthCode" />
<asp:BoundField DataField="ArrivalDate" HeaderText="ArrivalDate" SortExpression="ArrivalDate" />
<asp:CommandField ButtonType="Button" InsertVisible="False" ShowEditButton="True" />
</Fields>
</asp:DetailsView>
<asp:SqlDataSource ID="sqlEditSale" runat="server" ConnectionString="<%$ ConnectionStrings:PRIS2ConnectionString %>" SelectCommand="sp_GetSales_GetEditSale" SelectCommandType="StoredProcedure" UpdateCommand="sp_GetSales_UpdateEditSaleBP" UpdateCommandType="StoredProcedure">
<SelectParameters>
<asp:QueryStringParameter Name="crrecordid" QueryStringField="crrecordid" Type="Int32" />
</SelectParameters>
<UpdateParameters>
<asp:ControlParameter ControlID="EditUser" Name="EditUser" Type="String" />
<asp:Parameter Name="Campaign" Type="String" />
<asp:Parameter Name="crrecordid" Type="Int32" />
<asp:Parameter Name="crcalldatetime" Type="DateTime" />
<asp:Parameter Name="cragentid" Type="String" />
<asp:Parameter Name="verifierid" Type="String" />
<asp:Parameter Name="PhoneNumber" Type="String" />
<asp:Parameter Name="FirstName" Type="String" />
<asp:Parameter Name="SpouseName" Type="String" />
<asp:Parameter Name="LastName" Type="String" />
<asp:Parameter Name="Street" Type="String" />
<asp:Parameter Name="City" Type="String" />
<asp:Parameter Name="State" Type="String" />
<asp:Parameter Name="Zip" Type="String" />
<asp:Parameter Name="AltNumber" Type="String" />
<asp:Parameter Name="Email" Type="String" />
<asp:Parameter Name="Adults" Type="String" />
<asp:Parameter Name="Children" Type="String" />
<asp:Parameter Name="TaxPrice" Type="Decimal" />
<asp:Parameter Name="ResortCode" Type="String" />
<asp:Parameter Name="OfferCode" Type="String" />
<asp:Parameter Name="Comments" Type="String" />
<asp:Parameter Name="CCAuthCode" Type="String" />
<asp:Parameter Name="ArrivalDate" Type="DateTime" />
</UpdateParameters>
</asp:SqlDataSource>
</asp:Content>

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-12-14 : 07:14:44

Instead looking at front end, check out the back end stuff. Optimize the SQL query first, if it is not getting execute faster, by trying to execute at query analizer.

thanks,

Mahesh

wesh
Starting Member

3 Posts

Posted - 2007-12-14 : 09:49:47

This was the first thing I approached. The query is as optimum as it can be. The slow down comes from the distance between the servers and usage on the SQL server. The query runs in about 45-60 seconds depending on time of day and is required to update 3 tables on 2 servers.

quote:
Originally posted by mahesh_bote

Instead looking at front end, check out the back end stuff. Optimize the SQL query first, if it is not getting execute faster, by trying to execute at query analizer.

thanks,

Mahesh

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-12-18 : 02:20:22

quote:
Originally posted by wesh

This was the first thing I approached. The query is as optimum as it can be. The slow down comes from the distance between the servers and usage on the SQL server. The query runs in about 45-60 seconds depending on time of day and is required to update 3 tables on 2 servers.

quote:
Originally posted by mahesh_bote

Instead looking at front end, check out the back end stuff. Optimize the SQL query first, if it is not getting execute faster, by trying to execute at query analizer.

thanks,

Mahesh

Though u have to update 3 table on 2 server, ur query is taking too much time to execute. first try to optimize the qry so that it will take just a second or two. Coz for executing the qry, its around a minute. Look at esitmated plan so that u can understand, where ur qry is taking too much time. Consider few thing

1. Use Inner Join instead of IN
2. keep tables in join in a manner so that table with minimum records will be at left side.

for more information try:

http://www.builderau.com.au/program/sqlserver/soa/Speed-up-SQL-Server-database-queries-with-these-tips/0,339028455,339283993,00.htm

thanks,

Mahesh

wesh
Starting Member

3 Posts

Posted - 2008-01-04 : 12:35:38

The query contains no joins.
The query runs in under 30 seconds when run in query analyser.
The Query runs 20-50% slower when run as a stored procedure.
The query runs an additional 10% slower when run from the web form.

I am asking a very specific question about asp.net that involves doing something that is new to me, and that is using a table adapter and the setting the timeout on the sqlcommand.

I appreciate the help that you are offering Mahesh, but you are taking this on a tangent that has already been explored to it's fullest and now we are at the point of giving it just a little more time to run.

Can anyone help me with this since it is an asp.net concept that is new to me?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources