Solved

Query Cleanup - How to get advice

Posted on 2014-03-11
17
271 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:BobCSD
  • 12
  • 3
  • 2
17 Comments
 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 250 total points
Comment Utility
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:ScottPletcher
ScottPletcher earned 250 total points
Comment Utility
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:BobCSD
Comment Utility
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:BobCSD
Comment Utility
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
Comment Utility
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:BobCSD
Comment Utility
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:BobCSD
Comment Utility
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:
ScottPletcher earned 250 total points
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 1

Author Comment

by:BobCSD
Comment Utility
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:BobCSD
Comment Utility
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:BobCSD
Comment Utility
>>> 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:BobCSD
Comment Utility
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:BobCSD
Comment Utility
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:BobCSD
Comment Utility
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:BobCSD
Comment Utility
EmployeeUnitJobTypeUnitView

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

Open in new window

0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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:BobCSD
Comment Utility
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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

743 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

14 Experts available now in Live!

Get 1:1 Help Now