提高包含子查询的视图的性能 [英] Improve performance of view which contains sub query

问题描述

SELECT     dbo.EmployeeMaster.Employee_Id, dbo.EmployeeMaster.Emp_Id, dbo.EmployeeMaster.Emp_Name, FIRSTIN.Shift_Id, 
                      FIRSTIN.EmpPresent_EntryDate, FIRSTIN.EmpPresent_InTime AS FirstInTime, LASTOUT.EmpPresent_OutTime AS LastOutTime, 
                      FIRSTIN.EmpPresent_EmpInTime AS Emp_FirstInTime, LASTOUT.EmpPresent_EmpOutTime AS Emp_LastOutTime, 
                        FIRSTIN.Status , FIRSTIN.EmpPresent_Permission, 
                      FIRSTIN.EmpPresent_Remarks AS FirstRemark, FIRSTIN.EmpPresent_permission_by, FIRSTIN.Remark_for_permission, 
                      dbo.EmployeeMaster.Emp_Category_Id, FIRSTIN.EmpPresent_Id, dbo.EmployeeMaster.Emp_ConId, dbo.EmployeeMaster.Emp_ConName, 
                      dbo.EmployeeMaster.OTAllow
FROM         dbo.EmployeeMaster INNER JOIN
                      dbo.EmpPresentDetail AS FIRSTIN ON dbo.EmployeeMaster.Employee_Id = FIRSTIN.Employee_Id INNER JOIN
                      dbo.EmpPresentDetail AS LASTOUT ON dbo.EmployeeMaster.Employee_Id = LASTOUT.Employee_Id AND 
                      FIRSTIN.EmpPresent_EntryDate = LASTOUT.EmpPresent_EntryDate
WHERE     (FIRSTIN.EmpPresent_Id =
                          (SELECT     MIN(EmpPresent_Id) AS Expr1
                            FROM          dbo.EmpPresentDetail
                            WHERE      (Employee_Id = FIRSTIN.Employee_Id) AND (EmpPresent_EntryDate = FIRSTIN.EmpPresent_EntryDate) )  ) AND 
                      (LASTOUT.EmpPresent_Id =
                          (SELECT     MAX(EmpPresent_Id) AS Expr1
                            FROM          dbo.EmpPresentDetail AS EmpPresentDetail_2
                            WHERE      (Employee_Id = LASTOUT.Employee_Id) AND (EmpPresent_EntryDate = LASTOUT.EmpPresent_EntryDate) AND 
                                                   (EmpPresent_EmpOutTime IS NULL OR
                                                   EmpPresent_EmpOutTime =
                                                       (SELECT     MAX(EmpPresent_EmpOutTime) AS Expr1
                                                         FROM          dbo.EmpPresentDetail AS EmpPresentDetail_1
                                                         WHERE      (Employee_Id = LASTOUT.Employee_Id) AND (EmpPresent_EntryDate = LASTOUT.EmpPresent_EntryDate)))))

我想仅使用不包含subquery的内部联接进行此查询joinnings ...

让我知道是否需要进一步的帮助.

问候

SELECT     dbo.EmployeeMaster.Employee_Id, dbo.EmployeeMaster.Emp_Id, dbo.EmployeeMaster.Emp_Name, FIRSTIN.Shift_Id, 
                      FIRSTIN.EmpPresent_EntryDate, FIRSTIN.EmpPresent_InTime AS FirstInTime, LASTOUT.EmpPresent_OutTime AS LastOutTime, 
                      FIRSTIN.EmpPresent_EmpInTime AS Emp_FirstInTime, LASTOUT.EmpPresent_EmpOutTime AS Emp_LastOutTime, 
                        FIRSTIN.Status , FIRSTIN.EmpPresent_Permission, 
                      FIRSTIN.EmpPresent_Remarks AS FirstRemark, FIRSTIN.EmpPresent_permission_by, FIRSTIN.Remark_for_permission, 
                      dbo.EmployeeMaster.Emp_Category_Id, FIRSTIN.EmpPresent_Id, dbo.EmployeeMaster.Emp_ConId, dbo.EmployeeMaster.Emp_ConName, 
                      dbo.EmployeeMaster.OTAllow
FROM         dbo.EmployeeMaster INNER JOIN
                      dbo.EmpPresentDetail AS FIRSTIN ON dbo.EmployeeMaster.Employee_Id = FIRSTIN.Employee_Id INNER JOIN
                      dbo.EmpPresentDetail AS LASTOUT ON dbo.EmployeeMaster.Employee_Id = LASTOUT.Employee_Id AND 
                      FIRSTIN.EmpPresent_EntryDate = LASTOUT.EmpPresent_EntryDate
WHERE     (FIRSTIN.EmpPresent_Id =
                          (SELECT     MIN(EmpPresent_Id) AS Expr1
                            FROM          dbo.EmpPresentDetail
                            WHERE      (Employee_Id = FIRSTIN.Employee_Id) AND (EmpPresent_EntryDate = FIRSTIN.EmpPresent_EntryDate) )  ) AND 
                      (LASTOUT.EmpPresent_Id =
                          (SELECT     MAX(EmpPresent_Id) AS Expr1
                            FROM          dbo.EmpPresentDetail AS EmpPresentDetail_2
                            WHERE      (Employee_Id = LASTOUT.Employee_Id) AND (EmpPresent_EntryDate = LASTOUT.EmpPresent_EntryDate) AND 
                                                   (EmpPresent_EmpOutTime IS NULL OR
                                                   EmpPresent_EmpOutTime =
                                                       (SELECT     MAX(EmpPresent_EmpOutTime) AS Expr1
                                                         FROM          dbo.EmpPresentDetail AS EmpPresentDetail_1
                                                         WHERE      (Employee_Id = LASTOUT.Employee_Id) AND (EmpPresent_EntryDate = LASTOUT.EmpPresent_EntryDate)))))

I Want to This Query using Only Inner join which not contains subquery

解决方案

Create CTEs which calculate the max/min grouped on the columns in where clause and use those CTEs with joinnings...

Let me know if further assistance is required.

Regards

这篇关于提高包含子查询的视图的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！