Why need so much time form Function
-
HI,
I have created a multi line table function.This fucntion takes time to return output more than 1 min.I have replaced the function by Sp.Now it takes only
2-3 secs. to show the output.The internal code in SP and in Function are identically same.
I don't understand why function takes so much of time than sp?
Can any one tell me the reason(technical point) behind it?
I need it urgently..
Thanks & Regards
Niladri
-------------------------------------
Hi,
Herewith I am Pasting my code for SP and Function.
Color Code :
SP : Blue
Function: Green
Table : Red
Here I am using only two tables.I have also given the table structure with Primary keys and foreign key referrences below.
In code I am not using table directly.Instead of this I have created two views for each table with all fields and used those views.
CREATE PROCEDURE sproc_SupplyStaffAvailability
(@StartDate datetime,
@StartOpening char(1),
@EndDate datetime,
@EndOpening char(1)
 
AS
BEGIN
/* SET THE DATEFIRST TO MONDAY */
SET DATEFIRST 1
DECLARE @DateCnt DATETIME
DECLARE @TotalDays numeric(8,2)
DECLARE @StartOpeningA char(1)
DECLARE @StartOpeningM char(1)
DECLARE @EndOpeningA char(1)
DECLARE @EndOpeningM char(1)
SET @DateCnt = @StartDate
SET @TotalDays = 0
WHILE @DateCnt <= @EndDate
BEGIN
IF (datepart(DW,@DateCnt) <> 6) and (datepart(DW,@DateCnt) <> 7)
BEGIN
SET @TotalDays = @TotalDays + 1
END
SET @DateCnt = @DateCnt +1
END
SET @TotalDays = @TotalDays * 2
IF @StartOpening = 'M' OR @StartOpening IS NULL
BEGIN
SET @StartOpeningM = 'M'
SET @StartOpeningA = 'A'
END
ELSE IF @StartOpening = 'A'
BEGIN
SET @StartOpeningM = NULL
SET @StartOpeningA = 'A'
SET @TotalDays = @TotalDays -1
END
IF @EndOpening = 'A' OR @EndOpening IS NULL
BEGIN
SET @EndOpeningM = 'M'
SET @EndOpeningA = 'A'
END
ELSE IF @EndOpening = 'M'
BEGIN
SET @EndOpeningM = 'M'
SET @EndOpeningA = NULL
SET @TotalDays = @TotalDays -1
END
IF (@TotalDays <= 0)
BEGIN
RAISERROR ('Invalid date range',16,1)
RETURN 1
END
SELECT SupplyTeacherView.SupplyTeacherID, Forename, Surname,
100 - Convert(Numeric(10,2),((Count(bookingId)/@TotalDays) * 100)) as Availability
FROM SupplyTeacherView LEFT OUTER JOIN BookingsView
ON SupplyTeacherView.supplyTeacherId = BookingsView.supplyTeacherId
AND BookingDate between @StartDate AND @EndDate
AND (BookingDate = @StartDate AND (morningOrAfternoon IN (@StartOpeningM)
OR morningOrAfternoon IN (@StartOpeningA)
)
OR BookingDate <> @StartDate
 
AND (BookingDate = @EndDate AND (morningOrAfternoon IN (@EndOpeningM)
OR morningOrAfternoon IN (@EndOpeningA)
 
OR BookingDate <> @EndDate
 
GROUP BY SupplyTeacherView.supplyTeacherId, forename, surname
HAVING 100 - ((Count(bookingId)/@TotalDays) * 100) > 0
ORDER BY SupplyTeacherView.supplyTeacherId
RETURN 0
END
GO
CREATE FUNCTION ufn_SupplyStaffAvailability
(@StartDate datetime,
@StartOpening char(1),
@EndDate datetime,
@EndOpening char(1))
RETURNS @StaffAvailability TABLE
(supplyTeacherId int,
forename char(20),
surname char(20),
Availability Numeric(8,2)
 
AS
BEGIN
DECLARE @DateCnt DATETIME
DECLARE @TotalDays numeric(8,2)
DECLARE @StartOpeningA char(1)
DECLARE @StartOpeningM char(1)
DECLARE @EndOpeningA char(1)
DECLARE @EndOpeningM char(1)
SET @DateCnt = @StartDate
SET @TotalDays = 0
WHILE @DateCnt <= @EndDate
BEGIN
IF (datename(DW,@DateCnt) <> 'Saturday') and (datename(DW,@DateCnt) <> 'Sunday')
BEGIN
SET @TotalDays = @TotalDays + 1
END
SET @DateCnt = @DateCnt +1
END
SET @TotalDays = @TotalDays * 2
IF @StartOpening = 'M' OR @StartOpening IS NULL
BEGIN
SET @StartOpeningM = 'M'
SET @StartOpeningA = 'A'
END
ELSE IF @StartOpening = 'A'
BEGIN
SET @StartOpeningM = NULL
SET @StartOpeningA = 'A'
SET @TotalDays = @TotalDays -1
END
IF @EndOpening = 'A' OR @EndOpening IS NULL
BEGIN
SET @EndOpeningM = 'M'
SET @EndOpeningA = 'A'
END
ELSE IF @EndOpening = 'M'
BEGIN
SET @EndOpeningM = 'M'
SET @EndOpeningA = NULL
SET @TotalDays = @TotalDays -1
END
INSERT INTO @StaffAvailability
SELECT SupplyTeacherView.supplyTeacherId, forename, surname,
100 - ((Count(bookingId)/@TotalDays) * 100) as Availability
FROM SupplyTeacherView LEFT OUTER JOIN BookingsView
ON SupplyTeacherView.supplyTeacherId = BookingsView.supplyTeacherId
AND [date] between @StartDate AND @EndDate
AND ([date] = @StartDate AND (morningOrAfternoon IN (@StartOpeningM)
OR morningOrAfternoon IN (@StartOpeningA)
)
OR [date] <> @StartDate
 
AND ([date] = @EndDate AND (morningOrAfternoon IN (@EndOpeningM)
OR morningOrAfternoon IN (@EndOpeningA)
 
OR [date] <> @EndDate
 
GROUP BY SupplyTeacherView.supplyTeacherId, forename, surname
HAVING 100 - ((Count(bookingId)/@TotalDays) * 100) > 0
RETURN
END
CREATE TABLE [dbo].[SupplyTeacher] (
[SupplyTeacherID] [int] Primary Key IDENTITY (1, 1) NOT NULL ,
[Forename] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Surname] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Bookings] (
[BookingID] [int] Primary Key IDENTITY (1, 1) NOT NULL ,
[SupplyTeacherID] [int] NOT NULL ,
[BookingDate] [datetime] NOT NULL ,
[MorningOrAfternoon] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
ALTER TABLE [dbo].[Bookings] ADD
CONSTRAINT [FK_Bookings_supplyTeacherId] FOREIGN KEY
(
[SupplyTeacherID]
 
REFERENCES [dbo].[SupplyTeacher] ([SupplyTeacherID]
 
GO
Hope u all can debug the code and find out the actual problem.
Thanks
Niladri
Thanks & Regards,Niladri Kumar Saha
-
Can you show us the code and how you are using it (how you call 'em)?
-
Might be helpful to see what you have done.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Functions are normally slower than other methods.
However, to ensure it is not an indexing issue, run profiler against the call to see what type of output you are seeing. Sometimes that will clue you in.
It would help us a lot if we saw some representation of the code, however.
The fact that the SP takes 2 - 3 seconds is a long time. I would start by optimizing that SP. Reads < 1000 is usually a good target point to start (I try to get mine less than 500 where possible. My guess is you need additional indexes, or need to change existing indexes. If you post the code, please post the indexes as well, so that we can judge how those are set up.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply