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
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 = (SELECTSUM (P.HoursWorked) FROMPresence P
INNER JOIN Employee E ON P.EmpID_fk = E.EmpID // newly added join
WHEREP.EmpID_fk = E.EmpID
ANDP.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...
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...
kashifdotnet
Participant
765 Points
451 Posts
Re: Web-App waiting for indefinite time
Aug 06, 2007 03:48 AM|LINK
Also I have tested the parameters (which i pass from my application) so many times and they work just fine...
abhishek2981
Member
464 Points
92 Posts
Re: Web-App waiting for indefinite time
Aug 06, 2007 05:26 AM|LINK
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
kashifdotnet
Participant
765 Points
451 Posts
Re: Web-App waiting for indefinite time
Aug 06, 2007 07:08 AM|LINK
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...
abhishek2981
Member
464 Points
92 Posts
Re: Web-App waiting for indefinite time
Aug 06, 2007 07:16 AM|LINK
use group by on employees
abhishek2981
Member
464 Points
92 Posts
Re: Web-App waiting for indefinite time
Aug 06, 2007 07:38 AM|LINK
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 PINNER 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.
kashifdotnet
Participant
765 Points
451 Posts
Re: Web-App waiting for indefinite time
Aug 06, 2007 07:45 AM|LINK
Let me give it a try, thanks alot for your help and patience, im obliged...
kashifdotnet
Participant
765 Points
451 Posts
Re: Web-App waiting for indefinite time
Aug 07, 2007 08:03 AM|LINK
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...
abhishek2981
Member
464 Points
92 Posts
Re: Web-App waiting for indefinite time
Aug 07, 2007 08:39 AM|LINK
Thanks for the same By the way u know me I am Abhishek(aptech)