Solved

Query Cleanup - How to get advice

Posted on 2014-03-11
17
277 Views
Last Modified: 2014-03-16
I use sql server 2008 database.

What can I do in sql server management studio to clean up a query? look at it for problems. Get advice. Find problems?

1. I've used display estimated execution plan and that means nothing helpful to me.

2. I don't know how to use the SQL Server Profiler to trace the query. It asks me for a template and then that's as far as I get. "Help" doesn't help because it says to use the templates provided and there are none.

3. My client statistics reads:
client statistics

4. My Execution plan reads:
Query Cost: 100%

I don't know what that means.

5. I don't know how to use "Analyze Query in Database Engine Tuning Advisor." I get over there create a new connection,  I read the help file but that is more what it does and not how to do it.

6. Ignore all the above and please just tell me:
Does anyone have a link to step by step instructions for walking through and creating a something or other that will allow me to tune my query? I know the query I want to tune. I just need to know how to do it.

BTW, I use a query, not a stored procedure.

thanks!
0
Comment
Question by:Starr Duskk
  • 12
  • 3
  • 2
17 Comments
 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 250 total points
ID: 39921832
Can you post the execution plan and the query if there is nothing confidential in it?
In Estimated Execution Plan can you see any Missing index by any chance?
Also from the execution statistics other than large data returned by SQL I can't see anything really bad .
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 250 total points
ID: 39921833
To tune a query, one must look at the execution plan.

Since that's not yet meaningful to you, there's really no way now for you to be genuinely effective at tuning a query.

I'm sure there are web sites that have tips on understanding query plans.

Two quick-and-dirty tips, with an applicable caution, are:
1) look at any index SQL recommends -- caution: don't automatically create the index, as it may not be best for the system overall
2) table/index scans are generally bad which index seeks are generally good, but again, that's not 100% (for example, some index scans are partial, sequential scans and those can be perfectly acceptable, even desirable).
0
 
LVL 1

Author Comment

by:Starr Duskk
ID: 39921942
Here is the query. Do you want the execution plan as XML or a sqlplan file? thanks!

Select EmployeeId, EmployeeName,HireDate,EmploymentStatusCode,UnitName,sum(total1) + sum(total2) as Total,
sum(eligibleCount1) + sum(eligibleCount2) as eligibleCount, 
sum(failedCount1) + sum(failedcount2) as failedcount,
sum(assignedCount1) + sum(assignedCount2) as assignedCount,
sum(certifiedCount1) + sum(certifiedCount2) as certifiedCount,
sum(otherCount1) + sum(othercount2) as othercount,
CAST(CAST((sum(certifiedCount1) + sum(certifiedCount2)) / CAST((sum(total1) + sum(total2)) as decimal(6,0)) * 100 as decimal(6,2)) as varchar(10)) + '%' as certifiedPercent,
CAST(CAST((sum(assignedCount1) + sum(assignedCount2)) / CAST((sum(total1) + sum(total2)) as decimal(6,0)) * 100 as decimal(6,2)) as varchar(10)) + '%' as assignedPercent,
CAST(CAST((sum(failedCount1) + sum(failedCount2)) / CAST((sum(total1) + sum(total2)) as decimal(6,0)) * 100 as decimal(6,2)) as varchar(10)) + '%' as failedPercent,
CAST(CAST((sum(otherCount1) + sum(otherCount2)) / CAST((sum(total1) + sum(total2)) as decimal(6,0)) * 100 as decimal(6,2)) as varchar(10)) + '%' as otherPercent,
CAST(CAST((sum(eligibleCount1) + sum(eligibleCount2)) / CAST((sum(total1) + sum(total2)) as decimal(6,0)) * 100 as decimal(6,2)) as varchar(10)) + '%' as eligiblePercent 


from (
SELECT Employee.EmployeeId,LastName + ', ' + FirstName as EmployeeName,Employee.HireDate,Employee.EmploymentStatusCode,UnitName,COUNT(Employee.EmployeeId) as total1, 0 as total2,SUM(CASE ISNULL(EmployeeTrainingModuleAttempt.EmployeeTrainingModuleAttemptStatusCode,1535) WHEN 1535 THEN 1 ELSE 0  END) as eligibleCount1,SUM(CASE EmployeeTrainingModuleAttempt.EmployeeTrainingModuleAttemptStatusCode WHEN 1358 THEN 1 ELSE 0  END) as failedCount1,SUM(CASE EmployeeTrainingModuleAttempt.EmployeeTrainingModuleAttemptStatusCode WHEN 1356 THEN 1 ELSE 0  END) as assignedCount1,SUM(CASE EmployeeTrainingModuleAttempt.EmployeeTrainingModuleAttemptStatusCode WHEN 1357 THEN 1 ELSE 0  END) as certifiedCount1,SUM(CASE  WHEN EmployeeTrainingModuleAttempt.EmployeeTrainingModuleAttemptStatusCode IN (1473,1385,1386,1355,1412,1411,1359) THEN 1 ELSE 0  END) as otherCount1,0 as eligibleCount2, 0 as failedcount2, 0 as assignedCount2, 0 as certifiedCount2, 0 as otherCount2 from Employee  INNER JOIN UserProfile on UserProfile.UserProfileId = Employee.UserProfileId  
 INNER JOIN UserRole on UserRole.UserProfileId = UserProfile.UserProfileID 
 INNER JOIN Client on Client.ClientId = Employee.ClientId 
 INNER JOIN TrainingCourse on TrainingCourse.ClientId = Employee.ClientId 
 INNER JOIN TrainingCourseWorkshopModule on TrainingCourseWorkshopModule.TrainingCourseId = TrainingCourse.TrainingCourseId 
 INNER JOIN TrainingWorkshop on TrainingWorkshop.TrainingWorkshopId = TrainingCourseWorkshopModule.TrainingWorkshopId  
 INNER JOIN TrainingWorkShopTrainingModule on TrainingWorkShopTrainingModule.TrainingWorkshopId = TrainingCourseWorkshopModule.TrainingWorkshopId  
 INNER JOIN TrainingModule on TrainingModule.TrainingModuleId = TrainingWorkshopTrainingModule.TrainingModuleId  
 INNER JOIN ClientTrainingModuleSelector on ClientTrainingModuleSelector.TrainingModuleId = TrainingModule.TrainingModuleId   and ClientTrainingModuleSelector.ClientId = TrainingCourse.ClientId  

 LEFT OUTER JOIN EmployeeTrainingModuleAttempt ON  
   EmployeeTrainingModuleAttempt.ClientTrainingModuleSelectorId = ClientTrainingModuleSelector.ClientTrainingModuleSelectorId  
   AND EmployeeTrainingModuleAttempt.EmployeeId = Employee.EmployeeId
   AND EmployeeTrainingModuleAttempt.EmployeeTrainingModuleAttemptID IN 
   (select max(EmployeeTrainingModuleAttemptID) as EmployeeTrainingModuleAttemptID from EmployeeTrainingModuleAttempt  
   WHERE EmployeeTrainingModuleAttempt.EmployeeId = Employee.EmployeeId
    group by TrainingModuleId)  
 LEFT OUTER JOIN (SELECT *, ROW_NUMBER() OVER (PARTITION BY EmployeeId ORDER BY EmployeeId) rn FROM EmployeeUnitJobTypeAllView) EmployeeUnitJobTypeAllView ON EmployeeUnitJobTypeAllView.EmployeeId = Employee.EmployeeId AND rn = 1  
 INNER JOIN JobType on EmployeeUnitJobTypeAllView.JobTypeId = JobType.JobTypeId 
 INNER JOIN Department on Department.DepartmentId = JobType.DepartmentId 
 INNER JOIN Unit on Unit.UnitId = EmployeeUnitJobTypeAllView.UnitId 
  WHERE 
 UserProfile.UserStatusCode = 5  

 AND 
 Employee.ClientId = 18  
 AND 
 (TrainingCourse.BeginDate is null or TrainingCourse.BeginDate >= '3/11/2014 12:00:00 AM') 
 AND 
 (TrainingCourse.ExpireDate is null or TrainingCourse.ExpireDate <= '3/12/2014 12:00:00 AM') 
 AND 
 ( 
TrainingCourse.TrainingCourseId IN (Select TrainingCourseId from TrainingCourseUnit WHERE UnitId IN (Select UnitId from EmployeeUnitJobTypeUnitView where EmployeeId = Employee.EmployeeId))
 OR 
TrainingCourse.TrainingCourseId NOT IN (Select TrainingCourseId from TrainingCourseUnit)
 ) 
 AND 
 ( 
TrainingCourse.TrainingCourseId IN (Select TrainingCourseId from TrainingCourseJobType WHERE JobTypeId IN (Select Distinct JobTypeId from EmployeeUnitJobTypeIdView where EmployeeUnitJobTypeIdView.EmployeeId = Employee.EmployeeId) )
 OR 
TrainingCourse.TrainingCourseId NOT IN (Select TrainingCourseId from TrainingCourseJobType)
 ) 
 AND 
 ( 
TrainingCourse.TrainingCourseId IN (Select TrainingCourseId from TrainingCourseUserRole WHERE UserRoleCode = UserRole.UserRoleCode) 
 OR 
TrainingCourse.TrainingCourseId NOT IN (Select TrainingCourseId from TrainingCourseUserRole)
 ) 
 AND 
 ( 
TrainingCourse.TrainingCourseId IN (Select TrainingCourseId from TrainingCourseZone WHERE ZoneId = EmployeeUnitJobTypeAllView.ZoneId) 
 OR 
TrainingCourse.TrainingCourseId NOT IN (Select TrainingCourseId from TrainingCourseZone)
 ) 
 AND 
 ( 
TrainingCourse.TrainingCourseId IN (Select TrainingCourseId from TrainingCourseDistrict WHERE DistrictId = EmployeeUnitJobTypeAllView.DistrictId) 
 OR 
TrainingCourse.TrainingCourseId NOT IN (Select TrainingCourseId from TrainingCourseDistrict)
 ) 
 AND 
 ( 
TrainingCourse.TrainingCourseId IN  (Select TrainingCourseId from TrainingCourseState WHERE StateCode IN 
 (Select StateCode From Unit Where UnitID IN (Select UnitID From EmployeeUnitJobTypeUnitView where EmployeeId = Employee.Employeeid))) 
 OR 
TrainingCourse.TrainingCourseId NOT IN (Select TrainingCourseId from TrainingCourseState)
 ) 
 AND 
 ( 
TrainingCourse.TrainingCourseId IN (Select TrainingCourseId from TrainingCourseUnitOption WHERE UnitOptionCode IN 
 (Select UnitOptionCode From UnitUnitOption Where UnitID IN (Select UnitID From EmployeeUnitJobTypeUnitView where EmployeeId = Employee.Employeeid))) 
 OR 
TrainingCourse.TrainingCourseId NOT IN (Select TrainingCourseId from TrainingCourseUnitOption)
 ) 
 AND 
 (ClientTrainingModuleSelector.BeginDate is null or ClientTrainingModuleSelector.BeginDate >= '3/11/2014 12:00:00 AM') 
 AND 
 (ClientTrainingModuleSelector.ExpireDate is null or ClientTrainingModuleSelector.ExpireDate <= '3/12/2014 12:00:00 AM') 
 AND 
 ( 
ClientTrainingModuleSelector.ClientTrainingModuleSelectorId IN (Select ClientTrainingModuleSelectorId from ClientTrainingModuleSelectorUnit WHERE UnitId IN (Select UnitId from EmployeeUnitJobTypeUnitView where EmployeeId = Employee.EmployeeId))
 OR 
ClientTrainingModuleSelector.ClientTrainingModuleSelectorId NOT IN (Select ClientTrainingModuleSelectorId from ClientTrainingModuleSelectorUnit)
 ) 
 AND 
 ( 
ClientTrainingModuleSelector.ClientTrainingModuleSelectorId IN (Select ClientTrainingModuleSelectorId from ClientTrainingModuleSelectorJobType WHERE JobTypeId  IN (Select Distinct JobTypeId from EmployeeUnitJobTypeIdView where EmployeeUnitJobTypeIdView.EmployeeId = Employee.EmployeeId) )
 OR 
ClientTrainingModuleSelector.ClientTrainingModuleSelectorId NOT IN (Select ClientTrainingModuleSelectorId from ClientTrainingModuleSelectorJobType)
 ) 
 AND 
 ( 
ClientTrainingModuleSelector.ClientTrainingModuleSelectorId IN (Select ClientTrainingModuleSelectorId from ClientTrainingModuleSelectorUserRole WHERE UserRoleCode = UserRole.UserRoleCode) 
 OR 
ClientTrainingModuleSelector.ClientTrainingModuleSelectorId NOT IN (Select ClientTrainingModuleSelectorId from ClientTrainingModuleSelectorUserRole)
 ) 
 AND 
 ( 
ClientTrainingModuleSelector.ClientTrainingModuleSelectorId IN (Select ClientTrainingModuleSelectorId from ClientTrainingModuleSelectorZone WHERE ZoneId = EmployeeUnitJobTypeAllView.ZoneId) 
 OR 
ClientTrainingModuleSelector.ClientTrainingModuleSelectorId NOT IN (Select ClientTrainingModuleSelectorId from ClientTrainingModuleSelectorZone)
 ) 
 AND 
 ( 
ClientTrainingModuleSelector.ClientTrainingModuleSelectorId IN (Select ClientTrainingModuleSelectorId from ClientTrainingModuleSelectorDistrict WHERE DistrictId = EmployeeUnitJobTypeAllView.DistrictId) 
 OR 
ClientTrainingModuleSelector.ClientTrainingModuleSelectorId NOT IN (Select ClientTrainingModuleSelectorId from ClientTrainingModuleSelectorDistrict)
 ) 
 AND 
 ( 
ClientTrainingModuleSelector.ClientTrainingModuleSelectorId IN  (Select ClientTrainingModuleSelectorId from ClientTrainingModuleSelectorState WHERE StateCode IN 
 (Select StateCode From Unit Where UnitID IN (Select UnitID From EmployeeUnitJobTypeUnitView where EmployeeId = Employee.Employeeid))) 
 OR 
ClientTrainingModuleSelector.ClientTrainingModuleSelectorId NOT IN (Select ClientTrainingModuleSelectorId from ClientTrainingModuleSelectorState)
 ) 
 AND 
 ( 
ClientTrainingModuleSelector.ClientTrainingModuleSelectorId IN (Select ClientTrainingModuleSelectorId from ClientTrainingModuleSelectorUnitOption WHERE UnitOptionCode IN 
 (Select UnitOptionCode From UnitUnitOption Where UnitID IN (Select UnitID From EmployeeUnitJobTypeUnitView where EmployeeId = Employee.Employeeid))) 
 OR 
ClientTrainingModuleSelector.ClientTrainingModuleSelectorId NOT IN (Select ClientTrainingModuleSelectorId from ClientTrainingModuleSelectorUnitOption)
 ) 
 AND 
 ( 
 (TrainingModule.TrainingModuleStatusCode = 1258  
 OR TrainingModule.TrainingModuleStatusCode IS NULL)  
 AND (TrainingCourse.TrainingCourseStatusCode = 1375  
 OR TrainingCourse.TrainingCourseStatusCode IS NULL)  
  AND  (TrainingWorkshop.TrainingWorkshopStatusCode = 1300  
 OR TrainingWorkshop.TrainingWorkshopStatusCode IS NULL)  
 AND ClientTrainingModuleSelector.ClientTrainingModuleSelectorStatusCode = 1377  
 ) 
 AND 
 (TrainingWorkshop.BeginDate is null or TrainingWorkshop.BeginDate >= '3/11/2014 12:00:00 AM') 
 AND 
 (TrainingWorkshop.ExpireDate is null or TrainingWorkshop.ExpireDate <= '3/12/2014 12:00:00 AM') 
 AND 
 ( 
TrainingWorkshop.TrainingWorkshopId IN (Select TrainingWorkshopId from TrainingWorkshopUnit WHERE UnitId IN (Select UnitId from EmployeeUnitJobTypeUnitView where EmployeeId = Employee.EmployeeId))
 OR 
TrainingWorkshop.TrainingWorkshopId NOT IN (Select TrainingWorkshopId from TrainingWorkshopUnit)
 ) 
 AND 
 ( 
TrainingWorkshop.TrainingWorkshopId IN (Select TrainingWorkshopId from TrainingWorkshopJobType WHERE JobTypeId IN (Select Distinct JobTypeId from EmployeeUnitJobTypeIdView where EmployeeUnitJobTypeIdView.EmployeeId = Employee.EmployeeId) )
 OR 
TrainingWorkshop.TrainingWorkshopId NOT IN (Select TrainingWorkshopId from TrainingWorkshopJobType)
 ) 
 AND 
 ( 
TrainingWorkshop.TrainingWorkshopId IN (Select TrainingWorkshopId from TrainingWorkshopUserRole WHERE UserRoleCode = UserRole.UserRoleCode) 
 OR 
TrainingWorkshop.TrainingWorkshopId NOT IN (Select TrainingWorkshopId from TrainingWorkshopUserRole)
 ) 
 AND 
 ( 
TrainingWorkshop.TrainingWorkshopId IN (Select TrainingWorkshopId from TrainingWorkshopZone WHERE ZoneId = EmployeeUnitJobTypeAllView.ZoneId) 
 OR 
TrainingWorkshop.TrainingWorkshopId NOT IN (Select TrainingWorkshopId from TrainingWorkshopZone)
 ) 
 AND 
 ( 
TrainingWorkshop.TrainingWorkshopId IN (Select TrainingWorkshopId from TrainingWorkshopDistrict WHERE DistrictId = EmployeeUnitJobTypeAllView.DistrictId) 
 OR 
TrainingWorkshop.TrainingWorkshopId NOT IN (Select TrainingWorkshopId from TrainingWorkshopDistrict)
 ) 
 AND 
 ( 
TrainingWorkshop.TrainingWorkshopId IN  (Select TrainingWorkshopId from TrainingWorkshopState WHERE StateCode IN 
 (Select StateCode From Unit Where UnitID IN (Select UnitID From EmployeeUnitJobTypeUnitView where EmployeeId = Employee.Employeeid))) 
 OR 
TrainingWorkshop.TrainingWorkshopId NOT IN (Select TrainingWorkshopId from TrainingWorkshopState)
 ) 
 AND 
 ( 
TrainingWorkshop.TrainingWorkshopId IN (Select TrainingWorkshopId from TrainingWorkshopUnitOption WHERE UnitOptionCode IN 
 (Select UnitOptionCode From UnitUnitOption Where UnitID IN (Select UnitID From EmployeeUnitJobTypeUnitView where EmployeeId = Employee.Employeeid))) 
 OR 
TrainingWorkshop.TrainingWorkshopId NOT IN (Select TrainingWorkshopId from TrainingWorkshopUnitOption)
 ) 

Group by Employee.EmployeeId,Employee.HireDate,Employee.EmploymentStatusCode,Unit.UnitName, UserProfile.LastName, UserProfile.Firstname,EmployeeTrainingModuleAttempt.EmployeeTrainingModuleAttemptStatusCode
  UNION ALL 
SELECT Employee.EmployeeId,LastName + ', ' + FirstName as EmployeeName,Employee.HireDate,Employee.EmploymentStatusCode,UnitName,0 as total1, COUNT(Employee.EmployeeId) as total2,0 as eligibleCount1, 0 as failedcount1, 0 as assignedCount1, 0 as certifiedCount1, 0 as otherCount1,SUM(CASE ISNULL(EmployeeTrainingModuleAttempt.EmployeeTrainingModuleAttemptStatusCode,1535) WHEN 1535 THEN 1 ELSE 0  END) as eligibleCount2,SUM(CASE EmployeeTrainingModuleAttempt.EmployeeTrainingModuleAttemptStatusCode WHEN 1358 THEN 1 ELSE 0  END) as failedCount2,SUM(CASE EmployeeTrainingModuleAttempt.EmployeeTrainingModuleAttemptStatusCode WHEN 1356 THEN 1 ELSE 0  END) as assignedCount2,SUM(CASE EmployeeTrainingModuleAttempt.EmployeeTrainingModuleAttemptStatusCode WHEN 1357 THEN 1 ELSE 0  END) as certifiedCount2,SUM(CASE  WHEN EmployeeTrainingModuleAttempt.EmployeeTrainingModuleAttemptStatusCode IN (1473,1385,1386,1355,1412,1411,1359) THEN 1 ELSE 0  END) as otherCount2 from Employee  INNER JOIN UserProfile on UserProfile.UserProfileId = Employee.UserProfileId  
 INNER JOIN UserRole on UserRole.UserProfileId = UserProfile.UserProfileID 
 INNER JOIN Client on Client.ClientId = Employee.ClientId 
 INNER JOIN TrainingCourse on TrainingCourse.ClientId = Employee.ClientId 
 INNER JOIN TrainingCourseWorkshopModule on TrainingCourseWorkshopModule.TrainingCourseId = TrainingCourse.TrainingCourseId 
 INNER JOIN TrainingModule on TrainingModule.TrainingModuleId = TrainingCourseWorkshopModule.TrainingModuleId  
 INNER JOIN ClientTrainingModuleSelector on ClientTrainingModuleSelector.TrainingModuleId = TrainingCourseWorkshopModule.TrainingModuleId   and ClientTrainingModuleSelector.ClientId = TrainingCourse.ClientId  

 LEFT OUTER JOIN EmployeeTrainingModuleAttempt ON  
   EmployeeTrainingModuleAttempt.ClientTrainingModuleSelectorId = ClientTrainingModuleSelector.ClientTrainingModuleSelectorId  
   AND EmployeeTrainingModuleAttempt.EmployeeId = Employee.EmployeeId
   AND EmployeeTrainingModuleAttempt.EmployeeTrainingModuleAttemptID IN 
   (select max(EmployeeTrainingModuleAttemptID) as EmployeeTrainingModuleAttemptID from EmployeeTrainingModuleAttempt  
   WHERE EmployeeTrainingModuleAttempt.EmployeeId = Employee.EmployeeId
    group by TrainingModuleId)  
 LEFT OUTER JOIN (SELECT *, ROW_NUMBER() OVER (PARTITION BY EmployeeId ORDER BY EmployeeId) rn FROM EmployeeUnitJobTypeAllView) EmployeeUnitJobTypeAllView ON EmployeeUnitJobTypeAllView.EmployeeId = Employee.EmployeeId AND rn = 1  
 INNER JOIN JobType on EmployeeUnitJobTypeAllView.JobTypeId = JobType.JobTypeId 
 INNER JOIN Department on Department.DepartmentId = JobType.DepartmentId 
 INNER JOIN Unit on Unit.UnitId = EmployeeUnitJobTypeAllView.UnitId 
  WHERE 
 UserProfile.UserStatusCode = 5  

 AND 
 Employee.ClientId = 18  
 AND 
 (TrainingCourse.BeginDate is null or TrainingCourse.BeginDate >= '3/11/2014 12:00:00 AM') 
 AND 
 (TrainingCourse.ExpireDate is null or TrainingCourse.ExpireDate <= '3/12/2014 12:00:00 AM') 
 AND 
 ( 
TrainingCourse.TrainingCourseId IN (Select TrainingCourseId from TrainingCourseUnit WHERE UnitId IN (Select UnitId from EmployeeUnitJobTypeUnitView where EmployeeId = Employee.EmployeeId))
 OR 
TrainingCourse.TrainingCourseId NOT IN (Select TrainingCourseId from TrainingCourseUnit)
 ) 
 AND 
 ( 
TrainingCourse.TrainingCourseId IN (Select TrainingCourseId from TrainingCourseJobType WHERE JobTypeId IN (Select Distinct JobTypeId from EmployeeUnitJobTypeIdView where EmployeeUnitJobTypeIdView.EmployeeId = Employee.EmployeeId) )
 OR 
TrainingCourse.TrainingCourseId NOT IN (Select TrainingCourseId from TrainingCourseJobType)
 ) 
 AND 
 ( 
TrainingCourse.TrainingCourseId IN (Select TrainingCourseId from TrainingCourseUserRole WHERE UserRoleCode = UserRole.UserRoleCode) 
 OR 
TrainingCourse.TrainingCourseId NOT IN (Select TrainingCourseId from TrainingCourseUserRole)
 ) 
 AND 
 ( 
TrainingCourse.TrainingCourseId IN (Select TrainingCourseId from TrainingCourseZone WHERE ZoneId = EmployeeUnitJobTypeAllView.ZoneId) 
 OR 
TrainingCourse.TrainingCourseId NOT IN (Select TrainingCourseId from TrainingCourseZone)
 ) 
 AND 
 ( 
TrainingCourse.TrainingCourseId IN (Select TrainingCourseId from TrainingCourseDistrict WHERE DistrictId = EmployeeUnitJobTypeAllView.DistrictId) 
 OR 
TrainingCourse.TrainingCourseId NOT IN (Select TrainingCourseId from TrainingCourseDistrict)
 ) 
 AND 
 ( 
TrainingCourse.TrainingCourseId IN  (Select TrainingCourseId from TrainingCourseState WHERE StateCode IN 
 (Select StateCode From Unit Where UnitID IN (Select UnitID From EmployeeUnitJobTypeUnitView where EmployeeId = Employee.Employeeid))) 
 OR 
TrainingCourse.TrainingCourseId NOT IN (Select TrainingCourseId from TrainingCourseState)
 ) 
 AND 
 ( 
TrainingCourse.TrainingCourseId IN (Select TrainingCourseId from TrainingCourseUnitOption WHERE UnitOptionCode IN 
 (Select UnitOptionCode From UnitUnitOption Where UnitID IN (Select UnitID From EmployeeUnitJobTypeUnitView where EmployeeId = Employee.Employeeid))) 
 OR 
TrainingCourse.TrainingCourseId NOT IN (Select TrainingCourseId from TrainingCourseUnitOption)
 ) 
 AND 
 (ClientTrainingModuleSelector.BeginDate is null or ClientTrainingModuleSelector.BeginDate >= '3/11/2014 12:00:00 AM') 
 AND 
 (ClientTrainingModuleSelector.ExpireDate is null or ClientTrainingModuleSelector.ExpireDate <= '3/12/2014 12:00:00 AM') 
 AND 
 ( 
ClientTrainingModuleSelector.ClientTrainingModuleSelectorId IN (Select ClientTrainingModuleSelectorId from ClientTrainingModuleSelectorUnit WHERE UnitId IN (Select UnitId from EmployeeUnitJobTypeUnitView where EmployeeId = Employee.EmployeeId))
 OR 
ClientTrainingModuleSelector.ClientTrainingModuleSelectorId NOT IN (Select ClientTrainingModuleSelectorId from ClientTrainingModuleSelectorUnit)
 ) 
 AND 
 ( 
ClientTrainingModuleSelector.ClientTrainingModuleSelectorId IN (Select ClientTrainingModuleSelectorId from ClientTrainingModuleSelectorJobType WHERE JobTypeId  IN (Select Distinct JobTypeId from EmployeeUnitJobTypeIdView where EmployeeUnitJobTypeIdView.EmployeeId = Employee.EmployeeId) )
 OR 
ClientTrainingModuleSelector.ClientTrainingModuleSelectorId NOT IN (Select ClientTrainingModuleSelectorId from ClientTrainingModuleSelectorJobType)
 ) 
 AND 
 ( 
ClientTrainingModuleSelector.ClientTrainingModuleSelectorId IN (Select ClientTrainingModuleSelectorId from ClientTrainingModuleSelectorUserRole WHERE UserRoleCode = UserRole.UserRoleCode) 
 OR 
ClientTrainingModuleSelector.ClientTrainingModuleSelectorId NOT IN (Select ClientTrainingModuleSelectorId from ClientTrainingModuleSelectorUserRole)
 ) 
 AND 
 ( 
ClientTrainingModuleSelector.ClientTrainingModuleSelectorId IN (Select ClientTrainingModuleSelectorId from ClientTrainingModuleSelectorZone WHERE ZoneId = EmployeeUnitJobTypeAllView.ZoneId) 
 OR 
ClientTrainingModuleSelector.ClientTrainingModuleSelectorId NOT IN (Select ClientTrainingModuleSelectorId from ClientTrainingModuleSelectorZone)
 ) 
 AND 
 ( 
ClientTrainingModuleSelector.ClientTrainingModuleSelectorId IN (Select ClientTrainingModuleSelectorId from ClientTrainingModuleSelectorDistrict WHERE DistrictId = EmployeeUnitJobTypeAllView.DistrictId) 
 OR 
ClientTrainingModuleSelector.ClientTrainingModuleSelectorId NOT IN (Select ClientTrainingModuleSelectorId from ClientTrainingModuleSelectorDistrict)
 ) 
 AND 
 ( 
ClientTrainingModuleSelector.ClientTrainingModuleSelectorId IN  (Select ClientTrainingModuleSelectorId from ClientTrainingModuleSelectorState WHERE StateCode IN 
 (Select StateCode From Unit Where UnitID IN (Select UnitID From EmployeeUnitJobTypeUnitView where EmployeeId = Employee.Employeeid))) 
 OR 
ClientTrainingModuleSelector.ClientTrainingModuleSelectorId NOT IN (Select ClientTrainingModuleSelectorId from ClientTrainingModuleSelectorState)
 ) 
 AND 
 ( 
ClientTrainingModuleSelector.ClientTrainingModuleSelectorId IN (Select ClientTrainingModuleSelectorId from ClientTrainingModuleSelectorUnitOption WHERE UnitOptionCode IN 
 (Select UnitOptionCode From UnitUnitOption Where UnitID IN (Select UnitID From EmployeeUnitJobTypeUnitView where EmployeeId = Employee.Employeeid))) 
 OR 
ClientTrainingModuleSelector.ClientTrainingModuleSelectorId NOT IN (Select ClientTrainingModuleSelectorId from ClientTrainingModuleSelectorUnitOption)
 ) 
 AND 
 ( 
 (TrainingModule.TrainingModuleStatusCode = 1258  
 OR TrainingModule.TrainingModuleStatusCode IS NULL)  
 AND (TrainingCourse.TrainingCourseStatusCode = 1375  
 OR TrainingCourse.TrainingCourseStatusCode IS NULL)  
 AND ClientTrainingModuleSelector.ClientTrainingModuleSelectorStatusCode = 1377  
 ) 

Group by Employee.EmployeeId,Employee.HireDate,Employee.EmploymentStatusCode,Unit.UnitName, UserProfile.LastName, UserProfile.Firstname,EmployeeTrainingModuleAttempt.EmployeeTrainingModuleAttemptStatusCode
 ) as table1
 group by EmployeeName,EmployeeId,HireDate,EmploymentStatusCode,UnitName
 Order By EmployeeName

Open in new window

0
 
LVL 1

Author Comment

by:Starr Duskk
ID: 39921966
Here is the execution plan as both xml and a sqlplan:
Execution-plan.zip
0
 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 250 total points
ID: 39922034
Wow...that's a pretty large query and therefor a pretty big query plan.
Before going to spend (lots of) time to optimize it - is this a "one timer" or something you need to run repeatedly and maybe on-line against OLTP database?
In my opinion the level of optimization depends on the answer to the above question and here are a few things I would do:
Try to combine any redundant checks like:

(TrainingModule.TrainingModuleStatusCode = 1258  
 OR TrainingModule.TrainingModuleStatusCode IS NULL)  
 AND (TrainingCourse.TrainingCourseStatusCode = 1375  
 OR TrainingCourse.TrainingCourseStatusCode IS NULL)  
 AND ClientTrainingModuleSelector.ClientTrainingModuleSelectorStatusCode = 1377  


to:

 TrainingModule.TrainingModuleStatusCode IN(1258,1375,NULL)  
  AND ClientTrainingModuleSelector.ClientTrainingModuleSelectorStatusCode = 1377  

Try to get rid of NOT IN by using NOT ESISTS,  LEFT JOIN or EXCEPT
Try to limit the record sets as much as possible prior to aggregate data together.
0
 
LVL 1

Author Comment

by:Starr Duskk
ID: 39922087
Okay that makes sense on the IN(1258,NULL)

Can you advise on how to rework this? I know how to JOIN a table if it's IN it, but not if it's NOT IN, so that's why I did it this way. Basically just the NOT IN part. I can do a JOIN on the IN part... I think.

AND 
 ( 
TrainingCourse.TrainingCourseId IN (Select TrainingCourseId from TrainingCourseUnit WHERE UnitId IN (Select UnitId from EmployeeUnitJobTypeUnitView where EmployeeId = Employee.EmployeeId))
 OR 
TrainingCourse.TrainingCourseId NOT IN (Select TrainingCourseId from TrainingCourseUnit)
 ) 

Open in new window


Thanks!
0
 
LVL 1

Author Comment

by:Starr Duskk
ID: 39922091
oh...
>>Before going to spend (lots of) time to optimize it - is this a "one timer" or something you need to run repeatedly and maybe on-line against OLTP database?

It is a repeated query where the values change based on the client, training, zones, districts units, etc. etc. etc. selected in a "search form" or "report setup". They can run it one way, save a different filter, run it again, and so it has variations dependent upon the selections they make. It is a monster. So I build it manually based on the saved settings and execute it to populate a grid.

thanks.
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 250 total points
ID: 39923567
A few points at first look:


At least one specific change comes out of the query plan:

INCLUDE column TrainingModuleId in index IX_EmployeeTrainingModuleAttemptEmployeeId


Would also need to see the code for all views, but particularly:
--EmployeeUnitJobTypeAllView
--EmployeeUnitJobTypeUnitView
and to see how the underlying tables are indexes.


Any monster GROUP BY:
"
Group by Employee.EmployeeId,Employee.HireDate,Employee.EmploymentStatusCode,Unit.UnitName, UserProfile.LastName, UserProfile.Firstname,EmployeeTrainingModuleAttempt.EmployeeTrainingModuleAttemptStatusCode
"
is almost always a red flag that something in the query isn't right.  Typically it means summaries are being done at too high a level.  Given that only ~58 rows are in the result set, it's not as big a deal here.
But the "eligibleCount1", etc., counts still need done in more specific subqueries, then joined back to the main query.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 1

Author Comment

by:Starr Duskk
ID: 39923936
I will start a new question for this request I gave. It should likely be a new question anyone so don't answer it here, since no one has. The one about reworking a NOT IN:
BobCSDPosted on 2014-03-11 at 17:06:28ID: 39922087
0
 
LVL 1

Author Comment

by:Starr Duskk
ID: 39923955
The new question for reworking a NOT IN query is now here:
http://www.experts-exchange.com/Database/MS-SQL-Server/Q_28386721.html
0
 
LVL 1

Author Comment

by:Starr Duskk
ID: 39923974
>>> Typically it means summaries are being done at too high a level.  Given that only ~58 rows are in the result set, it's not as big a deal here.
But the "eligibleCount1", etc., counts still need done in more specific subqueries, then joined back to the main query.

I don't understand this. What I have done is:

I have a query that returns rows to populate the grid. (that is the embedded query)

But then they wanted a summary grid that just shows the count of how many of each type of those rows are returned. So I still run the same query to get those rows, then I wrap it with the SUMs to get the totals an those are now displayed in the grid.

I don't understand the "need done in more specific subqueries then joined back"... I would still have to run the same internal query to get the totals wouldn't I?

do you have an example of what you mean? thanks!
0
 
LVL 1

Author Comment

by:Starr Duskk
ID: 39924853
Icohan,

I changed this:
>>TrainingModule.TrainingModuleStatusCode IN(1258,NULL)  

And got entirely different results.

select *, statuscode from mytable
left outer join on trainingmodule ...

Your suggestion returns only rows where the value in the trainingmodule actual table is null or 1258.

What I want is that on the JOIN, there is no relation to the trainingmodule table, so the statuscode is NULL because of no related data. It's null in the query results. My version finds those rows. Your version excludes those rows.

Weird. huh.
0
 
LVL 1

Author Comment

by:Starr Duskk
ID: 39924936
Scott,

You say:

>>At least one specific change comes out of the query plan:

>>INCLUDE column TrainingModuleId in index IX_EmployeeTrainingModuleAttemptEmployeeId

Wouldn't I create a separate index for TrainingModuleId?
Sometimes I search for all training by EmployeeId and don't take into consideration the TrainingModuleId at all.
0
 
LVL 1

Author Comment

by:Starr Duskk
ID: 39924941
EmployeeUnitJobTypeAllView

SELECT DISTINCT 
               dbo.EmployeeUnitJobType.EmployeeId, dbo.DistrictZone.ZoneId, dbo.UnitDistrict.DistrictId, dbo.EmployeeUnitJobType.UnitId, 
               dbo.EmployeeUnitJobType.JobTypeId, dbo.JobType.JobTypeName
FROM  dbo.EmployeeUnitJobType INNER JOIN
               dbo.UnitDistrict ON dbo.EmployeeUnitJobType.UnitId = dbo.UnitDistrict.UnitId INNER JOIN
               dbo.DistrictZone ON dbo.UnitDistrict.DistrictId = dbo.DistrictZone.DistrictId INNER JOIN
               dbo.JobType ON dbo.EmployeeUnitJobType.JobTypeId = dbo.JobType.JobTypeId

Open in new window

0
 
LVL 1

Author Comment

by:Starr Duskk
ID: 39924949
EmployeeUnitJobTypeUnitView

SELECT DISTINCT EmployeeId, UnitId
FROM  dbo.EmployeeUnitJobType
WHERE (UnitId IS NOT NULL)

Open in new window

0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39926372
The view definitions are helpful.

I guess there isn't a "dbo.EmployeeUnit" table for determining valid combinations of EmployeeId and UnitId?

Please run each of the views stand-alone and post the query plans for those.
0
 
LVL 1

Author Comment

by:Starr Duskk
ID: 39933097
Thanks everyone! I got it tuned by using EXISTS and NOT EXISTS instead of IN / NOT IN and stuff like that.

I'll close this out and thanks everyone!
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now