Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2002-11-08 : 21:29:55
|
| I'll list the entire select below in case anyone wants to see it...I have a where condition that will execute in the time of a keyclick:(@Completed = 1 AND CRecordFinish >= (SELECT CourseStartDate FROM Courses WHERE CourseID=@CourseID))But if I add in the other 'HALF' of the test:(@Completed = 0 AND CRecordFinish IS NULL)OR (@Completed = 1 AND CRecordFinish >= (SELECT CourseStartDate FROM Courses WHERE CourseID=@CourseID))The query will yield the same result, but it takes 30 SECONDS to run.For the curious, the parameter @Completed is passed to indicate the type of course completion results:-1 don't care about the completion date0 list only incompleted users (date should be null)1 list the completed users (non null date)Help.Sam--------------SELECT U.UserID, Username, FirstName as [First Name], LastName as [Last Name], C.CenterName, B.BranchName, Email, CONVERT(varchar(10), CRecordFinish, 107) as [Completed], CASE WHEN CRecordFinish is NULL THEN '' ELSE datediff(minute, CRecordStart, CRecordFinish) END as [Elapsed Minutes], CASE WHEN CRAcknowledge IS NULL THEN '' WHEN CRAcknowledge = 0 then 'Deny' ELSE 'Accept' END AS Accept , U.EmployeeType as [Employee Type], CASE U.ISSO WHEN 1 THEN 'Yes' ELSE 'No' END AS ISSO , CASE U.Offline WHEN 1 THEN 'Yes' ELSE 'No' END AS Offline , CASE U.Inactive WHEN 1 THEN 'Yes' ELSE 'No' END AS Inactive FROM Users U LEFT OUTER JOIN Branches B on U.BranchID=B.BranchID LEFT OUTER JOIN Centers C on U.CenterID=C.CenterID LEFT OUTER JOIN CourseRecords CR ON CR.UserID = U.UserID AND CR.CourseID = @CourseID WHERE U.ClientID = @ClientID AND (@Email IS NULL OR Email Like @Email) AND (@Firstname IS NULL OR Firstname Like @Firstname) AND (@Middlename IS NULL OR Middlename LIKE @Middlename) AND (@Lastname IS NULL OR Lastname LIKE @Lastname) AND (@Username IS NULL OR Username LIKE @Username) AND (@EmployeeType IS NULL OR EmployeeType LIKE @EmployeeType) AND (@CenterID IS NULL OR U.CenterID = @CenterID) AND (@BranchID IS NULL OR U.BranchID = @BranchID) AND (@Offline = -1 OR Offline = @Offline) AND (@Inactive = -1 OR Inactive = @Inactive) AND (@Acknowledge = -1 OR CRAcknowledge = @Acknowledge) AND ( (@Completed = -1) OR (@Completed = 0 AND CRecordFinish IS NULL) OR (@Completed = 1 AND CRecordFinish >= (SELECT CourseStartDate FROM Courses WHERE CourseID=@CourseID)) ) AND (@CompletedSince IS NULL OR CRecordFinish > @CompletedSince) |
|
|
Lavos
Posting Yak Master
200 Posts |
Posted - 2002-11-09 : 14:26:05
|
| How does the query run with just (@Completed = 0 AND CRecordFinish IS NULL)?Have you looked at the execution plan being used in the Query Analyzer? Looking at it will tell you all the steps that SQL Server is taking to execute your query. Look at the plans for both of the queries you are trying to run. My guess, is that the multiple checks on CRecordFinish might be triggering a table or index scan instead of seeks.Once you've examined the execution plan, picking an appropriate course of action becomes a little easier.----------------------"O Theos mou! Echo ten labrida en te mou kephale!" |
 |
|
|
|
|
|
|
|
Way out performance problem