Can anyone make this user function perform better? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Can anyone make this user function perform better?

Author

Topic

dgaylor
Yak Posting Veteran

54 Posts

Posted - 2011-10-12 : 18:32:47

Hi, I have a user defined function that returns whether a class is full or not, based on the max number of seats and the number of students registered for it. I have not been able to think of a way to combine the selects into 1 to try and improve performance. Is there a way to do this? Thanks!

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
create FUNCTION [dbo].[fn_Event_IsClassFull2]
(

@ClassSchedulingID int
)
--This function returns True if class is full else False

returns bit

as

begin

declare @Full as bit
declare @MaxSeats as integer
declare @CurrentCount as integer

set @Full = 0

select @CurrentCount = count(*) from attendeeclasses where classschedulingid = @ClassSchedulingID
and regid in (select regid from regdata where dbo.fn_event_isactive(regid)=1 and persontypeid=0 )

select @MaxSeats = MaxSeats from classscheduling where classschedulingid = @classschedulingid

if @MaxSeats > 0 and @MaxSeats <= @CurrentCount set @Full = 1

return @Full

end

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-13 : 01:54:44

[code]
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
create FUNCTION [dbo].[fn_Event_IsClassFull2]
(

@ClassSchedulingID int
)
--This function returns True if class is full else False

returns bit

as

begin

declare @Full as bit
declare @MaxSeats as integer
declare @CurrentCount as integer

set @Full = 0
select @Full = case when cs.MaxSeats > 0 and cs.MaxSeats <= t.CurrentCount then 1 else 0 end
from classscheduling cs
cross apply(
select count(*) AS CurrentCount from attendeeclasses ac
join regdata r
on r.regid = ac.regid
where ac.classschedulingid = cs.classschedulingid
and dbo.fn_event_isactive(r.regid)=1
and r.persontypeid=0
)t
where cs.classschedulingid = @classschedulingid

return @Full

end
[/code]

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

Sachin.Nand

2937 Posts

Posted - 2011-10-13 : 04:13:53

Against how many records is this function going to be called ?

If you have a million rows of ClassSchedulingID then keep in mind that a million times will the function be executed.Now thats not even counting the rows that are going to be executed in the function itself.

PBUH

dgaylor
Yak Posting Veteran

54 Posts

Posted - 2011-10-13 : 12:21:59

visakh16, Thank you so much. Using your suggested change my operation changed from 7 seconds of execution to 1. That is amazing! Thanks again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-13 : 12:28:16

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources