Query performance problem - SQL Server Forums

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Query performance problem

Author

Topic

cloucas
Starting Member

2 Posts

Posted - 2011-10-06 : 10:33:47

Hi

I have developed a SQL2008R2 database to track employee arrival and departure times. These are written in a table with an Id(PK, IDENTITY), EmployeeId(int) and EventTypeId(int) (1=arrival,2=departure) and a timestamp field.

In running a report to show for each day each employee's arrival and departure times I JOIN the table to itself, like so:

(select e.EmployeeID AS EmployeeId,e.FirstName AS FirstName,e.LastName AS LastName,dbo.AdjustArrivalTime(ev.Timestamp, ev.EmployeeId) AS EventTime,ev.EventType AS EventType
FROM Employees e
INNER JOIN Events ev
ON e.EmployeeID = ev.EmployeeID
WHERE ev.Timestamp >= @DateToStart AND ev.Timestamp <= DATEADD(dd,1,@DateToEnd) AND EventType=1 AND (e.RankId = @RankId OR @RankId=0) AND (e.DirectorateId = @DirectorateId OR @DirectorateId = 0)
) AS allEventsA
LEFT JOIN
(
select ev.EmployeeID,dbo.AdjustDepartureTime(ev.Timestamp, ev.EmployeeId) AS EventTime
FROM Employees e
INNER JOIN Events ev
ON e.EmployeeID = ev.EmployeeID
WHERE ev.Timestamp >= @DateToStart AND ev.Timestamp <= DATEADD(dd,1,@DateToEnd) AND EventType=2 AND (e.RankId = @RankId OR @RankId=0) AND (e.DirectorateId = @DirectorateId OR @DirectorateId = 0)
) AS allEventsB
ON allEventsA.EmployeeId=allEventsB.EmployeeId

For a large timespan, say from 01 May 2011, to 01 October 2011 the query takes some 30 seconds to run.

Is there a way I can make the query run faster? Looking around some people are suggesting using table variables, but I have never used any so any help would be appreciated.

Chris

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-10-06 : 10:57:38

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

--
Gail Shaw
SQL Server MVP

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2011-10-06 : 11:19:42

You have indexes? Up-to-date Index statistics?

cloucas
Starting Member

2 Posts

Posted - 2011-10-06 : 23:25:13

quote:
Originally posted by denis_the_thief

You have indexes? Up-to-date Index statistics?

I am not very familiar with indices or index statistics, but if you can point to me how to get them I will sure do. The table has a single primary key (that's an index column, no?)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-07 : 02:03:55

first point to look for is analyse excution plan for the above query. for that select display execution plan button on top of management studio before you run query. then analyse it and see if there's any table scans happening having high cost (2008 even gives some index recommendations also). then create the indexes using CREATE INDEX statement

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-10-08 : 10:02:52

Check performance using with statement i.e CTE and Temporary tables like #temp to get desired data.

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

Reporter
Starting Member

48 Posts

Posted - 2011-10-10 : 03:56:38

may be, this will be useful

select e.EmployeeID AS EmployeeId,e.FirstName AS FirstName,e.LastName AS LastName,dbo.AdjustArrivalTime(ev.Timestamp, ev.EmployeeId) AS EventTime,ev.EventType AS EventType
,case when EventType=2 then dbo.AdjustDepartureTime(ev.Timestamp, ev.EmployeeId) else null end AS EventTime
FROM Employees e
INNER JOIN Events ev
ON e.EmployeeID = ev.EmployeeID
WHERE ev.Timestamp >= @DateToStart AND ev.Timestamp <= DATEADD(dd,1,@DateToEnd) AND (EventType=1 or EventType=2) AND (e.RankId = @RankId OR @RankId=0) AND (e.DirectorateId = @DirectorateId OR @DirectorateId = 0)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources