none
Timeout Exception while calling Stored Procedure from TableAdapter RRS feed

  • Question

  • Hi there,

     

    I have a nasty issue that almost makes me nuts!

     

    I get a Timeout Expired Exception while calling a certain Stored Procedure through a table adapter from ASP.NET.

    The table that is queried has 50k rows, not too big one might think...

     

    The stored procedure is used in the context of getting the total number of rows matching a fulltext query.

    It plays its part in implementing efficient paging with a GridView.

     

    There was a post in the past from a user that had a similar problem, but he instead had a stored procedure that returned a bunch of rows, where my SP only returns the count of rows - a single scalar value.

    (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1473354&SiteID=1)

     

    I'm not quite sure if it is a problem of the SP itself (in which case this would be the wrong place to post...), but i bet there are SQL gurus in this forum too. So I'll paste the part of the SP to which the problem applies (there are conditional blocks that lock out each other, so only this piece is executed in my case):

     

    Code Block

    WITH Entries AS (

    SELECT [Price] FROM [Objects] WHERE CONTAINS(([Model], [Description], [Number]), @phrase)

    )

    SELECT Count(*) FROM Entries WHERE [Price] BETWEEN @price_l AND @price_h

     

     

    - where @phrase is varchar(200) and the two prices are decimal(18,2).

     

    Like in the other post, my SP takes fractions of seconds to execute in SSMSE. Also, if I test against the database on my local system, everything is lightening-fast. But on the deployment machine (brand new web server with WS2003 R2 SP2), it happens very often that I get the following Exception:

     

    Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
      at System.Data.SqlClient.SqlConnection.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.SqlDataReader.ConsumeMetaData()
      at System.Data.SqlClient.SqlDataReader.get_MetaData()
      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.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
      at System.Data.SqlClient.SqlCommand.ExecuteScalar()
      at DAL.HardwarePortalExtendedTableAdapters.ObjekteTableAdapter.GetObjekteCountBySearch(String phrase, Nullable`1 price_l, Nullable`1 price_h, Nullable`1 groupID, Nullable`1 vendorID)
      at BLL.ObjekteManager.GetObjekteCountBySearch(String phrase, Nullable`1 price_l, Nullable`1 price_h, Nullable`1 groupID, Nullable`1 vendorID)

     

    I'm using SQLExpress 2005 w/ Advanced Services, .NET 3.5 and VS 2008 all with the newest updates.

    The three columns are fulltext indexed, the Price column is indexed including the ID column of the table.

     

    Perhaps I should also mention that the ASP.NET page is ASP.NET AJAX enabled (with the current AJAX version built in ASP.NET).

     

    I bet, there are some of you slapping their hands together over their head, calling me nuts. But I can't find the reason for this and I'm sitting here for a heck of while...

     

    Any help would be greatly appreciated!

     

    Thanks in advance,

     

    Dennis

     

    Saturday, December 22, 2007 1:42 PM

All replies

  • Hi again,

     

    turns out to be a problem of the SP. If I rewrite the SP like so

     

    Code Block

    SELECT Count(*) FROM [Objects] WHERE [Price] BETWEEN @price_l AND @price_h AND CONTAINS(([Model], [Description], [Number]), @phrase))

     

     

    thus avoiding to split it up into two statements, the error doesn't occur anymore.

     

    EDIT: THIS IS NOT TRUE - ERROR REOCCURS

     

     

    Unfortunately this way the SP is much slower than the other way. An execution takes some one or two seconds against a well indexed table with 50k rows. This is not very fast.

     

    Has any of you a SP that accomplishes the same, but doesn't use that much time???

     

    I bet this is an everyday problem, isn't it?

     

    Any SQL guru out there?

     

    Help would be greatly appreciated!

     

    Dennis

    Saturday, December 22, 2007 5:38 PM
  • Guessed too soon - this was NOT the solution.

     

    As I view the page over the internet, the same Timeout Exception returns...

     

    Man, this is so annoying. I've tried so hard.

     

    Is there someone that can help?

     

    Dennis

     

    Saturday, December 22, 2007 6:57 PM