October 19, 2005 at 10:27 am
Hi,
I am getting this timeout expired error from my clientsearch page. It does not happen all the time, once or twice a day. Everytime I see this error in the errorlog or get an error email, I go back and check this clientsearch page and it works fine, I don't get any errors.
This is what I am doing in this page. I have like 20 input fields (fname, lname, address, city...) where user can enter the search criteria. I pass these values as parameters to a stored procedure, where I dynamically construct a sql based on all the non-empty parameters. I execute the sql and return the results. Can any one tell me what could be the problem.
Thanks.
October 19, 2005 at 10:28 am
This is the error message.
Error Message:
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Stack Trace:
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at clients.clientsearch.CmdSearch_Click(Object sender, EventArgs e)
October 19, 2005 at 10:36 am
Does it happen at set times? Perhaps a job, heavy querie, network congestion got in the way.
Is it reproducable (same parameters, ....)?
You can still increase the timeout of the command object.
October 19, 2005 at 10:42 am
No, it happens at different times. I am checking the page once in a while, but I did not get any errors so far.
October 20, 2005 at 1:11 am
See if this helps you
http://vyaskn.tripod.com/watch_your_timeouts.htm
Failing to plan is Planning to fail
October 20, 2005 at 4:24 am
What's the Query or Stored procedure that's running? Complex views quite often show this behaviour!?
October 20, 2005 at 12:01 pm
This is a stored procedure. I join two tables, there is no views. I construct a sql stmnt based on the search parameters. The sql goes something like this,
"SELECT ...list... FROM A, B WHERE A.id = B.id"
and for every non-empty input parameter I add to the where condition
IF @name ''
SET @sql = @sql + ' and fname like ''' + @name + ''''
IF @company ''
SET @sql = @sql + ' and companyname like ''' + @company + ''''
IF @email ''
SET @sql = @sql + ' and email like ''' + @email + ''''
and so on. Finally I execute the sql and return the results.
October 20, 2005 at 9:40 pm
Maybe someone is injecting code. It appears vulnerable to that, and would safer if you used proper SQL parameters instead of string-concatenation.
Most likely someone is using leading wildcard-characters on the LIKE parameters that can't be optimized.
If you catch the parameter-string from a .Net error page or run a lightweight SQL trace (capture only the sproc/RPC) until you catch one of the timeouts, you should be able to showplan and walk through execution with the with parameters, to quickly see where your problem is. Imaginative test cases attempting to circumvent indexes with wildcard searches would probably expose worst-case plans, too.
Gary
October 21, 2005 at 7:05 pm
Also Like statements can be a resource hog as well. It is generally a good idea to stay away from them, unless it's absolutely necessary to use them.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply