troubleshooting Question

Optimize my code

Avatar of BobRosas
BobRosas asked on
Microsoft SQL Server 2005Microsoft SQL Server 2008SSRS
22 Comments1 Solution342 ViewsLast Modified:
I have a report with an access front end and SQLServer backend.  The report is linked to the SQL Server tables but all the code is access.  When I run a specific report for all supervisors it takes about 3 seconds.  EE helped me with some very complex SQL code so that I could run a similiar report in Report Services.  The code works,,,the problem is, when I initially got help, Supervisor was a required dropdown.  Now it needs to be like the access report so you can run it for all Supervisors.  I changed the code, but when I run it in SQL server it takes 45 seconds (with access it's 3 seconds).  When I tried it in Report Services it won't even run, it times out.  All I added was an OR statement to the WHERE statement to run when Supervisor is Null.  Did I do it wrong?  The code is complex enough that I'm afraid to ask what can be done to speed things up but I'm hoping someone can suggest something that a novice SQL programmer can do.

Here is the code...
declare @From datetime, @To datetime, @Supervisor int
SET @From = '01/01/12';
SET @To = '01/05/12';
SET @Supervisor = 212;

SELECT CONVERT(varchar(10), TS_Day, 101) AS Date,
       EmployeeId,
       Name,
       UserName AS Supervisor,
       CONVERT(varchar, DATEDIFF(hh, 0, DATEADD(ss,SUM(ts_seconds + ts_manual_seconds + ts_approved_break_seconds), 0))) + SUBSTRING
      (CONVERT(varchar(8),DATEADD(ss,SUM(ts_seconds + ts_manual_seconds + ts_approved_break_seconds), 0), 114), 3, 3) AS HrsMin
FROM (SELECT times.TS_Day,
      L.EmployeeId,
      L.LastName + ', ' + L.FirstName AS Name,
      U.UserName,
      SUM(times.TS_Seconds) AS ts_seconds,
      SUM(times.TS_Manual_Seconds) AS ts_manual_seconds,
      SUM(times.TS_Break_Seconds) AS ts_break_seconds,
      CASE
        WHEN SUM([TS_Break_Seconds]) < 1800
        THEN SUM([TS_Break_Seconds])
        ELSE 1800
        END AS ts_approved_break_seconds
            FROM (SELECT Company,
                  EmployeeId,
                  CONVERT(datetime, CONVERT(varchar(8), TimeOut, 112)) AS TS_Day,
                  TimeIn,
                  TimeOut,
                  BreakFlag,
                  DATEDIFF(ss,TimeIn, TimeOut) AS TS_Seconds,
                  ISNULL(TimeSheetMinutes, 0) * 60 AS TS_Manual_Seconds,
                  ISNULL ((SELECT TOP (1)
                              CASE
                              WHEN t1.Breakflag = 1
                              THEN
                               CASE
                               WHEN datediff(ss, t1.timeout, isnull(t2.timein, t1.timeout)) BETWEEN 900 AND 1500
                               THEN 900
                               WHEN datediff(ss, t1.timeout, isnull(t2.timein, t1.timeout)) < 901
                               THEN datediff(ss, t1.timeout, isnull(t2.timein, t1.timeout))
                               ELSE 0
                               END
                              ELSE 0
                              END AS Expr1
                        FROM dbo.EmployeeHours AS t2
                        WHERE (EmployeeId = t1.EmployeeId) AND
                          (Company = t1.Company) AND
                          (CONVERT(varchar(8), TimeIn) = CONVERT(varchar(8), t1.TimeIn)) AND
                          (TimeIn > t1.TimeIn) OR
                          (EmployeeId = t1.EmployeeId) AND
                          (Company = t1.Company) AND
                          (CONVERT(varchar(8), TimeIn) = CONVERT(varchar(8), t1.TimeIn)) AND
                          (TimeIn = t1.TimeIn) AND
                          (TimeOut > t1.TimeOut)
                        ORDER BY TimeIn, TimeOut, RecordId), 0
                        ) AS TS_Break_Seconds
                  FROM dbo.EmployeeHours AS t1
                ) AS times
       INNER JOIN
       dbo.EmployeeList AS L ON
       times.EmployeeId = L.EmployeeId AND
       times.Company = L.Company
       LEFT OUTER JOIN
       dbo.UserList AS U ON L.ManagerUID = U.UID AND
       L.Company = U.Company
--I added from HERE to...
       WHERE (CONVERT(datetime, CONVERT(char(8), times.TimeIn, 112)) BETWEEN @FROM AND @TO) AND
       (L.Suspend = 0) AND
       (times.Company = 1) AND
       (@Supervisor IS NULL)
       OR
--HERE
       (CONVERT(datetime, CONVERT(char(8), times.TimeIn, 112)) BETWEEN @FROM AND @TO) AND
       (L.Suspend = 0) AND
       (times.Company = 1) AND
       (U.UID = @Supervisor)
       GROUP BY times.TS_Day,
                times.EmployeeId,
                times.TS_Day,
                L.EmployeeId,
                L.LastName + ', ' + L.FirstName,
                U.UserName
    ) AS daily
GROUP BY TS_Day,
         EmployeeId,
         Name,
         UserName
HAVING (SUM(ts_seconds + ts_manual_seconds + ts_approved_break_seconds) / 60 > 540)

ORDER BY TS_Day, Name, EmployeeId
ASKER CERTIFIED SOLUTION
DcpKing

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 22 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 22 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros