Why my Query is Running very SLow

Question

Dear Friends,

I have created a view. The Query is mentioned below. Please see if any body can help me optimizing this query.

TimeAttendance_VIEW

SELECT     TOP (100) PERCENT Name, Badge, dbo.GETDEPARTMENT(Badge) AS Department, IdNumber, 
dbo.GETFIRSTIN(CONVERT(varchar, EventTime, 101), Badge) AS FirstIn,  
dbo.GETLASTOUT(DateAdd(day, DateDiff(day, 0, EventTime), 0),Badge) AS LastOut, 
EventTime, 
dbo.ConvertToHOURSMINS(DATEDIFF(second,dbo.GETFIRSTIN(CONVERT(varchar, EventTime, 101), Badge), dbo.GETLASTOUT(CONVERT(varchar, EventTime, 101), Badge))) AS TotalWorkingTime
FROM         dbo.TimeAttendance

The functions scripts is mentioned below

ConvertToHOURSMINS

Create FUNCTION [dbo].[ConvertToHOURSMINS](@sec int)
RETURNS char(50)
AS
BEGIN
declare @time as char(50)
SET @time = convert(varchar(5),@sec/3600)+':'+convert(varchar(5),@sec%3600/60)+':'+convert(varchar(5),(@sec%60))

RETURN @time

END

[GETDEPARTMENT]

Create FUNCTION [dbo].[GETDEPARTMENT] ( @BadgeNo char(20))  
RETURNS varchar(200) AS  
BEGIN 
declare @DEPARTMENT as varchar(200)
select @DEPARTMENT = DEPT  from User, Badges where Axperson.Id = Badges.CardholderId and 
Badges.Number = @BadgeNo
return @DEPARTMENT
END

GETFIRSTIN

Create FUNCTION [dbo].[GETFIRSTIN] ( @EVENTTIME DATETIME, @BadgeNo char(20))  
RETURNS DATETIME AS  
BEGIN 
declare @FIRSTIN as DATETIME
select TOP 1 @FIRSTIN = EVENTTIME  from TIMEATTENDANCE where CONVERT(varchar, EventTime, 101)= @EVENTTIME and Badge=@BadgeNo
ORDER BY EVENTTIME ASC
return @FIRSTIN
END

GETLASTOUT

cREATE FUNCTION [dbo].[GETLASTOUT] ( @EVENTTIME DATETIME, @BadgeNo char(20))  
RETURNS DATETIME AS  
BEGIN 
declare @LASTOUT as DATETIME
select TOP 1 @LASTOUT = EVENTTIME  from TIMEATTENDANCE where CONVERT(varchar, EventTime, 101)= @EVENTTIME and Badge=@BadgeNo
ORDER BY EVENTTIME DESC

return @LASTOUT
END

Table Script

CREATE TABLE [dbo].[TimeAttendance](
	[EventTime] [datetime] NULL,
	[Name] [nvarchar](80) NULL,
	[Badge] [varchar](21) NULL,
	[IdNumber] [varchar](21) NULL,
	[Location] [nvarchar](40) NULL,
	[Address] [nvarchar](30) NULL,
	[Event] [nvarchar](80) NULL
) 

Regards,

Usman

Answer 1

Can you try rewriting the code without all of those functions?

Each time you wrap a function around a column you stop it from making use of indexes or stats.

Instead of doing things like this:

DateAdd(day,DateDiff(day,0,EventTime)

Can you just cast it: Cast(eventtime as date)

Seth





---

http://lqqsql.wordpress.com

Answer 2

Instead of function calls you can directly join on user and badges tables to get department. Also, since your functuons are small, you can probably try to inline them.

Sddesh