OPTION (RECOMPILE) and WITH (NOLOCK) for tuning

I have a query that takes from 2 to 7 minutes to run, depending on the specifics of the search criteria. It pulls from a lot of tables. I read in a forum post about using OPTION (RECOMPILE) and WITH (NOLOCK) to make queries faster.

I added WITH (NOLOCK) on every single JOIN.
I add OPTION (RECOMPILE) at the bottom of the query.
I ran it in sql server management studio and compared the speed to the query without those changes.
Exactly the same.

SQL Server 2012 version.

Why did nothing happen? Other people said it made theirs so much faster?
It would make sense it should have sped it up because of the NOLOCK, but it didn't.
Do I need a server setting? Maybe a server setting overrode it or something else?

thanks!
LVL 2
Starr DuskkASP.NET VB.NET DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
Just a thought ... posting your query into this question in a code block would help.
Steve WalesSenior Database AdministratorCommented:
While waiting for your query, I'm going to throw out a few things.

1) Are your statistics up to date?
2) Have you looked at the actual execution plan to see what it's doing?  Is it accessing data via expected indexes or performing full scans
3) OPTION (RECOMPILE) forces the optimizer to generate a new execution plan every time.  I have seen articles too where this "improves" a query but the circumstances from what I recall are sometimes out of the ordinary
4) WITH (NOLOCK) could be getting you dirty reads - use with caution.  Because of the way SQL Server handles isolation levels by default, readers can be blocked while the data is accessed elsewhere.  If data is involved in an update and you're trying to read it, NOLOCK means that the database engine will read data that may or may not be consistent (because it's involved in an in flight transaction).  Usually not recommended ...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
I'm not concerned about dirty reads with this system. Here is the query. And yes, it is crazy. They have relations and filters on everything. And this is only a piece of it. But enough for you to see what a nightmare it is.

SELECT District.DistrictId,District.DistrictName,COUNT(District.DistrictId) as total1, 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 WHEN EmployeeTrainingModuleAttempt.EmployeeTrainingModuleAttemptStatusCode IN (1357,1412,1411) THEN 1 ELSE 0 END) as certifiedCount1,SUM(CASE WHEN EmployeeTrainingModuleAttempt.EmployeeTrainingModuleAttemptStatusCode IN (1473,1385,1386,1355,1359) THEN 1 ELSE 0 END) as otherCount1 from Employee 

INNER JOIN UserProfile WITH (NOLOCK) on UserProfile.UserProfileId = Employee.UserProfileId 

INNER JOIN UserRole WITH (NOLOCK) on UserRole.UserProfileId = UserProfile.UserProfileID 

INNER JOIN Client WITH (NOLOCK) on Client.ClientId = Employee.ClientId 

INNER JOIN TrainingCourse WITH (NOLOCK) on TrainingCourse.ClientId = Employee.ClientId 

LEFT OUTER JOIN EmployeeTraining as etw WITH (NOLOCK) on etw.EmployeeId = Employee.EmployeeId and etw.TrainingTypeCode = 1728 and etw.TrainingId = TrainingCourse.TrainingCourseId 

INNER JOIN TrainingCourseWorkshopModule WITH (NOLOCK) on TrainingCourseWorkshopModule.TrainingCourseId = TrainingCourse.TrainingCourseId 

INNER JOIN TrainingWorkshop WITH (NOLOCK) on TrainingWorkshop.TrainingWorkshopId = TrainingCourseWorkshopModule.TrainingWorkshopId 

INNER JOIN TrainingWorkShopTrainingModule WITH (NOLOCK) on TrainingWorkShopTrainingModule.TrainingWorkshopId = TrainingCourseWorkshopModule.TrainingWorkshopId 

INNER JOIN TrainingModule WITH (NOLOCK) on TrainingModule.TrainingModuleId = TrainingWorkshopTrainingModule.TrainingModuleId 

INNER JOIN ClientTrainingModuleSelector WITH (NOLOCK) on ClientTrainingModuleSelector.TrainingModuleId = TrainingModule.TrainingModuleId and ClientTrainingModuleSelector.ClientId = TrainingCourse.ClientId 

LEFT OUTER JOIN EmployeeTrainingModuleAttempt WITH (NOLOCK) ON 

EmployeeTrainingModuleAttempt.ClientTrainingModuleSelectorId = ClientTrainingModuleSelector.ClientTrainingModuleSelectorId 

AND EmployeeTrainingModuleAttempt.EmployeeId = Employee.EmployeeId

AND EmployeeTrainingModuleAttempt.EmployeeTrainingModuleAttemptStatusCode NOT IN (1386,1411,1412) 

AND EmployeeTrainingModuleAttempt.EmployeeTrainingModuleAttemptID IN 

(select max(EmployeeTrainingModuleAttemptID) as EmployeeTrainingModuleAttemptID from EmployeeTrainingModuleAttempt 

WHERE EmployeeTrainingModuleAttempt.EmployeeId = Employee.EmployeeId

AND EmployeeTrainingModuleAttempt.archivedate IS NULL 

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 WITH (NOLOCK) on EmployeeUnitJobTypeAllView.JobTypeId = JobType.JobTypeId 

INNER JOIN Department WITH (NOLOCK) on Department.DepartmentId = JobType.DepartmentId 

LEFT OUTER JOIN Unit WITH (NOLOCK) on Unit.UnitId = EmployeeUnitJobTypeAllView.UnitId 

INNER JOIN District WITH (NOLOCK) on District.DistrictId = EmployeeUnitJobTypeAllView.DistrictId 

WHERE 

UserProfile.UserStatusCode = 5 

AND 

Employee.HireDate <= '04/11/15' 

AND 

Employee.ClientId = 22 

AND 

(etw.EmployeeTrainingStatusCode IN (1363,1365,1364) or etw.EmployeeTrainingStatusCode is null) 

AND 

( 

TrainingCourse.TrainingCourseStatusCode = 1375 

) 

AND 

( (TrainingCourse.LanguageCode = Employee.LanguageCode or TrainingCourse.LanguageCode is null) or EmployeeTrainingModuleAttempt.EmployeeTrainingModuleAttemptStatusCode = 1357) 

AND 

( 

(ISNULL(TrainingCourse.SearchOnBeforeAfterCode, 1863) = 1863 AND Employee.HireDate > TrainingCourse.SkipHireDate) 

OR 

(ISNULL(TrainingCourse.SearchOnBeforeAfterCode, 0) = 1864 AND Employee.HireDate < TrainingCourse.SkipHireDate) 

OR 

(ISNULL(TrainingCourse.SearchOnBeforeAfterCode, 0) = 1865 AND TrainingCourse.SkipHireDate <> Employee.HireDate) 

OR 

TrainingCourse.skiphiredate IS NULL 

) 

AND 

(TrainingCourse.BeginDate is null or TrainingCourse.BeginDate <= '6/10/2015 12:00:00 AM') 

AND 

(TrainingCourse.ExpireDate is null or TrainingCourse.ExpireDate >= '6/11/2015 12:00:00 AM') 

AND 

( 

EXISTS ( 

select 1 

from TrainingCourseUnit 

join EmployeeUnitJobTypeUnitView WITH (NOLOCK) 

on EmployeeUnitJobTypeUnitView.UnitID = TrainingCourseUnit.UnitID 

and EmployeeUnitJobTypeUnitView.EmployeeId = Employee.EmployeeId 

where TrainingCourse.TrainingCourseId = TrainingCourseUnit.TrainingCourseId 

) OR NOT EXISTS ( 

select 1 from TrainingCourseUnit 

where TrainingCourse.TrainingCourseId = TrainingCourseUnit.TrainingCourseId 

) 

) 

AND 

( 

EXISTS ( 

select 1 

from TrainingCourseJobType 

join EmployeeUnitJobTypeIdView WITH (NOLOCK) 

on EmployeeUnitJobTypeIdView.JobTypeId = TrainingCourseJobType.JobTypeId 

and EmployeeUnitJobTypeIdView.EmployeeId = Employee.EmployeeId 

where TrainingCourse.TrainingCourseId = TrainingCourseJobType.TrainingCourseId 

) OR NOT EXISTS ( 

select 1 from TrainingCourseJobType 

where TrainingCourse.TrainingCourseId = TrainingCourseJobType.TrainingCourseId 

) 

OR ( 

TrainingCourse.TrainingCourseId = EmployeeTrainingModuleAttempt.TrainingCourseId 

AND EmployeeTrainingModuleAttempt.EmployeeTrainingModuleAttemptStatusCode = 1357 

) 

) 

AND 

( 

EXISTS ( 

select 1 

from TrainingCourseUserRole 

where TrainingCourse.TrainingCourseId = TrainingCourseUserRole.TrainingCourseId 

and TrainingCourseUserRole.UserRoleCode = UserRole.UserRoleCode 

) OR NOT EXISTS ( 

select 1 from TrainingCourseUserRole 

where TrainingCourse.TrainingCourseId = TrainingCourseUserRole.TrainingCourseId 

) 

) 

AND 

( 

EXISTS ( 

select 1 

from TrainingCourseZone 

join EmployeeUnitJobTypeAllView WITH (NOLOCK) 

on EmployeeUnitJobTypeAllView.ZoneId = TrainingCourseZone.ZoneId 

and EmployeeUnitJobTypeAllView.EmployeeId = Employee.EmployeeId 

where TrainingCourse.TrainingCourseId = TrainingCourseZone.TrainingCourseId 

) OR NOT EXISTS ( 

select 1 from TrainingCourseZone 

where TrainingCourse.TrainingCourseId = TrainingCourseZone.TrainingCourseId 

) 

) 

AND 

( 

EXISTS ( 

select 1 

from TrainingCourseDistrict 

join EmployeeUnitJobTypeAllView WITH (NOLOCK) 

on EmployeeUnitJobTypeAllView.DistrictId = TrainingCourseDistrict.DistrictId 

and EmployeeUnitJobTypeAllView.EmployeeId = Employee.EmployeeId 

where TrainingCourse.TrainingCourseId = TrainingCourseDistrict.TrainingCourseId 

) OR NOT EXISTS ( 

select 1 from TrainingCourseDistrict 

where TrainingCourse.TrainingCourseId = TrainingCourseDistrict.TrainingCourseId 

) 

) 

AND 

( 

EXISTS ( 

select 1 

from TrainingCourseState 

join Unit WITH (NOLOCK) on Unit.StateCode = TrainingCourseState.StateCode 

join EmployeeUnitJobTypeUnitView WITH (NOLOCK) on EmployeeUnitJobTypeUnitView.UnitID = Unit.UnitID 

and EmployeeUnitJobTypeUnitView.EmployeeId = Employee.EmployeeId 

where TrainingCourse.TrainingCourseId = TrainingCourseState.TrainingCourseId 

) OR NOT EXISTS ( 

select 1 from TrainingCourseState 

where TrainingCourse.TrainingCourseId = TrainingCourseState.TrainingCourseId 

) 

) 

AND 

( 

EXISTS ( 

select 1 

from TrainingCourseUnitOption 

join UnitUnitOption WITH (NOLOCK) on UnitUnitOption.UnitOptionCode = TrainingCourseUnitOption.UnitOptionCode 

join EmployeeUnitJobTypeUnitView WITH (NOLOCK) on EmployeeUnitJobTypeUnitView.UnitID = UnitUnitOption.UnitID 

and EmployeeUnitJobTypeUnitView.EmployeeId = Employee.EmployeeId 

where TrainingCourse.TrainingCourseId = TrainingCourseUnitOption.TrainingCourseId 

) OR NOT EXISTS ( 

select 1 from TrainingCourseUnitOption 

where TrainingCourse.TrainingCourseId = TrainingCourseUnitOption.TrainingCourseId 

) 

) 

AND 

(ClientTrainingModuleSelector.BeginDate is null or ClientTrainingModuleSelector.BeginDate <= '6/10/2015 12:00:00 AM') 

AND 

(ClientTrainingModuleSelector.ExpireDate is null or ClientTrainingModuleSelector.ExpireDate >= '6/11/2015 12:00:00 AM') 

AND 

( 

EXISTS ( 

select 1 

from ClientTrainingModuleSelectorUnit 

join EmployeeUnitJobTypeUnitView WITH (NOLOCK) 

on EmployeeUnitJobTypeUnitView.UnitID = ClientTrainingModuleSelectorUnit.UnitID 

and EmployeeUnitJobTypeUnitView.EmployeeId = Employee.EmployeeId 

where ClientTrainingModuleSelector.ClientTrainingModuleSelectorId = ClientTrainingModuleSelectorUnit.ClientTrainingModuleSelectorId 

) OR NOT EXISTS ( 

select 1 from ClientTrainingModuleSelectorUnit 

where ClientTrainingModuleSelector.ClientTrainingModuleSelectorId = ClientTrainingModuleSelectorUnit.ClientTrainingModuleSelectorId 

) 

) 

AND 

( 

EXISTS ( 

select 1 

from ClientTrainingModuleSelectorJobType 

join EmployeeUnitJobTypeIdView WITH (NOLOCK) 

on EmployeeUnitJobTypeIdView.JobTypeId = ClientTrainingModuleSelectorJobType.JobTypeId 

and EmployeeUnitJobTypeIdView.EmployeeId = Employee.EmployeeId 

where ClientTrainingModuleSelector.ClientTrainingModuleSelectorId = ClientTrainingModuleSelectorJobType.ClientTrainingModuleSelectorId 

) OR NOT EXISTS ( 

select 1 from ClientTrainingModuleSelectorJobType 

where ClientTrainingModuleSelector.ClientTrainingModuleSelectorId = ClientTrainingModuleSelectorJobType.ClientTrainingModuleSelectorId 

) 

OR ( 

ClientTrainingModuleSelector.ClientTrainingModuleSelectorId = EmployeeTrainingModuleAttempt.ClientTrainingModuleSelectorId 

AND EmployeeTrainingModuleAttempt.EmployeeTrainingModuleAttemptStatusCode = 1357 

) 

) 

AND 

( 

EXISTS ( 

select 1 

from ClientTrainingModuleSelectorUserRole 

where ClientTrainingModuleSelector.ClientTrainingModuleSelectorId = ClientTrainingModuleSelectorUserRole.ClientTrainingModuleSelectorId 

and ClientTrainingModuleSelectorUserRole.UserRoleCode = UserRole.UserRoleCode 

) OR NOT EXISTS ( 

select 1 from ClientTrainingModuleSelectorUserRole 

where ClientTrainingModuleSelector.ClientTrainingModuleSelectorId = ClientTrainingModuleSelectorUserRole.ClientTrainingModuleSelectorId 

) 

) 

AND 

( 

EXISTS ( 

select 1 

from ClientTrainingModuleSelectorZone 

join EmployeeUnitJobTypeAllView WITH (NOLOCK) 

on EmployeeUnitJobTypeAllView.ZoneId = ClientTrainingModuleSelectorZone.ZoneId 

and EmployeeUnitJobTypeAllView.EmployeeId = Employee.EmployeeId 

where ClientTrainingModuleSelector.ClientTrainingModuleSelectorId = ClientTrainingModuleSelectorZone.ClientTrainingModuleSelectorId 

) OR NOT EXISTS ( 

select 1 from ClientTrainingModuleSelectorZone 

where ClientTrainingModuleSelector.ClientTrainingModuleSelectorId = ClientTrainingModuleSelectorZone.ClientTrainingModuleSelectorId 

) 

) 

AND 

( 

EXISTS ( 

select 1 

from ClientTrainingModuleSelectorDistrict 

join EmployeeUnitJobTypeAllView WITH (NOLOCK) 

on EmployeeUnitJobTypeAllView.DistrictId = ClientTrainingModuleSelectorDistrict.DistrictId 

and EmployeeUnitJobTypeAllView.EmployeeId = Employee.EmployeeId 

where ClientTrainingModuleSelector.ClientTrainingModuleSelectorId = ClientTrainingModuleSelectorDistrict.ClientTrainingModuleSelectorId 

) OR NOT EXISTS ( 

select 1 from ClientTrainingModuleSelectorDistrict 

where ClientTrainingModuleSelector.ClientTrainingModuleSelectorId = ClientTrainingModuleSelectorDistrict.ClientTrainingModuleSelectorId 

) 

) 

AND 

( 

EXISTS ( 

select 1 

from ClientTrainingModuleSelectorState 

join Unit WITH (NOLOCK) on Unit.StateCode = ClientTrainingModuleSelectorState.StateCode 

join EmployeeUnitJobTypeUnitView WITH (NOLOCK) on EmployeeUnitJobTypeUnitView.UnitID = Unit.UnitID 

and EmployeeUnitJobTypeUnitView.EmployeeId = Employee.EmployeeId 

where ClientTrainingModuleSelector.ClientTrainingModuleSelectorId = ClientTrainingModuleSelectorState.ClientTrainingModuleSelectorId 

) OR NOT EXISTS ( 

select 1 from ClientTrainingModuleSelectorState 

where ClientTrainingModuleSelector.ClientTrainingModuleSelectorId = ClientTrainingModuleSelectorState.ClientTrainingModuleSelectorId 

) 

) 

AND 

( 

EXISTS ( 

select 1 

from ClientTrainingModuleSelectorUnitOption 

join UnitUnitOption WITH (NOLOCK) on UnitUnitOption.UnitOptionCode = ClientTrainingModuleSelectorUnitOption.UnitOptionCode 

join EmployeeUnitJobTypeUnitView WITH (NOLOCK) on EmployeeUnitJobTypeUnitView.UnitID = UnitUnitOption.UnitID 

and EmployeeUnitJobTypeUnitView.EmployeeId = Employee.EmployeeId 

where ClientTrainingModuleSelector.ClientTrainingModuleSelectorId = ClientTrainingModuleSelectorUnitOption.ClientTrainingModuleSelectorId 

) OR NOT EXISTS ( 

select 1 from ClientTrainingModuleSelectorUnitOption 

where ClientTrainingModuleSelector.ClientTrainingModuleSelectorId = ClientTrainingModuleSelectorUnitOption.ClientTrainingModuleSelectorId 

) 

) 

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.TrainingWorkshopStatusCode = 1300 

) 

AND 

( (TrainingWorkshop.LanguageCode = Employee.LanguageCode or TrainingWorkshop.LanguageCode is null) or EmployeeTrainingModuleAttempt.EmployeeTrainingModuleAttemptStatusCode = 1357) 

AND 

(TrainingWorkshop.BeginDate is null or TrainingWorkshop.BeginDate <= '6/10/2015 12:00:00 AM') 

AND 

(TrainingWorkshop.ExpireDate is null or TrainingWorkshop.ExpireDate >= '6/11/2015 12:00:00 AM') 

AND 

( 

EXISTS ( 

select 1 

from TrainingWorkshopUnit 

join EmployeeUnitJobTypeUnitView WITH (NOLOCK) 

on EmployeeUnitJobTypeUnitView.UnitID = TrainingWorkshopUnit.UnitID 

and EmployeeUnitJobTypeUnitView.EmployeeId = Employee.EmployeeId 

where TrainingWorkshop.TrainingWorkshopId = TrainingWorkshopUnit.TrainingWorkshopId 

) OR NOT EXISTS ( 

select 1 from TrainingWorkshopUnit 

where TrainingWorkshop.TrainingWorkshopId = TrainingWorkshopUnit.TrainingWorkshopId 

) 

) 

AND 

( 

EXISTS ( 

select 1 

from TrainingWorkshopJobType 

join EmployeeUnitJobTypeIdView WITH (NOLOCK) 

on EmployeeUnitJobTypeIdView.JobTypeId = TrainingWorkshopJobType.JobTypeId 

and EmployeeUnitJobTypeIdView.EmployeeId = Employee.EmployeeId 

where TrainingWorkshop.TrainingWorkshopId = TrainingWorkshopJobType.TrainingWorkshopId 

) OR NOT EXISTS ( 

select 1 from TrainingWorkshopJobType 

where TrainingWorkshop.TrainingWorkshopId = TrainingWorkshopJobType.TrainingWorkshopId 

) 

OR ( 

TrainingWorkshop.TrainingWorkshopId = EmployeeTrainingModuleAttempt.TrainingWorkshopId 

AND EmployeeTrainingModuleAttempt.EmployeeTrainingModuleAttemptStatusCode = 1357 

) 

) 

AND 

( 

EXISTS ( 

select 1 

from TrainingWorkshopUserRole 

where TrainingWorkshop.TrainingWorkshopId = TrainingWorkshopUserRole.TrainingWorkshopId 

and TrainingWorkshopUserRole.UserRoleCode = UserRole.UserRoleCode 

) OR NOT EXISTS ( 

select 1 from TrainingWorkshopUserRole 

where TrainingWorkshop.TrainingWorkshopId = TrainingWorkshopUserRole.TrainingWorkshopId 

) 

) 

AND 

( 

EXISTS ( 

select 1 

from TrainingWorkshopZone 

join EmployeeUnitJobTypeAllView WITH (NOLOCK) 

on EmployeeUnitJobTypeAllView.ZoneId = TrainingWorkshopZone.ZoneId 

and EmployeeUnitJobTypeAllView.EmployeeId = Employee.EmployeeId 

where TrainingWorkshop.TrainingWorkshopId = TrainingWorkshopZone.TrainingWorkshopId 

) OR NOT EXISTS ( 

select 1 from TrainingWorkshopZone 

where TrainingWorkshop.TrainingWorkshopId = TrainingWorkshopZone.TrainingWorkshopId 

) 

) 

AND 

( 

EXISTS ( 

select 1 

from TrainingWorkshopDistrict 

join EmployeeUnitJobTypeAllView WITH (NOLOCK) 

on EmployeeUnitJobTypeAllView.DistrictId = TrainingWorkshopDistrict.DistrictId 

and EmployeeUnitJobTypeAllView.EmployeeId = Employee.EmployeeId 

where TrainingWorkshop.TrainingWorkshopId = TrainingWorkshopDistrict.TrainingWorkshopId 

) OR NOT EXISTS ( 

select 1 from TrainingWorkshopDistrict 

where TrainingWorkshop.TrainingWorkshopId = TrainingWorkshopDistrict.TrainingWorkshopId 

) 

) 

AND 

( 

EXISTS ( 

select 1 

from TrainingWorkshopState 

join Unit WITH (NOLOCK) on Unit.StateCode = TrainingWorkshopState.StateCode 

join EmployeeUnitJobTypeUnitView WITH (NOLOCK) on EmployeeUnitJobTypeUnitView.UnitID = Unit.UnitID 

and EmployeeUnitJobTypeUnitView.EmployeeId = Employee.EmployeeId 

where TrainingWorkshop.TrainingWorkshopId = TrainingWorkshopState.TrainingWorkshopId 

) OR NOT EXISTS ( 

select 1 from TrainingWorkshopState 

where TrainingWorkshop.TrainingWorkshopId = TrainingWorkshopState.TrainingWorkshopId 

) 

) 

AND 

( 

EXISTS ( 

select 1 

from TrainingWorkshopUnitOption 

join UnitUnitOption WITH (NOLOCK) on UnitUnitOption.UnitOptionCode = TrainingWorkshopUnitOption.UnitOptionCode 

join EmployeeUnitJobTypeUnitView WITH (NOLOCK) on EmployeeUnitJobTypeUnitView.UnitID = UnitUnitOption.UnitID 

and EmployeeUnitJobTypeUnitView.EmployeeId = Employee.EmployeeId 

where TrainingWorkshop.TrainingWorkshopId = TrainingWorkshopUnitOption.TrainingWorkshopId 

) OR NOT EXISTS ( 

select 1 from TrainingWorkshopUnitOption 

where TrainingWorkshop.TrainingWorkshopId = TrainingWorkshopUnitOption.TrainingWorkshopId 

) 

) 

Group by District.DistrictId,District.DistrictName,EmployeeTrainingModuleAttempt.EmployeeTrainingModuleAttemptStatusCode

OPTION (RECOMPILE)

Open in new window

Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
And... in answer to these questions:

1) Are your statistics up to date?
 2) Have you looked at the actual execution plan to see what it's doing?  Is it accessing data via expected indexes or performing full scans

1. I don't know what that is.
2. No, I haven't looked.

I am not a DBA. I can write a decent query and I am a programmer. But I have told them that I have reached the max in my abilities to tune that query and that they need to hire someone who does dbf and query tuning every day of the week and is a dba. They've done this before and hired a friend of a friend. Billed them 6 hours for nothing. Now they're looking at someone who works in the building. I've seen that person's website and they don't have that they offer dbf tuning services.

I can run the sql admin tools, but that is not my primary function and it would take longer for me to learn how to use the tools than for them to hire someone who does this for a living. If you can recommend someone who does this for a living. I mean really does it for a living, I'd love to recommend someone that I would trust.

There's probably some magic that I just don't know about.
Steve WalesSenior Database AdministratorCommented:
With that in mind then ... statistics tell the engine the best access paths to the data.  If your statistics are out of date, the optimizer may not know where to go looking for data and resort to full scans, even if an index exists that could speed up the access.

Docs on updating statistics: https://msdn.microsoft.com/en-us/library/ms173804.aspx

You can manually run them by doing:

use name_of_your_db
go
exec sp_updatestats

This article tells you how to generate an actual execution plan: https://msdn.microsoft.com/en-us/ms189562.aspx

After your stats are gathered, rerun the query, have a look at the graphical representation that is generated for you.

Hovering on each node in the generated chart will tell you what indexes are being used and where the majority of your time is coming from.

Hopefully you know your database well enough to be able to see if the correct indexes are being used.
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
Will updating the statistics cause any hiccups on an active database?
Will it lock anything up or slow it down?
This database server was created about a year ago.

thanks!
Steve WalesSenior Database AdministratorCommented:
Might cause some locking ... maybe it's something you can schedule to run overnight ?
Vitor MontalvãoMSSQL Senior EngineerCommented:
I read in a forum post about using OPTION (RECOMPILE) and WITH (NOLOCK) to make queries faster.
Weird things that we can read from the internet.

I have a query that takes from 2 to 7 minutes to run, depending on the specifics of the search criteria.
Do you have indexes created on the criteria fields? You should always start from here before thinking in other options. Indexes, indexes and indexes!

If you want us to help you better then post your query and some sample data.
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
I already included the query above.

I have indexes on everything as far as I can tell.

The results are 64 rows that are simple and look like this:
469      XEMEKA      3359      0      0      0      3359      0
665      XAERD      333      333      0      0      0      0

At one time I ran something that told me that I needed an index here or there and I added indexes and I can't for the life of me find out where that was to run it again and check things out. Any ideas what that is?

I ran an execution plan and I really can't tell how to use it. It says in sections "Sort (Distinct Sort) Cost: 13%. I hover over it and it tells me what the field is and I check it and the field is indexed. I've checked all of them.

I looked at the XML version and can't tell what is going on. Here I an example of that:
<RelOp AvgRowSize="48" EstimateCPU="42.5766" EstimateIO="0.00563063" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="935305" LogicalOp="Sort" NodeId="76" Parallel="true" PhysicalOp="Sort" EstimatedTotalSubtreeCost="60.7121">

Open in new window


I'd like to find that area that tells me if I'm missing indexes. Any ideas?

But I feel this question is getting off track, because it was originally about those two commands and what they mean. I feel I should open another ticket for all this.
Vitor MontalvãoMSSQL Senior EngineerCommented:
But I feel this question is getting off track, because it was originally about those two commands and what they mean
I thought that was clear already. Those options per se doesn't improve the performance of a query. They may but then depends of the query itself, ie doesn't apply to all queries in general but particular ones. Doesn't seem to be your case.
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
>>I thought that was clear already.

Yes, it was clear at that point. My point was that I was asking where to find the tool that tells you if you are missing indexes; and that wasn't the original topic.

I'll close this out since no one is answering that.
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
Thank you Steve!!!!
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
I found the missing index feature is in the execution plan results. However, since there were none listed, I didn't realize it was there. I have no missing indexes.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.