Newbie Question - Web App hangs

[whateveragain]

I'm running SQL stored procedures from ASP.net. These stored procedures are linked together by one SP calling another SP. It works fine when I run a trial run from SQL; however, when I run it from ASP.net the app hangs. It never hangs in the same SP and it doesn't hang in the same spot; however, I always get a timeout expired error. Does anyone understand the stackTrace so I can find out what is wrong?

System.Data.SqlClient.SqlException was unhandled by user code
Class=11
ErrorCode=-2146232060
LineNumber=0
Message="Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. start of giving a-j a value end of giving a-j a value zip_code, fips_code, st_abbrev, msa_fipsst, msa_code, dma_code, a12, a13, a36, codeID, rate, lowrate, pctmkt, bkey, a, b, c, d, e, f, g, h, i, j, used, second as as #5 @OKCAT = BB,NB,WY,Y ,C , C @OKCATOR = BB,NB,WY,Y ,C , C @OKCATOR = BB,NB,WY,Y ,C , C @OKA = BB @OKB = NB @OKC = WY @OKD = Y @OKE = @OKF = C @OKG = C @OKH = BB @OKI = NB @OKJ = WY @OKK = Y @OKL = @OKM = C @OKN = C @OKO = @OKP = @OKQ = @OKR = creating appp @SQL10 = select * into APPPi0k23drwpdhy3vy3ae0w3a55 from OPPP1i0k23drwpdhy3vy3ae0w3a55 where ((DIR_CAT = 'BB') or (DIR_CAT = 'NB') or (DIR_CAT = 'WY') or (DIR_CAT = 'Y ') or (DIR_CAT = ' ') or (DIR_CAT = 'C ') or (DIR_CAT = ' C') or (DIR_CAT = 'BB') or (DIR_CAT = 'NB') or (DIR_CAT = 'WY') or (DIR_CAT = 'Y ') or (DIR_CAT = ' ') or (DIR_CAT = ' C') or (DIR_CAT = 'C ') or (DIR_CAT = ' ') or (DIR_CAT = ' ') or (DIR_CAT = ' ') or (DIR_CAT = ' ')) and ((DIR_TYPE = 'U') or (DIR_TYPE = ' ') or (DIR_TYPE = 'U') or (DIR_TYPE = 'M')) creating bppp creating ppp creating firststep creating rankfile altering table rankfile creating atemp entering copycolumn in copycolumn - @columnname = A12 processing a12 to a back from copycolumn entering copycolumn #2 column b"
Number=-2
Procedure=""
Server="\\.\pipe\87D7030E-F9C9-47\tsql\query"
Source=".Net SqlClient Data Provider"
State=0
StackTrace:
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadNetworkPacket()
at System.Data.SqlClient.TdsParserStateObject.ReadBuffer()
at System.Data.SqlClient.TdsParserStateObject.ReadByteArray(Byte[] buff, Int32 offset, Int32 len)
at System.Data.SqlClient.TdsParserStateObject.ReadString(Int32 length)
at System.Data.SqlClient.TdsParser.ProcessError(Byte token, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Title.makectyrankfile() in C:\Users\Joanne\Documents\Visual Studio 2008\WebSites\WebSite1\Title.aspx.vb:line 262
at Title.Button2_Click(Object sender, EventArgs e) in C:\Users\Joanne\Documents\Visual Studio 2008\WebSites\WebSite1\Title.aspx.vb:line 490
at System.Web.UI.WebControls.Button.OnClick(EventArgs e)
at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
InnerException:

 

[ca8msm]

There's nothing in the stack trace apart from the fact that it is timing out. How long do the procedures take to finish when ran directly?

Mark,

Darlington Web Design
Experts, Information, Ideas & Knowledge
ASP.NET Tips & Tricks

 

[jmeckley]

when you run the proc(s) from QA (or other sql server UI) there is no limit to how long a query will take. With ASP.Net a request will timeout after 30 seconds. Chances are your query is taking more than 30 seconds, or close to it because IIS and ASP.Net still has to process the code.

To resolve the execution time errors you will need to
1. examine the sql statements. maybe they can be simplified.
2. examine the table indexing. chances are the statements could benefit from a better indexing strategy.

for more help with this I would use forum183


Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[jbenson001]

Just looking at the bit of SQL from your stack trace and all I can say is that it is AWEFUL. As Mark and Jason stated, it is probably running more than 30 seconds and that is way too long to expect someone to wait for results. If you are not fluent with SQL, have a DBA optimize the query and tables.

 

[kss444]

http://geekswithblogs.net/chrishan/archive/2007/07/18/114030.aspx

Ordinary Programmer

 

[whateveragain]

I admit I'm just learning this stuff, but an experienced person wrote a smaller web app with similar computations. These computations are lengthy; therefore, he had the reports emailed to the user so the user didn't have to wait. However, I don't think it's the app. The hang is at the connection string. I deliberately put print messages in the SQL code. That is why it looks messy. I ran it again and cleaned out all of the print messages and added only one print message which is the very first line of code immediately after the parameters: "just entered makectyrankfile". Below shows a hang prior to the running of the code. I don't understand why as the connection string works on all other stored procedure calls:


System.Data.SqlClient.SqlException was unhandled by user code
Class=15
ErrorCode=-2146232060
LineNumber=1
Message="Incorrect syntax near the keyword 'where'. Incorrect syntax near the keyword 'where'. ....... Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. just entered makectyrankfile
Number=156
Procedure=""
Server="\\.\pipe\87D7030E-F9C9-47\tsql\query"
Source=".Net SqlClient Data Provider"
State=1
StackTrace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Title.makectyrankfile() in C:\Users\Joanne\Documents\Visual Studio 2008\WebSites\WebSite1\Title.aspx.vb:line 212
at Title.Button2_Click(Object sender, EventArgs e) in C:\Users\Joanne\Documents\Visual Studio 2008\WebSites\WebSite1\Title.aspx.vb:line 438
at System.Web.UI.WebControls.Button.OnClick(EventArgs e)
at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
InnerException:

 

[Turkbear]

Hi,
Try replacing this:

 ((DIR_CAT = 'BB') or (DIR_CAT = 'NB') or (DIR_CAT = 'WY') or (DIR_CAT = 'Y ') or (DIR_CAT = '  ') or (DIR_CAT = 'C ') or (DIR_CAT = ' C') or (DIR_CAT = 'BB') or (DIR_CAT = 'NB') or (DIR_CAT = 'WY') or (DIR_CAT = 'Y ') or (DIR_CAT = '  ') or (DIR_CAT = ' C') or (DIR_CAT = 'C ') or (DIR_CAT = '  ') or (DIR_CAT = '  ') or (DIR_CAT = '  ') or (DIR_CAT = '  ')) and ((DIR_TYPE = 'U') or (DIR_TYPE = ' ') or (DIR_TYPE = 'U') or (DIR_TYPE = 'M'))

With an IN statement

where DIR_CAT IN ('BB','NB','WY','Y ',' ','C ',...
AND
DIR_TYPE In ('U',' ','M'...

As so forth - far fewer chances to mismatch parens and should parse much more quickly than a series of ORs..





To Paraphrase:"The Help you get is proportional to the Help you give.."