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.
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...
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.
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).
(
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...
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.
Marked as answer by Kevin Yu - MSFT on Aug 03, 2007 08:21 AM
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...
SqlCommandcmd = 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...
kashifdotnet
Participant
765 Points
451 Posts
Web-App waiting for indefinite time
Aug 01, 2007 04:37 AM|LINK
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...
vivek_iit
All-Star
17778 Points
3189 Posts
MVP
Re: Web-App waiting for indefinite time
Aug 01, 2007 06:31 AM|LINK
What is the size of the data being returned by your query? Is the DB on the same machine as the web app?
-Vivek
Communifire: Social Networking and Business Collaboration Platform
kashifdotnet
Participant
765 Points
451 Posts
Re: Web-App waiting for indefinite time
Aug 01, 2007 07:12 AM|LINK
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...
abhishek2981
Member
464 Points
92 Posts
Re: Web-App waiting for indefinite time
Aug 02, 2007 05:22 AM|LINK
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.
kashifdotnet
Participant
765 Points
451 Posts
Re: Web-App waiting for indefinite time
Aug 02, 2007 06:51 AM|LINK
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...
abhishek2981
Member
464 Points
92 Posts
Re: Web-App waiting for indefinite time
Aug 02, 2007 08:21 AM|LINK
Paste me the whole stored procedure and le me have a look into that
kashifdotnet
Participant
765 Points
451 Posts
Re: Web-App waiting for indefinite time
Aug 02, 2007 09:52 AM|LINK
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...
abhishek2981
Member
464 Points
92 Posts
Re: Web-App waiting for indefinite time
Aug 02, 2007 11:06 AM|LINK
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.
vivek_iit
All-Star
17778 Points
3189 Posts
MVP
Re: Web-App waiting for indefinite time
Aug 03, 2007 09:55 AM|LINK
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
Communifire: Social Networking and Business Collaboration Platform
kashifdotnet
Participant
765 Points
451 Posts
Re: Web-App waiting for indefinite time
Aug 06, 2007 03:46 AM|LINK
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...