Timeout expired

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

We have this issue on one of our pages and we can not figure out what is
happning.
This sp (SQL2000) runs in less than a second on SQL. However, once app is
deployed problems are happening.
I also tried to connect to same database that deployed app is pointing to
from my local development and submit action is fast.
SP does following:
Select from Table1
Update Table1
Insert into Table1
Insert into Table2

Table1 has less than 1500 records, 56 columns, Table2 has less than 500
records and is replica of Table1 with few additional columns.
Table1 has trigger (insert, updated, delete) but we don't think trigger is
issue (we tried removing it).
Also, app does other transactions on Table1 - but always just one (insert,
uodate or delete) and never any issue with it.
Code we have looks like following:
VB page calls BusinesssRules (we tried Transaction = disabled and
=RequiresNew)
This BR calls DataAccess class and in that function we have ExecuteNonQuery.
Connection object is a black box to us I can only hope that there is no
exclusive locks on tables.
What I also notices is that once I hit submit on App and then go to db even
simple select statement on Table 1 would take long (combined time since
Submit and running statement from db is around 1 minut).
But this was not consistent behaviour; just as this timeout would work
sometimes and performance would be ok and most of time it is a pain.


Following is the error from App event log.

Assembly: WebUI, Version=2.1.4.0, Culture=neutral, PublicKeyToken=null
Method:ASP.ET_Futures_Contract_aspx InsertRecord Exception: Timeout expired.
The timeout period elapsed prior to completion of the operation or the server
is not responding. Source: .Net SqlClient Data Provider Stack:
Server stack trace:
Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage
reqMsg, IMessage retMsg)
at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData&
msgData, Int32 type)
at BusinessRules.CBOFuturesContract.Insert(String[]& arrCallerInfo, Int64
lOPCORollupSeq, Int64 lTraderSeq, Int64 lCommodityStrategySeq, Int64
lBrokerSeq, DateTime dtFromDateDt, DateTime dtToDateDt, DateTime
dtTradeDateDt, String strSideCd, Int64 lCommodityRollUpSeq, Int32
iContractMonthNum, Int32 iContractYearNum, Int32 iContractQtyNum, Decimal
decStrikeMny, Int64 lStrikeCurrencySeq, Int64 lStrikeUnitSeq, Decimal
decComissionMny, Int64 lComissionCurrencySeq, Int64 lComissionUnitSeq,
Decimal decComissionPercentPrc, String strAccountNumber, Int32
iFirstNoticeNum, Int32 iFirstNoticeIntervalNum, Int32 iRecuringNoticeNum,
Int32 iRecuringNoticeIntervalNum, String strSubjectD, String
strNotificationD, Int32 iContract_seq, String strChildTypeCd, Object
dtExpirationDate_dt, Int32 iCollar_seq)
at FuturesContract.InsertRecord(Int32 parentSeq, Int32 iCollarSeq)

I hope someone can help out.
thanks
 
LjP,

Okay, I assume you are using EnterpriseServices to rope in distributed
transactions. And if you can confirm that is indeed the case, I have a
pretty reasonable theory on why you are getting this problem and what you
can do to fix it.

But well, I'm lazy and I don't wanna type so much without knowing that itty
bitty peice of information :-) .. so are you using EnterpriseServices, or is
MSDTC involved in any manner whatsoever?


--

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
----------------------------------------------------------------------------
---------------



LjP said:
We have this issue on one of our pages and we can not figure out what is
happning.
This sp (SQL2000) runs in less than a second on SQL. However, once app is
deployed problems are happening.
I also tried to connect to same database that deployed app is pointing to
from my local development and submit action is fast.
SP does following:
Select from Table1
Update Table1
Insert into Table1
Insert into Table2

Table1 has less than 1500 records, 56 columns, Table2 has less than 500
records and is replica of Table1 with few additional columns.
Table1 has trigger (insert, updated, delete) but we don't think trigger is
issue (we tried removing it).
Also, app does other transactions on Table1 - but always just one (insert,
uodate or delete) and never any issue with it.
Code we have looks like following:
VB page calls BusinesssRules (we tried Transaction = disabled and
=RequiresNew)
This BR calls DataAccess class and in that function we have ExecuteNonQuery.
Connection object is a black box to us I can only hope that there is no
exclusive locks on tables.
What I also notices is that once I hit submit on App and then go to db even
simple select statement on Table 1 would take long (combined time since
Submit and running statement from db is around 1 minut).
But this was not consistent behaviour; just as this timeout would work
sometimes and performance would be ok and most of time it is a pain.


Following is the error from App event log.

Assembly: WebUI, Version=2.1.4.0, Culture=neutral, PublicKeyToken=null
Method:ASP.ET_Futures_Contract_aspx InsertRecord Exception: Timeout expired.
The timeout period elapsed prior to completion of the operation or the server
is not responding. Source: .Net SqlClient Data Provider Stack:
Server stack trace:
Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage
reqMsg, IMessage retMsg)
at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData&
msgData, Int32 type)
at BusinessRules.CBOFuturesContract.Insert(String[]& arrCallerInfo, Int64
lOPCORollupSeq, Int64 lTraderSeq, Int64 lCommodityStrategySeq, Int64
lBrokerSeq, DateTime dtFromDateDt, DateTime dtToDateDt, DateTime
dtTradeDateDt, String strSideCd, Int64 lCommodityRollUpSeq, Int32
iContractMonthNum, Int32 iContractYearNum, Int32 iContractQtyNum, Decimal
decStrikeMny, Int64 lStrikeCurrencySeq, Int64 lStrikeUnitSeq, Decimal
decComissionMny, Int64 lComissionCurrencySeq, Int64 lComissionUnitSeq,
Decimal decComissionPercentPrc, String strAccountNumber, Int32
iFirstNoticeNum, Int32 iFirstNoticeIntervalNum, Int32 iRecuringNoticeNum,
Int32 iRecuringNoticeIntervalNum, String strSubjectD, String
strNotificationD, Int32 iContract_seq, String strChildTypeCd, Object
dtExpirationDate_dt, Int32 iCollar_seq)
at FuturesContract.InsertRecord(Int32 parentSeq, Int32 iCollarSeq)

I hope someone can help out.
thanks
Click to expand...
 
While bulding the SqlCommand object you can set the "CommandTimeout" property
value to a larger value may be 60 to 180 seconds.
This value (in seconds) waits for the command to execute. The default is 30
seconds. If it is set to 0 it waits indefinitely and may cause infinite loop
situation if the command execution doesn't respond.

Vivek Natani
MCAD

LjP said:
We have this issue on one of our pages and we can not figure out what is
happning.
This sp (SQL2000) runs in less than a second on SQL. However, once app is
deployed problems are happening.
I also tried to connect to same database that deployed app is pointing to
from my local development and submit action is fast.
SP does following:
Select from Table1
Update Table1
Insert into Table1
Insert into Table2

Table1 has less than 1500 records, 56 columns, Table2 has less than 500
records and is replica of Table1 with few additional columns.
Table1 has trigger (insert, updated, delete) but we don't think trigger is
issue (we tried removing it).
Also, app does other transactions on Table1 - but always just one (insert,
uodate or delete) and never any issue with it.
Code we have looks like following:
VB page calls BusinesssRules (we tried Transaction = disabled and
=RequiresNew)
This BR calls DataAccess class and in that function we have ExecuteNonQuery.
Connection object is a black box to us I can only hope that there is no
exclusive locks on tables.
What I also notices is that once I hit submit on App and then go to db even
simple select statement on Table 1 would take long (combined time since
Submit and running statement from db is around 1 minut).
But this was not consistent behaviour; just as this timeout would work
sometimes and performance would be ok and most of time it is a pain.


Following is the error from App event log.

Assembly: WebUI, Version=2.1.4.0, Culture=neutral, PublicKeyToken=null
Method:ASP.ET_Futures_Contract_aspx InsertRecord Exception: Timeout expired.
The timeout period elapsed prior to completion of the operation or the server
is not responding. Source: .Net SqlClient Data Provider Stack:
Server stack trace:
Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage
reqMsg, IMessage retMsg)
at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData&
msgData, Int32 type)
at BusinessRules.CBOFuturesContract.Insert(String[]& arrCallerInfo, Int64
lOPCORollupSeq, Int64 lTraderSeq, Int64 lCommodityStrategySeq, Int64
lBrokerSeq, DateTime dtFromDateDt, DateTime dtToDateDt, DateTime
dtTradeDateDt, String strSideCd, Int64 lCommodityRollUpSeq, Int32
iContractMonthNum, Int32 iContractYearNum, Int32 iContractQtyNum, Decimal
decStrikeMny, Int64 lStrikeCurrencySeq, Int64 lStrikeUnitSeq, Decimal
decComissionMny, Int64 lComissionCurrencySeq, Int64 lComissionUnitSeq,
Decimal decComissionPercentPrc, String strAccountNumber, Int32
iFirstNoticeNum, Int32 iFirstNoticeIntervalNum, Int32 iRecuringNoticeNum,
Int32 iRecuringNoticeIntervalNum, String strSubjectD, String
strNotificationD, Int32 iContract_seq, String strChildTypeCd, Object
dtExpirationDate_dt, Int32 iCollar_seq)
at FuturesContract.InsertRecord(Int32 parentSeq, Int32 iCollarSeq)

I hope someone can help out.
thanks
Click to expand...
 
We figured out what was the issue.
Flow of action goes like this:
There is a dropdown on the page that causes postback and executes a faction
in which did not have 'autocomplete' in CBO and the query executed through
this function goes to the same table that submit button is going to. So by
the time Submit was triggered table was locked.
Now, that is OK now.
However, there is another issue that started occurring recently.
This page (same one) has several tabs. Submit action on one of those started
being quite slow recently. So I thought it has same issue (missing
autoComplete) but it is not.
I did notice that it is faster when CBO is compiled with
Transaction.Disabled which is not acceptable as another function (previously
described) is in the same CBO (not that we can not move it) and we need to
have it. But the thing is, performance used to be OK. Just to add, it goes
against same table in the DB.
Increasing timeout time is not acceptable option as business would not be
happy to click Submit and whistle for few minutes :))
Sahil Malik said:
LjP,

Okay, I assume you are using EnterpriseServices to rope in distributed
transactions. And if you can confirm that is indeed the case, I have a
pretty reasonable theory on why you are getting this problem and what you
can do to fix it.

But well, I'm lazy and I don't wanna type so much without knowing that itty
bitty peice of information :-) .. so are you using EnterpriseServices, or is
MSDTC involved in any manner whatsoever?


--

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
----------------------------------------------------------------------------
---------------



LjP said:
We have this issue on one of our pages and we can not figure out what is
happning.
This sp (SQL2000) runs in less than a second on SQL. However, once app is
deployed problems are happening.
I also tried to connect to same database that deployed app is pointing to
from my local development and submit action is fast.
SP does following:
Select from Table1
Update Table1
Insert into Table1
Insert into Table2

Table1 has less than 1500 records, 56 columns, Table2 has less than 500
records and is replica of Table1 with few additional columns.
Table1 has trigger (insert, updated, delete) but we don't think trigger is
issue (we tried removing it).
Also, app does other transactions on Table1 - but always just one (insert,
uodate or delete) and never any issue with it.
Code we have looks like following:
VB page calls BusinesssRules (we tried Transaction = disabled and
=RequiresNew)
This BR calls DataAccess class and in that function we have ExecuteNonQuery.
Connection object is a black box to us I can only hope that there is no
exclusive locks on tables.
What I also notices is that once I hit submit on App and then go to db even
simple select statement on Table 1 would take long (combined time since
Submit and running statement from db is around 1 minut).
But this was not consistent behaviour; just as this timeout would work
sometimes and performance would be ok and most of time it is a pain.


Following is the error from App event log.

Assembly: WebUI, Version=2.1.4.0, Culture=neutral, PublicKeyToken=null
Method:ASP.ET_Futures_Contract_aspx InsertRecord Exception: Timeout expired.
The timeout period elapsed prior to completion of the operation or the server
is not responding. Source: .Net SqlClient Data Provider Stack:
Server stack trace:
Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage
reqMsg, IMessage retMsg)
at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData&
msgData, Int32 type)
at BusinessRules.CBOFuturesContract.Insert(String[]& arrCallerInfo, Int64
lOPCORollupSeq, Int64 lTraderSeq, Int64 lCommodityStrategySeq, Int64
lBrokerSeq, DateTime dtFromDateDt, DateTime dtToDateDt, DateTime
dtTradeDateDt, String strSideCd, Int64 lCommodityRollUpSeq, Int32
iContractMonthNum, Int32 iContractYearNum, Int32 iContractQtyNum, Decimal
decStrikeMny, Int64 lStrikeCurrencySeq, Int64 lStrikeUnitSeq, Decimal
decComissionMny, Int64 lComissionCurrencySeq, Int64 lComissionUnitSeq,
Decimal decComissionPercentPrc, String strAccountNumber, Int32
iFirstNoticeNum, Int32 iFirstNoticeIntervalNum, Int32 iRecuringNoticeNum,
Int32 iRecuringNoticeIntervalNum, String strSubjectD, String
strNotificationD, Int32 iContract_seq, String strChildTypeCd, Object
dtExpirationDate_dt, Int32 iCollar_seq)
at FuturesContract.InsertRecord(Int32 parentSeq, Int32 iCollarSeq)

I hope someone can help out.
thanks
Click to expand...
Click to expand...
 
We figured out what was the issue.
Flow of action goes like this:
There is a dropdown on the page that causes postback and executes a faction
in which did not have 'autocomplete' in CBO and the query executed through
this function goes to the same table that submit button is going to. So by
the time Submit was triggered table was locked.
Now, that is OK now.
However, there is another issue that started occurring recently.
This page (same one) has several tabs. Submit action on one of those started
being quite slow recently. So I thought it has same issue (missing
autoComplete) but it is not.
I did notice that it is faster when CBO is compiled with
Transaction.Disabled which is not acceptable as another function (previously
described) is in the same CBO (not that we can not move it) and we need to
have it. But the thing is, performance used to be OK. Just to add, it goes
against same table in the DB.
Increasing timeout time is not acceptable option as business would not be
happy to click Submit and whistle for few minutes :))
And, no, we are not using MSDTC.
Vivek Natani said:
While bulding the SqlCommand object you can set the "CommandTimeout" property
value to a larger value may be 60 to 180 seconds.
This value (in seconds) waits for the command to execute. The default is 30
seconds. If it is set to 0 it waits indefinitely and may cause infinite loop
situation if the command execution doesn't respond.

Vivek Natani
MCAD

LjP said:
We have this issue on one of our pages and we can not figure out what is
happning.
This sp (SQL2000) runs in less than a second on SQL. However, once app is
deployed problems are happening.
I also tried to connect to same database that deployed app is pointing to
from my local development and submit action is fast.
SP does following:
Select from Table1
Update Table1
Insert into Table1
Insert into Table2

Table1 has less than 1500 records, 56 columns, Table2 has less than 500
records and is replica of Table1 with few additional columns.
Table1 has trigger (insert, updated, delete) but we don't think trigger is
issue (we tried removing it).
Also, app does other transactions on Table1 - but always just one (insert,
uodate or delete) and never any issue with it.
Code we have looks like following:
VB page calls BusinesssRules (we tried Transaction = disabled and
=RequiresNew)
This BR calls DataAccess class and in that function we have ExecuteNonQuery.
Connection object is a black box to us I can only hope that there is no
exclusive locks on tables.
What I also notices is that once I hit submit on App and then go to db even
simple select statement on Table 1 would take long (combined time since
Submit and running statement from db is around 1 minut).
But this was not consistent behaviour; just as this timeout would work
sometimes and performance would be ok and most of time it is a pain.


Following is the error from App event log.

Assembly: WebUI, Version=2.1.4.0, Culture=neutral, PublicKeyToken=null
Method:ASP.ET_Futures_Contract_aspx InsertRecord Exception: Timeout expired.
The timeout period elapsed prior to completion of the operation or the server
is not responding. Source: .Net SqlClient Data Provider Stack:
Server stack trace:
Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage
reqMsg, IMessage retMsg)
at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData&
msgData, Int32 type)
at BusinessRules.CBOFuturesContract.Insert(String[]& arrCallerInfo, Int64
lOPCORollupSeq, Int64 lTraderSeq, Int64 lCommodityStrategySeq, Int64
lBrokerSeq, DateTime dtFromDateDt, DateTime dtToDateDt, DateTime
dtTradeDateDt, String strSideCd, Int64 lCommodityRollUpSeq, Int32
iContractMonthNum, Int32 iContractYearNum, Int32 iContractQtyNum, Decimal
decStrikeMny, Int64 lStrikeCurrencySeq, Int64 lStrikeUnitSeq, Decimal
decComissionMny, Int64 lComissionCurrencySeq, Int64 lComissionUnitSeq,
Decimal decComissionPercentPrc, String strAccountNumber, Int32
iFirstNoticeNum, Int32 iFirstNoticeIntervalNum, Int32 iRecuringNoticeNum,
Int32 iRecuringNoticeIntervalNum, String strSubjectD, String
strNotificationD, Int32 iContract_seq, String strChildTypeCd, Object
dtExpirationDate_dt, Int32 iCollar_seq)
at FuturesContract.InsertRecord(Int32 parentSeq, Int32 iCollarSeq)

I hope someone can help out.
thanks
Click to expand...
Click to expand...
 
Back
Top