Web-App waiting for indefinite time

Hi,

I have got an interesting problem. It seems to be some architectural bug thats why im placing this post in this forum. I have got an inline query which i try to execute from my web application. Using the SqlCommand + DataSet, i am trying to execute the query but it does not work (will explain the problem shortly). I also placed the same query in a stored procedure, call it from my web app but its not working. The interesting point is that if i execute the same query or stored procedure using Sql Server 2005 environment, they work perfectly alright. But when i try to execute the same query/procedure from my web application, sql server goes in an ever lasting processing loop and then after 30 seconds (default for SqlCommand object), the application comes back with the following error:

"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding"

I even set the SqlCommand.CommandTimeOut property to 0 (unlimited), but sql server just keeps on processing the request and never returns a result. As i mentioned, the same query/procedure works fine directly within SQL Server 2005 environment, but when i call them from my web app, it just keeps on processing the request. This really amazes me.

Any help will be greatly appreciated...

Re: Web-App waiting for indefinite time

What is the size of the data being returned by your query? Is the DB on the same machine as the web app?

-Vivek

Re: Web-App waiting for indefinite time

Hi Vivik,

Thanks for the reply. Well there are hardly 500 rows (each with mere 10 columns) and in the future the rows wont be more than 2000. Yes, the database is on the same machine. As i said above, the query/stored procedure returns the perfect result within SQL 2005 query environment but when executing it through the web-app, it waits indefinitely until it timesout.

Let me also add, that the query has got a few nested select statement. When i commented out the nested statements, the page worked fine. For your info, following is a snippet of one of the nested query:

SELECT ....

(

SELECT SUM (T2.PointsEarned)

FROM Table2 T2

WHERE T2.ID_fk = T1.ID

AND (T2.DateStarted BETWEEN @Date1 AND @Date2)

) TotalPoints,

FROM Table1 T1 INNER JOIN ....

I wonder if there is a problem with the query, why does it get executed perfectly within SQL Server 2005 query environment. But when calling it through a web-page, it waits forever eventually timing-out. I just have a weired feeling if this is somehow bothering the dataset. I did place a breakpoint on SqlDataAdapter.Fill (DataSet) (last statement before the try ... catch block) and this is where the exception is raised. I mean no data is populated...

Thanks...

Re: Web-App waiting for indefinite time

Hi Khasif,

Writing Sub query will always make things complicated from the performance point of view, instead you may use the table variable in your query and populate all sorts of data in that table and return that table from the procedure.

Anyways in your case this shouldnt be the problem as there are very less data in your table. Run SQL Profiler to see exactly what query is getting into your db from you application.

Re: Web-App waiting for indefinite time

Hi Abhishek,

Thanks for the reply. You see, this is what has been bothering for me so long. I ran the SQL Profiler and was looking at the query which was being executed. I executed the query (from the sql profiler) in sql management studio and it worked according to my expectation. But when i tried to run it from my web-app, it just hangs out. I even wrote the parameters (only 2 in this case) to a file and they were just fine. Infact, the same parameters could be seen under Sql Profiler.

Somehow, Could this be a possible framework bug under ADO.NET (forgive me for my strightforwardness). If the query is working perfectly under sql server and in a stored procedure than it should definitely get called from the web-app. Although now im thinking of rewriting the whole query but it keeps me satiated for a reply.

Ok, one more thing, i printed the StackTrace Message from the exception and this is what is making me say in the above paragraph:

.........
was not returned properly. 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.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.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, 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 .........
in ..........

In the above case, it seems that the DataSet (from the last few lines) is not getting populated. If the query is well executed in sql studio but not able to return data to the DataSet then this leave me totally confused. Can any guru point out the problem (most probably my mistake).

Thanks...

Re: Web-App waiting for indefinite time

Paste me the whole stored procedure and le me have a look into that

Re: Web-App waiting for indefinite time

SELECT E.EmpID, E.Name, E.Status_fk, E.Marital_fk, E.Address,

          (
                 SELECT  SUM (P.HoursWorked) FROM  Presence P
                 WHERE  (P.EmpID_fk = E.EmpID)
                 AND  (P.DateWorked Between @JoinDate AND @CurrentDate)
          )     TotalHoursWorked,

         (
                SELECT  SUM (P.HoursAbsent)
                FROM  Presence P
                WHERE  (P.EmpID_fk = E.EmpID)
                AND  (P.DateWorked Between @JoinDate AND @CurrentDate)
          )    TotalHoursAbsent,

         (
                SELECT  SUM (P.HoursAbsent) - SUM (P.HoursAbsent)
                FROM  Presence P
                WHERE  (P.EmpID_fk = E.EmpID)
                AND  (P.DateWorked Between @JoinDate AND @CurrentDate)
          ) AbsentDifference,

E.Age

FROM Employee E

INNER JOIN EmployeeStatus ES

ON E.Status_fk = ES.Status_ID

INNER JOIN Marital M

ON E.Marital_fk = M.Marital_ID

INNER JOIN Presence P

ON E.EmpID = P.EmpID_fk

WHERE (P.DateWorked Between @JoinDate AND @CurrentDate)

AND (E.Status_fk > 5)

ORDER BY E.Name, E.Status_fk, E.Marital_fk

In the above query, @JoinDate and @CurrentDate are passed in parameters, the above query is working perfectly within sql server 2005 but not with the web-app...

Thanks...

Re: Web-App waiting for indefinite time

Try this

Declare @TotalHoursWorked int

Declare @TotalHoursAbsent int

Declare @AbsentDifference int

Set @TotalHoursWorked = (SELECT  SUM (P.HoursWorked) FROM  Presence P
                 WHERE  P.EmpID_fk = E.EmpID
                 AND  P.DateWorked Between @JoinDate AND @CurrentDate)

Set @TotalHoursAbsent = (SELECT  SUM (P.HoursAbsent)
                FROM  Presence P
                WHERE  P.EmpID_fk = E.EmpID
                AND  P.DateWorked Between @JoinDate AND @CurrentDate)

Set @AbsentDiffrence = (SELECT  SUM (P.HoursAbsent) - SUM (P.HoursAbsent)
                FROM  Presence P
                WHERE  P.EmpID_fk = E.EmpID
                AND  (P.DateWorked Between @JoinDate AND @CurrentDate)

Finally have this select query

SELECT E.EmpID, E.Name, E.Status_fk, E.Marital_fk, E.Address, @TotalHoursWorked as TotalHoursWorked, @TotalHoursAbsent as TotalHoursAbsent, @AbsentDiffrence as AbsentDiffrence

FROM Employee E

INNER JOIN EmployeeStatus ES

ON E.Status_fk = ES.Status_ID

INNER JOIN Marital M

ON E.Marital_fk = M.Marital_ID

INNER JOIN Presence P

ON E.EmpID = P.EmpID_fk

WHERE (P.DateWorked Between @JoinDate AND @CurrentDate)

AND (E.Status_fk > 5)

ORDER BY E.Name, E.Status_fk, E.Marital_fk

Idea is to remove the subqueries in the table.

Re: Web-App waiting for indefinite time

There might be an issue with your asp.net code. Is it possible for you to paste the code where you are calling this SP?

-Vivek

Re: Web-App waiting for indefinite time

Hi Abhishek,

Sorry for late reply and once again thanks for the response. Well actually now the above mentioned query has got a problem. Where we declare our variable @TotalHoursWorked etc for the sub-query, it comes with an error telling that E.EmpID is not recognizable since its not aware of what E is. Its only down in the query where we define E as Employee.

Vivik, the code is stright forward and i use it at several places. For your info, its something like:

DataSet ds = new DataSet ();

SqlConnection conn = Initialize it...

SqlCommand cmd = new SqlCommand ();

cmd.CommandType = SqlCommandType.StoredProcedure;

cmd.CommandText = "sp_MyStoredProcedure";

cmd.Parameters.Add (new SqlParameter ("Name1", Type, Length).Value = parameter1

cmd.Parameters.Add (new SqlParameter ("Name2", Type, Length).Value = parameter2

SqlDataAdapter da = new SqlDataAdapter (cmd);

da.Fill (ds);

The above is just stright forward code and requires no explanation. I have used it so many times. Either there is something wrong with the query or something in the whole process is not right...

Thanks for your time...

Re: Web-App waiting for indefinite time

Also I have tested the parameters (which i pass from my application) so many times and they work just fine...

Re: Web-App waiting for indefinite time

Hi,

I forgot the Employee table join while popluating the variables, include them and the obove query will be fine, try this and i am sure there wont be problem in executing the stored procedure

Re: Web-App waiting for indefinite time

Hi Abhishek,

Well i got the join in the variables but now the problem is that although the result set (number of rows) is correct but the calculated fields are equal for all the employees e.g. for @TotalHoursWorked the SUM is equal for all employees. Let us see at one of the query after editing:

Set @TotalHoursWorked = (SELECT SUM (P.HoursWorked) FROM Presence P

INNER JOIN Employee E ON P.EmpID_fk = E.EmpID // newly added join

WHERE P.EmpID_fk = E.EmpID

AND P.DateWorked Between @JoinDate AND @CurrentDate)

In the above case, the SUM is being calculated from the entire table. Thus the results returned are euqal for all the employees.

What should be done to compute values for individual employees. By the way, i see a dramatic improvement in the result set being returned within SQL Server. From 6 seconds it went to 1 second, cool...

Thanks for your patience...

Re: Web-App waiting for indefinite time

use group by on employees

Re: Web-App waiting for indefinite time

Create 3 table variables for each @TotalHoursWorked etc. having columns EmpID and the TotalHoursWorked and insert like this

Insert into @TotalHoursWorked

Values(SELECT EmpID, SUM (P.HoursWorked) FROM Presence P

INNER JOIN Employee E ON P.EmpID_fk = E.EmpID // newly added join

WHERE P.EmpID_fk = E.EmpID

AND P.DateWorked Between @JoinDate AND @CurrentDate

group By E.EmpID)

This will give the Employees and thier Sum

and then in the main query you join with these tables on empid to get the result.

Re: Web-App waiting for indefinite time

Let me give it a try, thanks alot for your help and patience, im obliged...

Re: Web-App waiting for indefinite time

Hi Abhishek,

Awesom, my problem got solved. I used table variables and Joined those tables with my query and that gave me my result with exactly the same Parameters and Where Clause. Also the performance was great. I cant believe it. Greate job Abhishek. Really wonder job done. Thumbs upto you and i specially admire your patience in this whole process.

I also learnt a wonder technique of writing better queries with SQL Server. I still have my point unanswered as why not the same query (leaving apart the table variables) didnt work at the first place. Can you guess what could be going wrong without the table variables. Also why the application hung indefintely ???

Anyways, once again thanks alot for your time and patience. Wonderful job done man...

Re: Web-App waiting for indefinite time

Thanks for the same By the way u know me I am Abhishek(aptech)